use rusqlite::Connection;

pub fn init_db(conn: &Connection) -> rusqlite::Result<()> {
    conn.execute_batch(
        r#"
        PRAGMA journal_mode=WAL;
        PRAGMA foreign_keys=ON;
        CREATE TABLE IF NOT EXISTS users (
            id TEXT PRIMARY KEY,
            email TEXT NOT NULL UNIQUE,
            password_hash TEXT NOT NULL,
            name TEXT NOT NULL,
            role TEXT NOT NULL CHECK(role IN ('admin','member')),
            active INTEGER NOT NULL DEFAULT 1,
            ai_enabled INTEGER NOT NULL DEFAULT 0,
            daily_run_limit INTEGER NOT NULL DEFAULT 10,
            created_at TEXT NOT NULL
        );
        CREATE TABLE IF NOT EXISTS sessions (
            id TEXT PRIMARY KEY,           -- sha256(token)
            user_id TEXT NOT NULL REFERENCES users(id),
            created_at TEXT NOT NULL,
            expires_at TEXT NOT NULL,
            origin_note TEXT
        );
        CREATE TABLE IF NOT EXISTS login_failures (
            email TEXT NOT NULL,
            failed_at TEXT NOT NULL
        );
        CREATE TABLE IF NOT EXISTS invitations (
            id TEXT PRIMARY KEY,
            email TEXT,
            token_hash TEXT NOT NULL UNIQUE,
            role TEXT NOT NULL DEFAULT 'member',
            expires_at TEXT NOT NULL,
            used_by TEXT,
            created_at TEXT NOT NULL
        );
        CREATE TABLE IF NOT EXISTS password_resets (
            id TEXT PRIMARY KEY,
            user_id TEXT NOT NULL REFERENCES users(id),
            token_hash TEXT NOT NULL UNIQUE,
            expires_at TEXT NOT NULL,
            used INTEGER NOT NULL DEFAULT 0
        );
        CREATE TABLE IF NOT EXISTS audit (
            seq INTEGER PRIMARY KEY AUTOINCREMENT,
            ts TEXT NOT NULL,
            actor_id TEXT,
            action TEXT NOT NULL,
            target TEXT,
            status TEXT NOT NULL,
            details TEXT
        );
        CREATE TABLE IF NOT EXISTS projects (
            id TEXT PRIMARY KEY,
            user_id TEXT NOT NULL REFERENCES users(id),
            name TEXT NOT NULL,
            description TEXT NOT NULL DEFAULT '',
            draft_code TEXT NOT NULL DEFAULT '',
            draft_generation INTEGER NOT NULL DEFAULT 0,
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL
        );
        CREATE TABLE IF NOT EXISTS project_versions (
            id TEXT PRIMARY KEY,
            project_id TEXT NOT NULL REFERENCES projects(id),
            code TEXT NOT NULL,
            hash TEXT NOT NULL,
            message TEXT NOT NULL,
            source TEXT NOT NULL CHECK(source IN ('manual','run','ai','restore')),
            created_at TEXT NOT NULL
        );
        CREATE TABLE IF NOT EXISTS datasets (
            id TEXT PRIMARY KEY,
            user_id TEXT NOT NULL REFERENCES users(id),
            name TEXT NOT NULL,
            request TEXT NOT NULL,
            status TEXT NOT NULL CHECK(status IN ('pending','running','ready','failed')),
            error TEXT,
            manifest_hash TEXT,
            manifest TEXT,
            cache_hit INTEGER,
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL
        );
        CREATE TABLE IF NOT EXISTS data_objects (
            hash TEXT PRIMARY KEY,
            path TEXT NOT NULL UNIQUE,
            size INTEGER NOT NULL,
            fetched_at TEXT NOT NULL
        );
        CREATE TABLE IF NOT EXISTS runs (
            id TEXT PRIMARY KEY,
            user_id TEXT NOT NULL REFERENCES users(id),
            project_id TEXT NOT NULL REFERENCES projects(id),
            version_id TEXT NOT NULL REFERENCES project_versions(id),
            dataset_id TEXT NOT NULL REFERENCES datasets(id),
            status TEXT NOT NULL CHECK(status IN ('queued','running','succeeded','failed','cancelled')),
            config TEXT NOT NULL,
            manifest_hash TEXT,
            container_id TEXT,
            result TEXT,
            error TEXT,
            created_at TEXT NOT NULL,
            started_at TEXT,
            finished_at TEXT
        );
        CREATE INDEX IF NOT EXISTS runs_project ON runs(project_id, created_at DESC);
        CREATE INDEX IF NOT EXISTS runs_user_daily ON runs(user_id, created_at DESC);
        CREATE TABLE IF NOT EXISTS ai_requests (
            id TEXT PRIMARY KEY,
            user_id TEXT NOT NULL REFERENCES users(id),
            project_id TEXT NOT NULL REFERENCES projects(id),
            instruction TEXT NOT NULL,
            status TEXT NOT NULL CHECK(status IN ('pending','succeeded','failed')),
            model TEXT,
            explanation TEXT,
            proposed_code TEXT,
            diff TEXT,
            base_generation INTEGER,
            base_code_hash TEXT,
            version_id TEXT,
            usage TEXT,
            error TEXT,
            created_at TEXT NOT NULL
        );
        CREATE TABLE IF NOT EXISTS fetch_cache (
            key TEXT PRIMARY KEY,
            manifest_hash TEXT NOT NULL,
            manifest TEXT NOT NULL,
            object_count INTEGER NOT NULL,
            fetched_at TEXT NOT NULL
        );
        CREATE TABLE IF NOT EXISTS ai_usage (
            id TEXT PRIMARY KEY,
            user_id TEXT NOT NULL,
            request_id TEXT,
            ts TEXT NOT NULL,
            kind TEXT NOT NULL,
            input_tokens INTEGER DEFAULT 0,
            output_tokens INTEGER DEFAULT 0
        );
        "#,
    )
}