"""Shared SQLite conversation persistence for Chanakya agents.

Every agent (gmail, jira, chanakya) uses this module with its own database
file.  Call ``set_db_path(path)`` once at agent startup (or at module import
via an env-var-driven default) before calling any other function.

Schema is forward-compatible with future authentication strategies.
The ``users.auth_provider`` column supports: 'mock', 'google', 'local'.
"""

import json
import sqlite3
import uuid
from datetime import datetime, timezone
from pathlib import Path
from typing import Optional

# Module-level path — overridden per agent via set_db_path().
_DB_PATH: Path = Path(__file__).parents[2] / "data" / "chanakya" / "conversations.db"

MOCK_USER_ID = "user-mock-001"


# ---------------------------------------------------------------------------
# Configuration
# ---------------------------------------------------------------------------

def set_db_path(path: str | Path) -> None:
    """Configure the database file path for this process.

    Must be called before ``init_db()`` and any CRUD function.
    """
    global _DB_PATH
    _DB_PATH = Path(path)
    _DB_PATH.parent.mkdir(parents=True, exist_ok=True)


# ---------------------------------------------------------------------------
# Internal helpers
# ---------------------------------------------------------------------------

def _get_conn() -> sqlite3.Connection:
    conn = sqlite3.connect(str(_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


# ---------------------------------------------------------------------------
# Schema init
# ---------------------------------------------------------------------------

def init_db() -> None:
    """Create all tables and seed the mock user. Safe to call on every startup."""
    _DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    with _get_conn() as conn:
        conn.executescript(
            """
            CREATE TABLE IF NOT EXISTS users (
                id            TEXT PRIMARY KEY,
                username      TEXT NOT NULL,
                email         TEXT,
                auth_provider TEXT NOT NULL DEFAULT 'mock',
                created_at    TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS sessions (
                id         TEXT PRIMARY KEY,
                user_id    TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                title      TEXT NOT NULL DEFAULT 'New Chat',
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS messages (
                id         TEXT PRIMARY KEY,
                session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
                role       TEXT NOT NULL CHECK(role IN ('user', 'assistant')),
                content    TEXT NOT NULL,
                created_at TEXT NOT NULL
            );

            INSERT OR IGNORE INTO users (id, username, email, auth_provider, created_at)
            VALUES (
                'user-mock-001',
                'Enterprise User',
                'enterprise@example.com',
                'mock',
                datetime('now')
            );
            """
        )


# ---------------------------------------------------------------------------
# Session CRUD
# ---------------------------------------------------------------------------

def create_session(user_id: str = MOCK_USER_ID, title: str = "New Chat") -> dict:
    session_id = str(uuid.uuid4())
    now = datetime.now(timezone.utc).isoformat()
    with _get_conn() as conn:
        conn.execute(
            "INSERT INTO sessions (id, user_id, title, created_at, updated_at) VALUES (?, ?, ?, ?, ?)",
            (session_id, user_id, title, now, now),
        )
    return {
        "id": session_id,
        "user_id": user_id,
        "title": title,
        "created_at": now,
        "updated_at": now,
        "last_message": None,
    }


def get_sessions(user_id: str = MOCK_USER_ID) -> list[dict]:
    with _get_conn() as conn:
        rows = conn.execute(
            """
            SELECT
                s.id,
                s.user_id,
                s.title,
                s.created_at,
                s.updated_at,
                (
                    SELECT content
                    FROM messages
                    WHERE session_id = s.id
                      AND role = 'user'
                    ORDER BY created_at ASC
                    LIMIT 1
                ) AS last_message
            FROM sessions s
            WHERE s.user_id = ?
            ORDER BY s.updated_at DESC
            """,
            (user_id,),
        ).fetchall()
    return [dict(r) for r in rows]


def get_session(session_id: str) -> Optional[dict]:
    with _get_conn() as conn:
        row = conn.execute(
            "SELECT * FROM sessions WHERE id = ?", (session_id,)
        ).fetchone()
    return dict(row) if row else None


def update_session_title(session_id: str, title: str) -> None:
    now = datetime.now(timezone.utc).isoformat()
    with _get_conn() as conn:
        conn.execute(
            "UPDATE sessions SET title = ?, updated_at = ? WHERE id = ?",
            (title, now, session_id),
        )


def delete_session(session_id: str) -> bool:
    with _get_conn() as conn:
        cursor = conn.execute("DELETE FROM sessions WHERE id = ?", (session_id,))
    return cursor.rowcount > 0


# ---------------------------------------------------------------------------
# Message CRUD
# ---------------------------------------------------------------------------

def save_message(session_id: str, role: str, content: str) -> dict:
    msg_id = str(uuid.uuid4())
    now = datetime.now(timezone.utc).isoformat()
    with _get_conn() as conn:
        conn.execute(
            "INSERT INTO messages (id, session_id, role, content, created_at) VALUES (?, ?, ?, ?, ?)",
            (msg_id, session_id, role, content, now),
        )
        conn.execute(
            "UPDATE sessions SET updated_at = ? WHERE id = ?",
            (now, session_id),
        )
    return {
        "id": msg_id,
        "session_id": session_id,
        "role": role,
        "content": content,
        "created_at": now,
    }


def get_messages(session_id: str) -> list[dict]:
    with _get_conn() as conn:
        rows = conn.execute(
            """
            SELECT id, session_id, role, content, created_at
            FROM messages
            WHERE session_id = ?
            ORDER BY created_at ASC
            """,
            (session_id,),
        ).fetchall()
    return [dict(r) for r in rows]