"""
User registry and engagement database management module.
Uses SQLite and standard library cryptography (hashlib.scrypt, hmac.compare_digest).
"""

import os
import sqlite3
import hashlib
import secrets
import hmac
from datetime import datetime

REGISTRY_DB_PATH = os.path.abspath(os.path.join(os.path.dirname(__file__), "workspace.db"))
ENGAGEMENTS_DIR = os.path.abspath(os.path.join(os.path.dirname(__file__), "engagements"))


def init_registry_db():
    """Initialise the workspace database and directory for engagements."""
    os.makedirs(ENGAGEMENTS_DIR, exist_ok=True)
    
    conn = sqlite3.connect(REGISTRY_DB_PATH)
    c = conn.cursor()
    c.execute("""
        CREATE TABLE IF NOT EXISTS users (
            username TEXT PRIMARY KEY,
            password_hash TEXT NOT NULL,
            salt TEXT NOT NULL,
            created_at TEXT NOT NULL
        )
    """)
    c.execute("""
        CREATE TABLE IF NOT EXISTS engagements (
            engagement_key TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            db_path TEXT NOT NULL,
            password_hash TEXT NOT NULL,
            salt TEXT NOT NULL,
            created_at TEXT NOT NULL,
            last_opened TEXT
        )
    """)
    conn.commit()
    conn.close()


def _hash_pwd(password: str) -> tuple[str, str]:
    """Hash a password using hashlib.scrypt with a secure random salt."""
    salt = secrets.token_hex(16)
    pwd_bytes = password.encode("utf-8")
    salt_bytes = salt.encode("utf-8")
    # Using standard secure parameters for scrypt
    h = hashlib.scrypt(pwd_bytes, salt=salt_bytes, n=16384, r=8, p=1)
    return h.hex(), salt


def _verify_pwd(password: str, password_hash: str, salt: str) -> bool:
    """Verify a password against a hash/salt combination using hmac.compare_digest."""
    pwd_bytes = password.encode("utf-8")
    salt_bytes = salt.encode("utf-8")
    h = hashlib.scrypt(pwd_bytes, salt=salt_bytes, n=16384, r=8, p=1)
    return hmac.compare_digest(h.hex(), password_hash)


def create_user(username: str, password: str) -> bool:
    """Create a new user. Returns False if user already exists."""
    username = username.strip().lower()
    if not username or not password:
        return False
        
    conn = sqlite3.connect(REGISTRY_DB_PATH)
    c = conn.cursor()
    c.execute("SELECT 1 FROM users WHERE username = ?", (username,))
    if c.fetchone():
        conn.close()
        return False
        
    pwd_hash, salt = _hash_pwd(password)
    c.execute(
        "INSERT INTO users (username, password_hash, salt, created_at) VALUES (?, ?, ?, ?)",
        (username, pwd_hash, salt, datetime.now().isoformat())
    )
    conn.commit()
    conn.close()
    return True


def verify_user(username: str, password: str) -> bool:
    """Verify username and password credentials."""
    username = username.strip().lower()
    if not username or not password:
        return False
        
    conn = sqlite3.connect(REGISTRY_DB_PATH)
    c = conn.cursor()
    c.execute("SELECT password_hash, salt FROM users WHERE username = ?", (username,))
    row = c.fetchone()
    conn.close()
    
    if not row:
        return False
    return _verify_pwd(password, row[0], row[1])


def count_users() -> int:
    """Return the total number of registered users."""
    conn = sqlite3.connect(REGISTRY_DB_PATH)
    c = conn.cursor()
    c.execute("SELECT COUNT(*) FROM users")
    count = c.fetchone()[0]
    conn.close()
    return count


def create_engagement(engagement_key: str, name: str, password: str) -> tuple[bool, str]:
    """Create and register a new engagement database. Run init_db on it."""
    key = engagement_key.strip().lower()
    name = name.strip()
    if not key or not name or not password:
        return False, "All fields are required."
        
    conn = sqlite3.connect(REGISTRY_DB_PATH)
    c = conn.cursor()
    c.execute("SELECT 1 FROM engagements WHERE engagement_key = ?", (key,))
    if c.fetchone():
        conn.close()
        return False, f"Engagement key '{key}' already exists."
        
    db_filename = f"{key}.db"
    db_path = os.path.abspath(os.path.join(ENGAGEMENTS_DIR, db_filename))
    
    pwd_hash, salt = _hash_pwd(password)
    c.execute("""
        INSERT INTO engagements (engagement_key, name, db_path, password_hash, salt, created_at)
        VALUES (?, ?, ?, ?, ?, ?)
    """, (key, name, db_path, pwd_hash, salt, datetime.now().isoformat()))
    conn.commit()
    conn.close()
    
    # Import ledger_core dynamically to avoid circular imports if any, and initialize the database.
    import ledger_core as core
    core.set_active_db(db_path)
    core.init_db()
    
    return True, db_path


def verify_engagement(engagement_key: str, password: str) -> tuple[bool, str]:
    """Verify engagement key and password. Returns (success, db_path)."""
    key = engagement_key.strip().lower()
    if not key or not password:
        return False, ""
        
    conn = sqlite3.connect(REGISTRY_DB_PATH)
    c = conn.cursor()
    c.execute("SELECT password_hash, salt, db_path FROM engagements WHERE engagement_key = ?", (key,))
    row = c.fetchone()
    conn.close()
    
    if not row:
        return False, ""
        
    if _verify_pwd(password, row[0], row[1]):
        return True, row[2]
    return False, ""


def get_engagement_db_path(engagement_key: str) -> str:
    """Return the db_path for an engagement key, or '' if not found. No password check."""
    key = engagement_key.strip().lower()
    conn = sqlite3.connect(REGISTRY_DB_PATH)
    c = conn.cursor()
    c.execute("SELECT db_path FROM engagements WHERE engagement_key = ?", (key,))
    row = c.fetchone()
    conn.close()
    return row[0] if row else ""


def get_engagements() -> list:
    """Retrieve all registered engagements."""
    conn = sqlite3.connect(REGISTRY_DB_PATH)
    c = conn.cursor()
    c.execute("SELECT engagement_key, name, db_path, last_opened FROM engagements ORDER BY name")
    rows = c.fetchall()
    conn.close()
    return rows


def update_engagement_last_opened(engagement_key: str):
    """Update the last_opened timestamp for an engagement."""
    key = engagement_key.strip().lower()
    conn = sqlite3.connect(REGISTRY_DB_PATH)
    c = conn.cursor()
    c.execute(
        "UPDATE engagements SET last_opened = ? WHERE engagement_key = ?",
        (datetime.now().isoformat(), key)
    )
    conn.commit()
    conn.close()


# Initialize upon loading
init_registry_db()