package db

import (
	"context"
	"database/sql"
	"fmt"

	_ "github.com/mattn/go-sqlite3"
)

// DB est le wrapper principal autour de la connexion SQLite
type DB struct {
	conn *sql.DB
}

// Open ouvre (ou crée) la base SQLite et applique les migrations
func Open(path string) (*DB, error) {
	conn, err := sql.Open("sqlite3", path+"?_journal_mode=WAL&_busy_timeout=5000")
	if err != nil {
		return nil, fmt.Errorf("open db: %w", err)
	}

	conn.SetMaxOpenConns(1)
	conn.SetMaxIdleConns(1)

	db := &DB{conn: conn}
	if err := db.migrate(); err != nil {
		conn.Close()
		return nil, fmt.Errorf("migrate: %w", err)
	}

	return db, nil
}

func (db *DB) Close() error {
	return db.conn.Close()
}

func (db *DB) Conn() *sql.DB {
	return db.conn
}

// migrate crée les tables si elles n'existent pas
func (db *DB) migrate() error {
	ctx := context.Background()

	_, err := db.conn.ExecContext(ctx, `
		CREATE TABLE IF NOT EXISTS spots (
			id                  INTEGER PRIMARY KEY AUTOINCREMENT,
			dx                  TEXT NOT NULL,
			spotter             TEXT NOT NULL,
			frequency_khz       REAL NOT NULL,
			frequency_mhz       TEXT NOT NULL,
			band                TEXT NOT NULL,
			mode                TEXT NOT NULL,
			comment             TEXT DEFAULT '',
			original_comment    TEXT DEFAULT '',
			time                TEXT DEFAULT '',
			timestamp           INTEGER NOT NULL,
			dxcc                TEXT DEFAULT '',
			country_name        TEXT DEFAULT '',
			new_dxcc            INTEGER DEFAULT 0,
			new_band            INTEGER DEFAULT 0,
			new_mode            INTEGER DEFAULT 0,
			new_slot            INTEGER DEFAULT 0,
			callsign_worked     INTEGER DEFAULT 0,
			in_watchlist        INTEGER DEFAULT 0,
			pota_ref            TEXT DEFAULT '',
			sota_ref            TEXT DEFAULT '',
			park_name           TEXT DEFAULT '',
			summit_name         TEXT DEFAULT '',
			flex_spot_number    INTEGER DEFAULT 0,
			command_number      INTEGER DEFAULT 0,
			color               TEXT DEFAULT '#ffeaeaea',
			background_color    TEXT DEFAULT '#ff000000',
			priority            TEXT DEFAULT '5',
			life_time           TEXT DEFAULT '900',
			cluster_name        TEXT DEFAULT '',
			source              INTEGER DEFAULT 0
		)`)
	if err != nil {
		return fmt.Errorf("create spots table: %w", err)
	}

	// Index pour les recherches fréquentes
	_, err = db.conn.ExecContext(ctx, `
		CREATE INDEX IF NOT EXISTS idx_spots_dx_band ON spots(dx, band);
		CREATE INDEX IF NOT EXISTS idx_spots_timestamp ON spots(timestamp);
		CREATE INDEX IF NOT EXISTS idx_spots_flex_number ON spots(flex_spot_number);
		CREATE INDEX IF NOT EXISTS idx_spots_command_number ON spots(command_number);
	`)
	if err != nil {
		return fmt.Errorf("create indexes: %w", err)
	}

	return nil
}

// DeleteAll supprime tous les spots (appelé au démarrage comme l'ancien code)
func (db *DB) DeleteAll(ctx context.Context) error {
	_, err := db.conn.ExecContext(ctx, `DELETE FROM spots`)
	return err
}