import sqlite3

DB_PATH = "secuvault.db"

_SCHEMA = """
CREATE TABLE IF NOT EXISTS users (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    username      TEXT    UNIQUE NOT NULL,
    password_hash TEXT    NOT NULL,
    is_active     INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS teams (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    name        TEXT UNIQUE NOT NULL,
    description TEXT
);

CREATE TABLE IF NOT EXISTS user_teams (
    user_id INTEGER NOT NULL REFERENCES users(id)  ON DELETE CASCADE,
    team_id INTEGER NOT NULL REFERENCES teams(id)  ON DELETE CASCADE,
    PRIMARY KEY (user_id, team_id)
);

CREATE TABLE IF NOT EXISTS secrets (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    name            TEXT    NOT NULL,
    encrypted_value TEXT    NOT NULL,
    team_id         INTEGER NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
    created_at      TEXT    NOT NULL DEFAULT (datetime('now')),
    updated_at      TEXT    NOT NULL DEFAULT (datetime('now')),
    version         INTEGER NOT NULL DEFAULT 1
);
"""

def get_database_connection() -> sqlite3.Connection:
    conn = sqlite3.connect(DB_PATH, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys=ON")
    return conn

def init_db() -> None:
    conn = get_database_connection()
    try:
        conn.executescript(_SCHEMA)
        conn.commit()
    finally:
        conn.close()

def provision_data() -> None:
    import os
    from dotenv import load_dotenv
    from infra.crypto import hash_password

    load_dotenv()

    conn = get_database_connection()
    try:
        if conn.execute("SELECT COUNT(*) FROM users").fetchone()[0] > 0:
            return

        with conn:
            conn.execute("INSERT INTO teams (name, description) VALUES ('devops',    'Équipe DevOps')")
            conn.execute("INSERT INTO teams (name, description) VALUES ('marketing', 'Équipe Marketing')")

            for username, password in [
                ("alice",   os.environ["ALICE_PASSWORD"]),
                ("bob",     os.environ["BOB_PASSWORD"]),
                ("charlie", os.environ["CHARLIE_PASSWORD"]),
            ]:
                conn.execute(
                    "INSERT INTO users (username, password_hash) VALUES (?, ?)",
                    (username, hash_password(password)),
                )

            users = {r["username"]: r["id"] for r in conn.execute("SELECT id, username FROM users")}
            teams = {r["name"]: r["id"] for r in conn.execute("SELECT id, name FROM teams")}

            memberships = [
                (users["alice"],   teams["devops"]),
                (users["bob"],     teams["devops"]),
                (users["bob"],     teams["marketing"]),
                (users["charlie"], teams["marketing"]),
            ]
            conn.executemany(
                "INSERT INTO user_teams (user_id, team_id) VALUES (?, ?)",
                memberships,
            )

            devops_id    = teams["devops"]
            marketing_id = teams["marketing"]
            from infra.crypto import encrypt_secret
            conn.execute(
                "INSERT INTO secrets (name, encrypted_value, team_id) VALUES (?, ?, ?)",
                ("AWS root key", encrypt_secret("AKIAIOSFODNN7EXAMPLE"), devops_id),
            )
            conn.execute(
                "INSERT INTO secrets (name, encrypted_value, team_id) VALUES (?, ?, ?)",
                ("Mailchimp API", encrypt_secret("mc-api-key-placeholder"), marketing_id),
            )
    finally:
        conn.close()