import sqlite3
import os
import time

DB_PATH = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), "data", "grimhold.db")

def get_connection():
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    conn = sqlite3.connect(DB_PATH, timeout=30.0)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON;")
    conn.execute("PRAGMA journal_mode = WAL;")
    conn.execute("PRAGMA busy_timeout = 30000;")
    conn.execute("PRAGMA synchronous = NORMAL;")
    return conn

def init_db():
    conn = get_connection()
    with conn:
        conn.executescript("""
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password_hash TEXT NOT NULL,
            fortress_name TEXT NOT NULL,
            gold REAL NOT NULL DEFAULT 200.0,
            ore REAL NOT NULL DEFAULT 100.0,
            energy REAL NOT NULL DEFAULT 60.0,
            rating INTEGER NOT NULL DEFAULT 1000,
            camp_level INTEGER NOT NULL DEFAULT 1,
            camp_upgrade_finishes_at REAL NOT NULL DEFAULT 0,
            shield_until REAL NOT NULL DEFAULT 0,
            max_building_slots INTEGER NOT NULL DEFAULT 8,
            last_tick_at REAL NOT NULL,
            created_at REAL NOT NULL
        );

        CREATE TABLE IF NOT EXISTS user_army (
            user_id INTEGER NOT NULL,
            unit_type TEXT NOT NULL,
            pool_count INTEGER NOT NULL DEFAULT 0,
            defense_count INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (user_id, unit_type),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS maze_tiles (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            x INTEGER NOT NULL,
            y INTEGER NOT NULL,
            tile_type TEXT NOT NULL, -- 'rock', 'mg', 'cannon', 'ice', 'tesla'
            level INTEGER NOT NULL DEFAULT 1,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            UNIQUE(user_id, x, y)
        );

        CREATE TABLE IF NOT EXISTS machines (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            machine_type TEXT NOT NULL, -- 'drill', 'turbine', 'press'
            level INTEGER NOT NULL DEFAULT 1,
            upgrade_finishes_at REAL NOT NULL DEFAULT 0,
            sabotaged_until REAL NOT NULL DEFAULT 0,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            UNIQUE(user_id, machine_type)
        );

        CREATE TABLE IF NOT EXISTS sabotage_logs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            attacker_id INTEGER NOT NULL,
            defender_id INTEGER NOT NULL,
            result TEXT NOT NULL,
            creeps_leaked INTEGER NOT NULL,
            total_creeps INTEGER NOT NULL DEFAULT 0,
            units_sent TEXT DEFAULT '',
            stolen_gold REAL NOT NULL DEFAULT 0,
            stolen_ore REAL NOT NULL DEFAULT 0,
            stolen_energy REAL NOT NULL DEFAULT 0,
            created_at REAL NOT NULL,
            FOREIGN KEY (attacker_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (defender_id) REFERENCES users(id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS dungeon_runs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            username TEXT NOT NULL,
            hero_class TEXT NOT NULL,
            floor_reached INTEGER NOT NULL,
            hero_level INTEGER NOT NULL DEFAULT 1,
            monsters_slain INTEGER NOT NULL DEFAULT 0,
            gold_collected INTEGER NOT NULL DEFAULT 0,
            turns_count INTEGER NOT NULL DEFAULT 0,
            score INTEGER NOT NULL DEFAULT 0,
            cause_of_death TEXT NOT NULL,
            is_victory INTEGER NOT NULL DEFAULT 0,
            created_at REAL NOT NULL,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
        );

        CREATE TABLE IF NOT EXISTS dungeon_active_runs (
            user_id INTEGER PRIMARY KEY,
            hero_class TEXT NOT NULL,
            run_state TEXT NOT NULL,
            updated_at REAL NOT NULL,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        );
        """)
    conn.close()

if __name__ == "__main__":
    init_db()
    print("Database schema updated successfully at", DB_PATH)