package database

import (
	"database/sql"
	"log"
	"strings"

	_ "modernc.org/sqlite"
)

var DB *sql.DB

// Init 打开 SQLite 并执行必要的表结构初始化.
func Init(path string) {
	dsn := "file:" + path + "?_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)&_pragma=foreign_keys(1)"
	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		log.Fatalf("open sqlite: %v", err)
	}
	DB = db
	if err := migrate(); err != nil {
		log.Fatalf("migrate: %v", err)
	}
}

func migrate() error {
	stmts := []string{
		`CREATE TABLE IF NOT EXISTS accounts (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            name       TEXT NOT NULL,
            email      TEXT NOT NULL UNIQUE,
            password   TEXT NOT NULL DEFAULT '',
            smtp_host  TEXT NOT NULL,
            smtp_port  INTEGER NOT NULL,
            imap_host  TEXT NOT NULL,
            imap_port  INTEGER NOT NULL,
            use_tls    INTEGER DEFAULT 1,
            auth_type  TEXT DEFAULT 'basic',
            oauth_refresh_token TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        );`,
		`CREATE TABLE IF NOT EXISTS sent_logs (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            account_id INTEGER NOT NULL,
            to_addr    TEXT NOT NULL,
            subject    TEXT,
            body       TEXT,
            status     TEXT,
            error      TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY(account_id) REFERENCES accounts(id) ON DELETE CASCADE
        );`,
		`CREATE TABLE IF NOT EXISTS oauth_microsoft_pending (
            state          TEXT PRIMARY KEY,
            refresh_token  TEXT,
            email          TEXT,
            created_at     DATETIME DEFAULT CURRENT_TIMESTAMP
        );`,
	}
	for _, s := range stmts {
		if _, err := DB.Exec(s); err != nil {
			return err
		}
	}
	// 旧库升级：逐列添加（已存在则忽略错误）
	alters := []string{
		`ALTER TABLE accounts ADD COLUMN auth_type TEXT DEFAULT 'basic'`,
		`ALTER TABLE accounts ADD COLUMN oauth_refresh_token TEXT`,
	}
	for _, s := range alters {
		if _, err := DB.Exec(s); err != nil {
			if !strings.Contains(strings.ToLower(err.Error()), "duplicate column") {
				log.Printf("migrate alter note: %v", err)
			}
		}
	}
	return nil
}