"""
Persistent SQLite database repository for FreeExile Accounts, Tokens, and Characters.
Implements ACID compliance, Write-Ahead Logging (WAL), and strict parameterization.
"""

from __future__ import annotations
import json
import sqlite3
import time
import uuid
from typing import Any, Dict, List, Optional

from server.auth.models import (
    Account,
    AccountStatus,
    OAuthIdentity,
    OAuthProviderType,
)


class DatabaseAccountRepository:
    """Persistent SQLite Account, Token, and Character Repository."""

    def __init__(self, db_path: str = "data/freeexile_auth.db") -> None:
        self.db_path = db_path
        self._init_database()

    def _get_connection(self) -> sqlite3.Connection:
        conn = sqlite3.connect(self.db_path, timeout=10.0)
        conn.row_factory = sqlite3.Row
        conn.execute("PRAGMA journal_mode = WAL;")
        conn.execute("PRAGMA foreign_keys = ON;")
        conn.execute("PRAGMA synchronous = NORMAL;")
        return conn

    def _init_database(self) -> None:
        """Create tables and indexes if they do not exist."""
        with self._get_connection() as conn:
            conn.executescript("""
                CREATE TABLE IF NOT EXISTS accounts (
                    account_id TEXT PRIMARY KEY,
                    email TEXT UNIQUE NOT NULL,
                    username TEXT UNIQUE NOT NULL,
                    password_hash TEXT NOT NULL,
                    status INTEGER NOT NULL DEFAULT 1,
                    roles TEXT NOT NULL DEFAULT '["player"]',
                    created_at REAL NOT NULL,
                    last_login_at REAL,
                    failed_login_attempts INTEGER NOT NULL DEFAULT 0,
                    lockout_until REAL
                );

                CREATE TABLE IF NOT EXISTS oauth_identities (
                    provider INTEGER NOT NULL,
                    provider_user_id TEXT NOT NULL,
                    account_id TEXT NOT NULL,
                    email TEXT NOT NULL,
                    linked_at REAL NOT NULL,
                    PRIMARY KEY (provider, provider_user_id),
                    FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE
                );

                CREATE TABLE IF NOT EXISTS refresh_tokens (
                    token_hash TEXT PRIMARY KEY,
                    account_id TEXT NOT NULL,
                    expires_at REAL NOT NULL,
                    is_revoked INTEGER NOT NULL DEFAULT 0,
                    created_at REAL NOT NULL,
                    FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE
                );

                CREATE TABLE IF NOT EXISTS remember_tokens (
                    token_hash TEXT PRIMARY KEY,
                    account_id TEXT NOT NULL,
                    device_id TEXT NOT NULL,
                    device_name TEXT NOT NULL,
                    hardware_hash TEXT NOT NULL,
                    client_ip TEXT NOT NULL,
                    expires_at REAL NOT NULL,
                    is_revoked INTEGER NOT NULL DEFAULT 0,
                    created_at REAL NOT NULL,
                    FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE
                );

                CREATE TABLE IF NOT EXISTS characters (
                    character_id TEXT PRIMARY KEY,
                    account_id TEXT NOT NULL,
                    name TEXT UNIQUE NOT NULL,
                    class_type TEXT NOT NULL,
                    level INTEGER NOT NULL DEFAULT 1,
                    season_id TEXT NOT NULL,
                    experience INTEGER NOT NULL DEFAULT 0,
                    current_hp INTEGER NOT NULL DEFAULT 100,
                    max_hp INTEGER NOT NULL DEFAULT 100,
                    current_mana INTEGER NOT NULL DEFAULT 50,
                    max_mana INTEGER NOT NULL DEFAULT 50,
                    current_zone_id TEXT NOT NULL DEFAULT 'zone_boundless_sanctuary',
                    pos_x REAL NOT NULL DEFAULT 0.0,
                    pos_y REAL NOT NULL DEFAULT 10.0,
                    inventory_json TEXT NOT NULL DEFAULT '[]',
                    created_at REAL NOT NULL,
                    FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE
                );

                CREATE TABLE IF NOT EXISTS security_audit_logs (
                    log_id TEXT PRIMARY KEY,
                    account_id TEXT,
                    event_type TEXT NOT NULL,
                    ip_address TEXT NOT NULL,
                    device_fingerprint TEXT,
                    status TEXT NOT NULL,
                    details TEXT,
                    timestamp REAL NOT NULL
                );

                CREATE INDEX IF NOT EXISTS idx_accounts_email ON accounts(email);
                CREATE INDEX IF NOT EXISTS idx_accounts_username ON accounts(username);
                CREATE INDEX IF NOT EXISTS idx_characters_account ON characters(account_id);
                CREATE INDEX IF NOT EXISTS idx_audit_account ON security_audit_logs(account_id);
            """)

    def _row_to_account(self, row: sqlite3.Row) -> Account:
        roles = json.loads(row["roles"]) if row["roles"] else ["player"]
        acc = Account(
            account_id=row["account_id"],
            email=row["email"],
            username=row["username"],
            password_hash=row["password_hash"],
            status=AccountStatus(row["status"]),
            roles=roles,
            created_at=row["created_at"],
            last_login_at=row["last_login_at"],
            failed_login_attempts=row["failed_login_attempts"],
            lockout_until=row["lockout_until"],
        )

        # Load OAuth identities
        with self._get_connection() as conn:
            cur = conn.execute(
                "SELECT provider, provider_user_id, email, linked_at FROM oauth_identities WHERE account_id = ?",
                (acc.account_id,),
            )
            for r in cur.fetchall():
                p_type = OAuthProviderType(r["provider"])
                acc.oauth_identities[p_type] = OAuthIdentity(
                    provider=p_type,
                    provider_user_id=r["provider_user_id"],
                    email=r["email"],
                    linked_at=r["linked_at"],
                )

        return acc

    def create_account(
        self,
        email: str,
        password_hash: str,
        username: str,
        status: AccountStatus = AccountStatus.PENDING_ACTIVATION,
        roles: Optional[list[str]] = None,
    ) -> Account:
        account_id = f"acc_{uuid.uuid4().hex[:16]}"
        norm_email = email.strip().lower()
        norm_user = username.strip()
        roles_list = roles if roles is not None else ["player"]
        roles_json = json.dumps(roles_list)
        now = time.time()

        with self._get_connection() as conn:
            conn.execute(
                """
                INSERT INTO accounts (account_id, email, username, password_hash, status, roles, created_at)
                VALUES (?, ?, ?, ?, ?, ?, ?)
                """,
                (account_id, norm_email, norm_user, password_hash, status.value, roles_json, now),
            )
            conn.commit()

        return Account(
            account_id=account_id,
            email=norm_email,
            username=norm_user,
            password_hash=password_hash,
            status=status,
            roles=roles_list,
            created_at=now,
        )

    def get_by_id(self, account_id: str) -> Optional[Account]:
        with self._get_connection() as conn:
            cur = conn.execute("SELECT * FROM accounts WHERE account_id = ?", (account_id,))
            row = cur.fetchone()
            if row:
                return self._row_to_account(row)
        return None

    def get_by_email(self, email: str) -> Optional[Account]:
        norm_email = email.strip().lower()
        with self._get_connection() as conn:
            cur = conn.execute("SELECT * FROM accounts WHERE email = ?", (norm_email,))
            row = cur.fetchone()
            if row:
                return self._row_to_account(row)
        return None

    def get_by_username(self, username: str) -> Optional[Account]:
        norm_user = username.strip().lower()
        with self._get_connection() as conn:
            cur = conn.execute("SELECT * FROM accounts WHERE LOWER(username) = ?", (norm_user,))
            row = cur.fetchone()
            if row:
                return self._row_to_account(row)
        return None

    def get_by_oauth(self, provider: OAuthProviderType, provider_user_id: str) -> Optional[Account]:
        with self._get_connection() as conn:
            cur = conn.execute(
                "SELECT account_id FROM oauth_identities WHERE provider = ? AND provider_user_id = ?",
                (provider.value, provider_user_id),
            )
            row = cur.fetchone()
            if row:
                return self.get_by_id(row["account_id"])
        return None

    def update_account(self, account: Account) -> None:
        roles_json = json.dumps(account.roles)
        with self._get_connection() as conn:
            conn.execute(
                """
                UPDATE accounts
                SET status = ?, roles = ?, last_login_at = ?, failed_login_attempts = ?, lockout_until = ?
                WHERE account_id = ?
                """,
                (
                    account.status.value,
                    roles_json,
                    account.last_login_at,
                    account.failed_login_attempts,
                    account.lockout_until,
                    account.account_id,
                ),
            )
            conn.commit()

    def store_refresh_token(self, refresh_token: str, account_id: str, ttl_seconds: float = 604800.0) -> None:
        now = time.time()
        expires_at = now + ttl_seconds
        with self._get_connection() as conn:
            conn.execute(
                """
                INSERT OR REPLACE INTO refresh_tokens (token_hash, account_id, expires_at, is_revoked, created_at)
                VALUES (?, ?, ?, 0, ?)
                """,
                (refresh_token, account_id, expires_at, now),
            )
            conn.commit()

    def get_account_by_refresh_token(self, refresh_token: str) -> Optional[Account]:
        now = time.time()
        with self._get_connection() as conn:
            cur = conn.execute(
                "SELECT account_id FROM refresh_tokens WHERE token_hash = ? AND is_revoked = 0 AND expires_at > ?",
                (refresh_token, now),
            )
            row = cur.fetchone()
            if row:
                return self.get_by_id(row["account_id"])
        return None

    def revoke_refresh_token(self, refresh_token: str) -> None:
        with self._get_connection() as conn:
            conn.execute("UPDATE refresh_tokens SET is_revoked = 1 WHERE token_hash = ?", (refresh_token,))
            conn.commit()