use rusqlite::Connection;
use std::path::Path;
use std::sync::{Arc, Mutex};

#[derive(Clone)]
pub struct Database {
    conn: Arc<Mutex<Connection>>,
}

// Safe because all access to Connection goes through Mutex::lock()
unsafe impl Sync for Database {}

impl Database {
    pub fn open(path: &Path) -> Result<Self, rusqlite::Error> {
        let conn = Connection::open(path)?;
        conn.execute_batch("PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;")?;
        let db = Self {
            conn: Arc::new(Mutex::new(conn)),
        };
        db.migrate()?;
        Ok(db)
    }

    fn migrate(&self) -> Result<(), rusqlite::Error> {
        let conn = self.conn.lock().unwrap();
        conn.execute_batch(
            "
            CREATE TABLE IF NOT EXISTS accounts (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                username    TEXT NOT NULL UNIQUE,
                password    TEXT NOT NULL,
                is_superuser INTEGER NOT NULL DEFAULT 0,
                password_change_required INTEGER NOT NULL DEFAULT 0
            );

            CREATE TABLE IF NOT EXISTS sessions (
                id          TEXT PRIMARY KEY,
                account_id  INTEGER NOT NULL,
                created_at  TEXT NOT NULL DEFAULT (datetime('now')),
                FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS instances (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                name        TEXT NOT NULL UNIQUE,
                version     TEXT NOT NULL,
                server_type TEXT NOT NULL CHECK(server_type IN ('VANILLA', 'FORGE', 'FABRIC')),
                world_source TEXT NOT NULL CHECK(world_source IN ('generate', 'upload')),
                world_seed   TEXT,
                level_type   TEXT NOT NULL DEFAULT 'DEFAULT',
                has_modpack  INTEGER NOT NULL DEFAULT 0,
                memory_mb    INTEGER NOT NULL,
                difficulty   INTEGER NOT NULL DEFAULT 1,
                gamemode     INTEGER NOT NULL DEFAULT 0,
                max_players  INTEGER NOT NULL DEFAULT 20,
                pvp          INTEGER NOT NULL DEFAULT 1,
                motd         TEXT NOT NULL DEFAULT 'A Minecraft Server',
                rcon_password TEXT NOT NULL,
                is_active    INTEGER NOT NULL DEFAULT 0,
                container_state TEXT NOT NULL DEFAULT 'stopped',
                created_at   TEXT NOT NULL DEFAULT (datetime('now')),
                updated_at   TEXT NOT NULL DEFAULT (datetime('now'))
            );

            CREATE TABLE IF NOT EXISTS mods (
                id              INTEGER PRIMARY KEY AUTOINCREMENT,
                instance_id     INTEGER NOT NULL,
                filename        TEXT NOT NULL,
                display_name    TEXT,
                side            TEXT NOT NULL DEFAULT 'UNKNOWN',
                accepted        INTEGER NOT NULL DEFAULT 0,
                rejection_reason TEXT,
                created_at      TEXT NOT NULL DEFAULT (datetime('now')),
                FOREIGN KEY (instance_id) REFERENCES instances(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS backups (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                instance_id INTEGER NOT NULL,
                filename    TEXT NOT NULL,
                created_at  TEXT NOT NULL DEFAULT (datetime('now')),
                FOREIGN KEY (instance_id) REFERENCES instances(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS whitelist (
                instance_id INTEGER NOT NULL,
                username    TEXT NOT NULL,
                PRIMARY KEY (instance_id, username),
                FOREIGN KEY (instance_id) REFERENCES instances(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS ops (
                instance_id INTEGER NOT NULL,
                username    TEXT NOT NULL,
                PRIMARY KEY (instance_id, username),
                FOREIGN KEY (instance_id) REFERENCES instances(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS bans (
                instance_id INTEGER NOT NULL,
                username    TEXT NOT NULL,
                ban_type    TEXT NOT NULL CHECK(ban_type IN ('user', 'ip')),
                PRIMARY KEY (instance_id, username),
                FOREIGN KEY (instance_id) REFERENCES instances(id) ON DELETE CASCADE
            );
            ",
        )?;

        // Migration: add container_state column if missing (for older DBs)
        let _ = conn.execute_batch(
            "ALTER TABLE instances ADD COLUMN container_state TEXT NOT NULL DEFAULT 'stopped'",
        );
        // Migration: add whitelist_enabled column if missing
        let _ = conn.execute_batch(
            "ALTER TABLE instances ADD COLUMN whitelist_enabled INTEGER NOT NULL DEFAULT 1",
        );
        // Migration: add enabled column to mods for disable/enable toggling
        let _ =
            conn.execute_batch("ALTER TABLE mods ADD COLUMN enabled INTEGER NOT NULL DEFAULT 1");
        Ok(())
    }

    pub fn conn(&self) -> std::sync::MutexGuard<'_, Connection> {
        self.conn.lock().unwrap()
    }
}