package database

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

	_ "modernc.org/sqlite"
)

func Connect(dbPath string) (*sql.DB, error) {
	// Pastikan direktori database ada
	dir := filepath.Dir(dbPath)
	if _, err := os.Stat(dir); os.IsNotExist(err) {
		os.MkdirAll(dir, 0755)
	}

	db, err := sql.Open("sqlite", dbPath)
	if err != nil {
		return nil, err
	}

	// Optimasi SQLite untuk Performance & Reliability
	// WAL (Write-Ahead Logging) memungkinkan pembacaan dan penulisan bersamaan
	_, err = db.Exec(`
		PRAGMA journal_mode = WAL;
		PRAGMA synchronous = NORMAL;
		PRAGMA foreign_keys = ON;
		PRAGMA busy_timeout = 5000;
	`)
	if err != nil {
		return nil, err
	}

	// Buat Tabel jika belum ada
	if err := createTables(db); err != nil {
		return nil, err
	}

	return db, nil
}

func createTables(db *sql.DB) error {
	queries := []string{
		`CREATE TABLE IF NOT EXISTS webhook_queue (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			type TEXT NOT NULL,
			database_id INTEGER,
			uuid TEXT,
			payload TEXT NOT NULL,
			status TEXT DEFAULT 'pending',
			attempts INTEGER DEFAULT 0,
			error TEXT,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
		)`,
		`CREATE TABLE IF NOT EXISTS accurate_webhook_active_period (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			timestamp DATETIME NOT NULL,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
		)`,
		`CREATE INDEX IF NOT EXISTS idx_webhook_status ON webhook_queue(status)`,
		`CREATE UNIQUE INDEX IF NOT EXISTS idx_webhook_uuid ON webhook_queue(uuid) WHERE uuid IS NOT NULL`,
	}

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

	// Migration: Add columns if they don't exist (for existing databases)
	// Check and add database_id column
	var dbIDExists int
	err := db.QueryRow(`
		SELECT COUNT(*) FROM pragma_table_info('webhook_queue') WHERE name = 'database_id'
	`).Scan(&dbIDExists)
	if err == nil && dbIDExists == 0 {
		if _, execErr := db.Exec("ALTER TABLE webhook_queue ADD COLUMN database_id INTEGER"); execErr != nil {
			log.Printf("⚠️ Warning: Failed to add database_id column: %v", execErr)
		}
	}

	// Check and add uuid column
	var uuidExists int
	err = db.QueryRow(`
		SELECT COUNT(*) FROM pragma_table_info('webhook_queue') WHERE name = 'uuid'
	`).Scan(&uuidExists)
	if err == nil && uuidExists == 0 {
		if _, execErr := db.Exec("ALTER TABLE webhook_queue ADD COLUMN uuid TEXT"); execErr != nil {
			log.Printf("⚠️ Warning: Failed to add uuid column: %v", execErr)
		} else {
			log.Printf("✅ Added uuid column to webhook_queue")
		}
	}

	// Create unique index on uuid if not exists
	_, _ = db.Exec("CREATE UNIQUE INDEX IF NOT EXISTS idx_webhook_uuid ON webhook_queue(uuid) WHERE uuid IS NOT NULL")


	return nil
}