package db

import (
	"database/sql"

	_ "modernc.org/sqlite" // à la place de go-sqlite3
)

type DB struct {
	*sql.DB
}

func Init(path string) (*DB, error) {
	sqldb, err := sql.Open("sqlite", path)
	if err != nil {
		return nil, err
	}

	// Une seule connexion — évite les SQLITE_BUSY entre goroutines concurrentes
	sqldb.SetMaxOpenConns(1)
	sqldb.SetMaxIdleConns(1)

	if err := sqldb.Ping(); err != nil {
		return nil, err
	}

	// WAL : lectures non bloquantes + timeout 10s avant BUSY
	sqldb.Exec("PRAGMA journal_mode=WAL")
	sqldb.Exec("PRAGMA busy_timeout=10000")

	database := &DB{sqldb}
	if err := database.migrate(); err != nil {
		return nil, err
	}

	return database, nil
}

func (db *DB) migrate() error {
	queries := []string{
		`CREATE TABLE IF NOT EXISTS settings (
            key         TEXT PRIMARY KEY,
            value       TEXT NOT NULL,
            encrypted   INTEGER DEFAULT 0,
            updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP
        )`,
		`CREATE TABLE IF NOT EXISTS watchlist (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            ticker      TEXT NOT NULL UNIQUE,
            name        TEXT,
            sector      TEXT,
            exchange    TEXT,
            active      INTEGER DEFAULT 1,
            created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
        )`,
		`CREATE TABLE IF NOT EXISTS instruments (
            instrument_id   INTEGER PRIMARY KEY,
            ticker          TEXT NOT NULL,
            name            TEXT,
            sector_id       INTEGER,
            exchange_id     INTEGER,
            asset_class_id  INTEGER,
            synced_at       DATETIME DEFAULT CURRENT_TIMESTAMP
        )`,
		`CREATE TABLE IF NOT EXISTS news (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            ticker      TEXT,
            headline    TEXT NOT NULL,
            source      TEXT,
            url         TEXT,
            sentiment   TEXT,
            published_at DATETIME,
            created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
        )`,
		`CREATE TABLE IF NOT EXISTS prices (
            id      INTEGER PRIMARY KEY AUTOINCREMENT,
            ticker  TEXT NOT NULL,
            date    DATE NOT NULL,
            open    REAL,
            high    REAL,
            low     REAL,
            close   REAL,
            volume  INTEGER,
            UNIQUE(ticker, date)
        )`,
		`CREATE TABLE IF NOT EXISTS signals (
            id           INTEGER PRIMARY KEY AUTOINCREMENT,
            ticker       TEXT NOT NULL UNIQUE,
            price        REAL,
            change_pct   REAL,
            rsi14        REAL,
            macd         REAL,
            macd_signal  REAL,
            macd_hist    REAL,
            sma20        REAL,
            sma50        REAL,
            volume       INTEGER,
            avg_volume20 INTEGER,
            alert        TEXT DEFAULT '',
            computed_at  DATETIME DEFAULT CURRENT_TIMESTAMP
        )`,
		`CREATE TABLE IF NOT EXISTS insider_trades (
            id               INTEGER PRIMARY KEY AUTOINCREMENT,
            ticker           TEXT NOT NULL,
            insider_name     TEXT,
            insider_title    TEXT,
            transaction_code TEXT,
            shares           REAL,
            price            REAL,
            total_value      REAL,
            transaction_date DATE,
            accession_no     TEXT UNIQUE,
            filing_url       TEXT,
            created_at       DATETIME DEFAULT CURRENT_TIMESTAMP
        )`,
		`CREATE INDEX IF NOT EXISTS idx_insider_ticker ON insider_trades(ticker)`,
	}

	for _, q := range queries {
		if _, err := db.Exec(q); err != nil {
			return err
		}
	}

	// Nettoyage : supprime les signaux watchlist pour les tickers retirés de la watchlist
	db.Exec(`DELETE FROM signals WHERE source='watchlist' AND ticker NOT IN (SELECT ticker FROM watchlist WHERE active=1)`)

	// Migrations additives — on ignore les erreurs si la colonne/index existe déjà
	additive := []string{
		`ALTER TABLE news ADD COLUMN finnhub_id INTEGER`,
		`CREATE UNIQUE INDEX IF NOT EXISTS idx_news_finnhub_id ON news(finnhub_id) WHERE finnhub_id IS NOT NULL`,
		`ALTER TABLE signals ADD COLUMN market_cap INTEGER DEFAULT 0`,
		`ALTER TABLE signals ADD COLUMN short_ratio REAL DEFAULT 0`,
		`ALTER TABLE signals ADD COLUMN score INTEGER DEFAULT 0`,
		`ALTER TABLE signals ADD COLUMN on_etoro INTEGER DEFAULT 0`,
		`ALTER TABLE signals ADD COLUMN week52_high REAL DEFAULT 0`,
		`ALTER TABLE signals ADD COLUMN week52_low REAL DEFAULT 0`,
		`ALTER TABLE signals ADD COLUMN pct_from_high REAL DEFAULT 0`,
		`ALTER TABLE signals ADD COLUMN insider_value_30d REAL DEFAULT 0`,
		`ALTER TABLE signals ADD COLUMN source TEXT DEFAULT 'watchlist'`,
		`CREATE INDEX IF NOT EXISTS idx_instruments_ticker ON instruments(ticker)`,
		`CREATE INDEX IF NOT EXISTS idx_signals_score ON signals(score DESC)`,
		`CREATE INDEX IF NOT EXISTS idx_signals_source ON signals(source)`,
		`ALTER TABLE signals ADD COLUMN insider_sell_value_30d REAL DEFAULT 0`,
		`ALTER TABLE signals ADD COLUMN earnings_date TEXT DEFAULT ''`,
		`CREATE TABLE IF NOT EXISTS company_events (
			id           INTEGER PRIMARY KEY AUTOINCREMENT,
			ticker       TEXT NOT NULL,
			event_type   TEXT NOT NULL,
			title        TEXT,
			accession_no TEXT UNIQUE,
			filing_date  DATE,
			filing_url   TEXT,
			created_at   DATETIME DEFAULT CURRENT_TIMESTAMP
		)`,
		`CREATE INDEX IF NOT EXISTS idx_company_events_ticker ON company_events(ticker)`,
		`ALTER TABLE signals ADD COLUMN ceo_change INTEGER DEFAULT 0`,
	}
	for _, q := range additive {
		db.Exec(q) // intentionnellement sans vérification d'erreur
	}

	return nil
}