#!/usr/bin/env python3
"""
Initialisation de la base locale ~/.web-sentinel/auth.db avec un compte SysOp.
Ce script crée les tables attendues par AuthManager et insère (ou met à jour)
le compte développeur bruno@taaazzz-prog.fr avec le rôle SysOp.
"""

from __future__ import annotations

import importlib.util
import json
import sqlite3
from datetime import datetime, timedelta, timezone
from pathlib import Path

DB_DIR = Path.home() / ".web-sentinel"
DB_PATH = DB_DIR / "auth.db"

SYSOP_ACCOUNT = {
    "email": "bruno@taaazzz-prog.fr",
    "password": "@51008473@ZoE@",
    "first_name": "Bruno",
    "last_name": "Taaazzz",
    "role": "sysop",
    "tier": "sysop",
}

TEST_ACCOUNTS = [
    {
        "email": "compteFree@sentinel.fr",
        "password": "123456",
        "first_name": "Compte",
        "last_name": "Free",
        "role": "member",
        "tier": "free",
        "max_domains": 3,
        "max_users": 1,
    },
    {
        "email": "comptePro@sentinel.fr",
        "password": "123456",
        "first_name": "Compte",
        "last_name": "Pro",
        "role": "member",
        "tier": "pro",
        "max_domains": -1,
        "max_users": 5,
    },
    {
        "email": "compteEntreprise@sentinel.fr",
        "password": "123456",
        "first_name": "Compte",
        "last_name": "Enterprise",
        "role": "member",
        "tier": "enterprise",
        "max_domains": -1,
        "max_users": 50,
    },
]


def _load_auth_models():
    """Charger les classes User/AuthProvider/UserRole sans importer tkinter."""
    models_path = Path(__file__).resolve().parents[1] / "web_sentinel" / "auth" / "models.py"
    spec = importlib.util.spec_from_file_location("ws_auth_models", models_path)
    module = importlib.util.module_from_spec(spec)
    spec.loader.exec_module(module)  # type: ignore[attr-defined]
    return module


def _ensure_directory() -> None:
    DB_DIR.mkdir(parents=True, exist_ok=True)


def _create_tables(conn: sqlite3.Connection) -> None:
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS users (
            id TEXT PRIMARY KEY,
            email TEXT UNIQUE NOT NULL,
            username TEXT,
            data TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS sessions (
            session_id TEXT PRIMARY KEY,
            user_id TEXT NOT NULL,
            data TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id)
        )
        """
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS login_attempts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT NOT NULL,
            ip_address TEXT,
            success BOOLEAN NOT NULL,
            error_reason TEXT,
            timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """
    )
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS user_subscriptions (
            user_id TEXT PRIMARY KEY,
            email TEXT UNIQUE NOT NULL,
            tier TEXT NOT NULL DEFAULT 'free',
            expires_at TIMESTAMP,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id)
        )
        """
    )


def _upsert_user(conn: sqlite3.Connection, models_module, account: dict) -> None:
    User = models_module.User
    AuthProvider = models_module.AuthProvider
    UserRole = models_module.UserRole

    user = User(
        email=account["email"],
        first_name=account.get("first_name"),
        last_name=account.get("last_name"),
        auth_provider=AuthProvider.EMAIL,
        email_verified=True,
        role=UserRole(account.get("role", "member")),
    )
    user.set_password(account["password"])
    user_data = user.to_dict()

    cursor = conn.execute("SELECT id FROM users WHERE email = ?", (account["email"],))
    row = cursor.fetchone()

    user_id = row[0] if row else user.id
    user_data["id"] = user_id  # garantir la cohérence
    user_json = json.dumps(user_data, ensure_ascii=False)

    if row:
        conn.execute(
            """
            UPDATE users
            SET data = ?, updated_at = CURRENT_TIMESTAMP
            WHERE email = ?
            """,
            (user_json, account["email"]),
        )
    else:
        conn.execute(
            """
            INSERT INTO users (id, email, username, data)
            VALUES (?, ?, ?, ?)
            """,
            (user_id, account["email"], user.username, user_json),
        )

    expires_at = (datetime.now(timezone.utc) + timedelta(days=365)).isoformat()
    conn.execute(
        """
        INSERT INTO user_subscriptions (user_id, email, tier, expires_at, updated_at)
        VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP)
        ON CONFLICT(user_id) DO UPDATE SET
            tier = excluded.tier,
            expires_at = excluded.expires_at,
            updated_at = CURRENT_TIMESTAMP
        """,
        (user_id, account.get("email"), account.get("tier", "free"), expires_at),
    )


def _seed_accounts(conn: sqlite3.Connection, models_module) -> None:
    accounts = [SYSOP_ACCOUNT, *TEST_ACCOUNTS]
    for account in accounts:
        _upsert_user(conn, models_module, account)


def main() -> None:
    models_module = _load_auth_models()
    _ensure_directory()
    with sqlite3.connect(DB_PATH) as conn:
        conn.execute("PRAGMA foreign_keys = ON;")
        _create_tables(conn)
        _seed_accounts(conn, models_module)
        conn.commit()

    print(f"✅ Base locale initialisée : {DB_PATH}")
    print(f"✅ Compte SysOp configuré pour {SYSOP_ACCOUNT['email']}")
    print(f"🔐 Mot de passe SysOp : {SYSOP_ACCOUNT['password']}")
    for account in TEST_ACCOUNTS:
        print(f"✅ Compte test créé : {account['email']} (tier {account['tier']}) - mot de passe {account['password']}")


if __name__ == "__main__":
    main()