// Package database opens the SQLite store, runs migrations and exposes a
// shared *sql.DB handle to the rest of the application. We use the pure-Go
// modernc.org/sqlite driver so the binary needs no CGO and cross-compiles
// cleanly.
package database

import (
	"database/sql"
	"fmt"
	"os"
	"path/filepath"

	_ "modernc.org/sqlite"
)

// Open prepares the database file (creating its parent directory) and runs
// all migrations. It returns a ready-to-use *sql.DB.
func Open(path string) (*sql.DB, error) {
	if dir := filepath.Dir(path); dir != "" {
		if err := os.MkdirAll(dir, 0o755); err != nil {
			return nil, fmt.Errorf("create db dir %s: %w", dir, err)
		}
	}

	// busy_timeout avoids "database is locked" under concurrent web + bot use.
	dsn := fmt.Sprintf("file:%s?_pragma=busy_timeout(5000)&_pragma=foreign_keys(1)&_pragma=journal_mode(WAL)", path)
	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, fmt.Errorf("open sqlite %s: %w", path, err)
	}

	// A single writer connection + a small read pool is the sweet spot for
	// SQLite with WAL. The bot and the web panel share this pool.
	db.SetMaxOpenConns(1)

	if err := db.Ping(); err != nil {
		_ = db.Close()
		return nil, fmt.Errorf("ping sqlite: %w", err)
	}

	if err := migrate(db); err != nil {
		_ = db.Close()
		return nil, err
	}
	return db, nil
}

// migrate runs the schema idempotently. Each statement is executed separately
// so a partial failure is easy to locate.
func migrate(db *sql.DB) error {
	stmts := []string{
		`CREATE TABLE IF NOT EXISTS schema_version (
			version INTEGER PRIMARY KEY,
			applied_at TEXT NOT NULL DEFAULT (datetime('now'))
		);`,

		`CREATE TABLE IF NOT EXISTS users (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			tg_id INTEGER NOT NULL UNIQUE,
			username TEXT,
			first_name TEXT,
			last_name TEXT,
			language TEXT NOT NULL DEFAULT 'fa',
			is_banned INTEGER NOT NULL DEFAULT 0,
			is_admin INTEGER NOT NULL DEFAULT 0,
			created_count INTEGER NOT NULL DEFAULT 0,
			created_at TEXT NOT NULL DEFAULT (datetime('now')),
			last_seen TEXT
		);`,

		`CREATE TABLE IF NOT EXISTS api_keys (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			user_id INTEGER NOT NULL,
			tg_id INTEGER NOT NULL,
			phone TEXT NOT NULL,
			app_title TEXT,
			app_shortname TEXT,
			app_platform TEXT,
			api_id TEXT,
			api_hash TEXT,
			status TEXT NOT NULL DEFAULT 'active',
			created_at TEXT NOT NULL DEFAULT (datetime('now')),
			FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
		);`,

		`CREATE INDEX IF NOT EXISTS idx_api_keys_user ON api_keys(user_id);`,
		`CREATE INDEX IF NOT EXISTS idx_api_keys_tg ON api_keys(tg_id);`,

		`CREATE TABLE IF NOT EXISTS proxies (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			kind TEXT NOT NULL DEFAULT 'http',
			host TEXT NOT NULL,
			port INTEGER NOT NULL,
			username TEXT,
			password TEXT,
			enabled INTEGER NOT NULL DEFAULT 1,
			last_used TEXT,
			fail_count INTEGER NOT NULL DEFAULT 0,
			created_at TEXT NOT NULL DEFAULT (datetime('now'))
		);`,

		`CREATE TABLE IF NOT EXISTS daily_usage (
			user_id INTEGER NOT NULL,
			day TEXT NOT NULL,
			count INTEGER NOT NULL DEFAULT 0,
			PRIMARY KEY (user_id, day)
		);`,

		`CREATE TABLE IF NOT EXISTS settings (
			key TEXT PRIMARY KEY,
			value TEXT NOT NULL
		);`,

		`CREATE TABLE IF NOT EXISTS logs (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			level TEXT NOT NULL,
			source TEXT NOT NULL,
			user_id INTEGER,
			message TEXT NOT NULL,
			created_at TEXT NOT NULL DEFAULT (datetime('now'))
		);`,

		`CREATE INDEX IF NOT EXISTS idx_logs_created ON logs(created_at DESC);`,
		`CREATE INDEX IF NOT EXISTS idx_logs_level ON logs(level);`,
	}

	for _, s := range stmts {
		if _, err := db.Exec(s); err != nil {
			return fmt.Errorf("migration statement failed:\n%s\n%w", s, err)
		}
	}

	// Record schema version so future migrations can be incremental.
	_, _ = db.Exec(`INSERT OR IGNORE INTO schema_version(version) VALUES (1);`)
	return nil
}
