import os
import sqlite3
from pathlib import Path


ROOT = Path(__file__).resolve().parent
ALLOWED_READ_TABLES = {"clients", "projects", "tasks", "repositories", "memories"}
FORBIDDEN_SQL_WORDS = {
    "insert",
    "update",
    "delete",
    "drop",
    "alter",
    "create",
    "replace",
    "truncate",
    "attach",
    "detach",
    "pragma",
    "vacuum",
    "reindex",
}


def get_memory_db():
    return Path(os.getenv("MEMORY_DB", ROOT / "data" / "memory.sqlite"))


def connect():
    memory_db = get_memory_db()
    memory_db.parent.mkdir(parents=True, exist_ok=True)
    connection = sqlite3.connect(memory_db)
    connection.row_factory = sqlite3.Row
    connection.execute("PRAGMA foreign_keys = ON")
    return connection


def init_db():
    with connect() as db:
        db.executescript(
            """
            CREATE TABLE IF NOT EXISTS clients (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL UNIQUE COLLATE NOCASE,
                status TEXT DEFAULT 'actif',
                notes TEXT DEFAULT '',
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            );

            CREATE TABLE IF NOT EXISTS projects (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                client_id INTEGER REFERENCES clients(id) ON DELETE SET NULL,
                name TEXT NOT NULL UNIQUE COLLATE NOCASE,
                status TEXT DEFAULT 'actif',
                summary TEXT DEFAULT '',
                next_action TEXT DEFAULT '',
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            );

            CREATE TABLE IF NOT EXISTS tasks (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                client_id INTEGER REFERENCES clients(id) ON DELETE SET NULL,
                project_id INTEGER REFERENCES projects(id) ON DELETE SET NULL,
                title TEXT NOT NULL,
                details TEXT DEFAULT '',
                due_at TEXT DEFAULT '',
                done INTEGER NOT NULL DEFAULT 0,
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            );

            CREATE TABLE IF NOT EXISTS repositories (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_id INTEGER REFERENCES projects(id) ON DELETE SET NULL,
                name TEXT NOT NULL,
                local_path TEXT DEFAULT '',
                remote_url TEXT DEFAULT '',
                main_branch TEXT DEFAULT '',
                last_indexed_at TEXT DEFAULT '',
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(name, local_path, remote_url)
            );

            CREATE TABLE IF NOT EXISTS memories (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                client_id INTEGER REFERENCES clients(id) ON DELETE SET NULL,
                project_id INTEGER REFERENCES projects(id) ON DELETE SET NULL,
                kind TEXT NOT NULL DEFAULT 'note',
                subject TEXT DEFAULT '',
                content TEXT NOT NULL,
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            );
            """
        )


def row_to_dict(row):
    return dict(row) if row else None


def execute_read_query(sql, limit=100):
    sql = normalize_read_sql(sql)
    limited_sql = f"SELECT * FROM ({sql}) AS readonly_query LIMIT ?"

    with connect() as db:
        db.set_authorizer(readonly_authorizer)
        rows = db.execute(limited_sql, (limit,)).fetchall()
        return [row_to_dict(row) for row in rows]


def normalize_read_sql(sql):
    sql = clean_text(sql).rstrip(";")
    sql_lower = sql.lower()
    if not sql_lower.startswith("select "):
        raise ValueError("Seules les requetes SELECT sont autorisees")
    if ";" in sql:
        raise ValueError("Une seule requete SELECT est autorisee")
    if "--" in sql or "/*" in sql or "*/" in sql:
        raise ValueError("Les commentaires SQL ne sont pas autorises")
    for word in FORBIDDEN_SQL_WORDS:
        if re_word_match(sql_lower, word):
            raise ValueError(f"Mot SQL interdit: {word}")
    return sql


def re_word_match(text, word):
    import re

    return re.search(rf"\b{re.escape(word)}\b", text) is not None


def readonly_authorizer(action, arg1, arg2, db_name, trigger_name):
    if action == sqlite3.SQLITE_SELECT:
        return sqlite3.SQLITE_OK
    if action == sqlite3.SQLITE_READ:
        table_name = arg1 or ""
        if table_name in ALLOWED_READ_TABLES:
            return sqlite3.SQLITE_OK
        return sqlite3.SQLITE_DENY
    if action == sqlite3.SQLITE_FUNCTION:
        return sqlite3.SQLITE_OK
    return sqlite3.SQLITE_DENY


def find_client(db, name):
    if not name:
        return None
    return db.execute(
        "SELECT * FROM clients WHERE name = ? COLLATE NOCASE",
        (clean_text(name),),
    ).fetchone()


def find_project(db, name):
    if not name:
        return None
    return db.execute(
        "SELECT * FROM projects WHERE name = ? COLLATE NOCASE",
        (clean_text(name),),
    ).fetchone()


def upsert_client(name, notes="", status="actif"):
    name = clean_text(name)
    notes = clean_text(notes)
    status = clean_text(status) or "actif"
    if not name:
        raise ValueError("Nom de client manquant")

    with connect() as db:
        existing = find_client(db, name)
        if existing:
            merged_notes = merge_notes(existing["notes"], notes)
            db.execute(
                """
                UPDATE clients
                SET status = ?, notes = ?, updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
                """,
                (status, merged_notes, existing["id"]),
            )
            client_id = existing["id"]
        else:
            cursor = db.execute(
                "INSERT INTO clients (name, status, notes) VALUES (?, ?, ?)",
                (name, status, notes),
            )
            client_id = cursor.lastrowid
        return row_to_dict(db.execute("SELECT * FROM clients WHERE id = ?", (client_id,)).fetchone())


def deactivate_client(name):
    name = clean_text(name)
    if not name:
        raise ValueError("Nom de client manquant")

    with connect() as db:
        row = find_client(db, name)
        if not row:
            return None
        db.execute(
            """
            UPDATE clients
            SET status = 'inactif', updated_at = CURRENT_TIMESTAMP
            WHERE id = ?
            """,
            (row["id"],),
        )
        return row_to_dict(row)


def upsert_project(name, client_name="", status="actif", summary="", next_action=""):
    name = clean_text(name)
    if not name:
        raise ValueError("Nom de projet manquant")

    client_id = None
    with connect() as db:
        if client_name:
            client = find_client(db, client_name)
            if client is None:
                cursor = db.execute(
                    "INSERT INTO clients (name, notes) VALUES (?, ?)",
                    (clean_text(client_name), "Ajoute automatiquement depuis un projet."),
                )
                client_id = cursor.lastrowid
            else:
                client_id = client["id"]

        existing = find_project(db, name)
        if existing:
            db.execute(
                """
                UPDATE projects
                SET client_id = COALESCE(?, client_id),
                    status = COALESCE(NULLIF(?, ''), status),
                    summary = COALESCE(NULLIF(?, ''), summary),
                    next_action = COALESCE(NULLIF(?, ''), next_action),
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
                """,
                (client_id, clean_text(status), clean_text(summary), clean_text(next_action), existing["id"]),
            )
            project_id = existing["id"]
        else:
            cursor = db.execute(
                """
                INSERT INTO projects (client_id, name, status, summary, next_action)
                VALUES (?, ?, ?, ?, ?)
                """,
                (client_id, name, clean_text(status) or "actif", clean_text(summary), clean_text(next_action)),
            )
            project_id = cursor.lastrowid
        return row_to_dict(db.execute("SELECT * FROM projects WHERE id = ?", (project_id,)).fetchone())


def add_task(title, client_name="", project_name="", details="", due_at=""):
    title = clean_text(title)
    if not title:
        raise ValueError("Titre de tache manquant")

    with connect() as db:
        client_id = ensure_client_id(db, client_name)
        project_id = ensure_project_id(db, project_name, client_id)
        cursor = db.execute(
            """
            INSERT INTO tasks (client_id, project_id, title, details, due_at)
            VALUES (?, ?, ?, ?, ?)
            """,
            (client_id, project_id, title, clean_text(details), clean_text(due_at)),
        )
        return row_to_dict(db.execute("SELECT * FROM tasks WHERE id = ?", (cursor.lastrowid,)).fetchone())


def list_clients(limit=100):
    with connect() as db:
        rows = db.execute(
            """
            SELECT * FROM clients
            WHERE status != 'inactif'
            ORDER BY name COLLATE NOCASE
            LIMIT ?
            """,
            (limit,),
        ).fetchall()
        return [row_to_dict(row) for row in rows]


def list_open_tasks(limit=100):
    with connect() as db:
        rows = db.execute(
            """
            SELECT t.*, c.name AS client_name, p.name AS project_name
            FROM tasks t
            LEFT JOIN clients c ON c.id = t.client_id
            LEFT JOIN projects p ON p.id = t.project_id
            WHERE t.done = 0
              AND (c.id IS NULL OR c.status != 'inactif')
            ORDER BY COALESCE(c.name, p.name, 'zzzz') COLLATE NOCASE, t.created_at DESC
            LIMIT ?
            """,
            (limit,),
        ).fetchall()
        return [row_to_dict(row) for row in rows]


def get_memory_snapshot(limit=25):
    with connect() as db:
        clients = db.execute(
            """
            SELECT c.*,
                   COUNT(t.id) AS open_task_count
            FROM clients c
            LEFT JOIN tasks t ON t.client_id = c.id AND t.done = 0
            WHERE c.status != 'inactif'
            GROUP BY c.id
            ORDER BY c.name COLLATE NOCASE
            LIMIT ?
            """,
            (limit,),
        ).fetchall()
        tasks = db.execute(
            """
            SELECT t.*, c.name AS client_name, p.name AS project_name
            FROM tasks t
            LEFT JOIN clients c ON c.id = t.client_id
            LEFT JOIN projects p ON p.id = t.project_id
            WHERE t.done = 0
            ORDER BY t.created_at DESC
            LIMIT ?
            """,
            (limit,),
        ).fetchall()
        projects = db.execute(
            """
            SELECT p.*, c.name AS client_name
            FROM projects p
            LEFT JOIN clients c ON c.id = p.client_id
            ORDER BY p.updated_at DESC
            LIMIT ?
            """,
            (limit,),
        ).fetchall()
        repos = db.execute(
            """
            SELECT r.*, p.name AS project_name
            FROM repositories r
            LEFT JOIN projects p ON p.id = r.project_id
            ORDER BY r.updated_at DESC
            LIMIT ?
            """,
            (limit,),
        ).fetchall()
        return {
            "clients": [row_to_dict(row) for row in clients],
            "tasks": [row_to_dict(row) for row in tasks],
            "projects": [row_to_dict(row) for row in projects],
            "repositories": [row_to_dict(row) for row in repos],
        }


def close_task(task_id):
    with connect() as db:
        row = db.execute(
            """
            SELECT t.*, c.name AS client_name, p.name AS project_name
            FROM tasks t
            LEFT JOIN clients c ON c.id = t.client_id
            LEFT JOIN projects p ON p.id = t.project_id
            WHERE t.id = ? AND t.done = 0
            """,
            (task_id,),
        ).fetchone()
        if not row:
            return None
        db.execute(
            "UPDATE tasks SET done = 1, updated_at = CURRENT_TIMESTAMP WHERE id = ?",
            (task_id,),
        )
        return row_to_dict(row)


def add_repository(name, project_name="", local_path="", remote_url="", main_branch=""):
    name = clean_text(name) or derive_repo_name(local_path, remote_url)
    if not name:
        raise ValueError("Nom de depot manquant")

    with connect() as db:
        project_id = ensure_project_id(db, project_name, None)
        db.execute(
            """
            INSERT INTO repositories (project_id, name, local_path, remote_url, main_branch)
            VALUES (?, ?, ?, ?, ?)
            ON CONFLICT(name, local_path, remote_url) DO UPDATE SET
                project_id = COALESCE(excluded.project_id, repositories.project_id),
                main_branch = COALESCE(NULLIF(excluded.main_branch, ''), repositories.main_branch),
                updated_at = CURRENT_TIMESTAMP
            """,
            (project_id, name, clean_text(local_path), clean_text(remote_url), clean_text(main_branch)),
        )
        return row_to_dict(
            db.execute(
                """
                SELECT * FROM repositories
                WHERE name = ? AND local_path = ? AND remote_url = ?
                """,
                (name, clean_text(local_path), clean_text(remote_url)),
            ).fetchone()
        )


def add_memory(content, kind="note", subject="", client_name="", project_name=""):
    content = clean_text(content)
    if not content:
        raise ValueError("Memoire vide")

    with connect() as db:
        client_id = ensure_client_id(db, client_name)
        project_id = ensure_project_id(db, project_name, client_id)
        cursor = db.execute(
            """
            INSERT INTO memories (client_id, project_id, kind, subject, content)
            VALUES (?, ?, ?, ?, ?)
            """,
            (client_id, project_id, clean_text(kind) or "note", clean_text(subject), content),
        )
        return row_to_dict(db.execute("SELECT * FROM memories WHERE id = ?", (cursor.lastrowid,)).fetchone())


def search_memory(query="", limit=12):
    query = clean_text(query)
    like = f"%{query}%"
    search_long_text = int(len(query) > 4)
    with connect() as db:
        if query:
            clients = db.execute(
                """
                SELECT * FROM clients
                WHERE name LIKE ? OR (? = 1 AND notes LIKE ?)
                ORDER BY
                    CASE WHEN name = ? COLLATE NOCASE THEN 0 ELSE 1 END,
                    updated_at DESC
                LIMIT ?
                """,
                (like, search_long_text, like, query, limit),
            ).fetchall()
            projects = db.execute(
                """
                SELECT p.*, c.name AS client_name
                FROM projects p
                LEFT JOIN clients c ON c.id = p.client_id
                WHERE p.name LIKE ?
                   OR c.name LIKE ?
                   OR (? = 1 AND (p.summary LIKE ? OR p.next_action LIKE ?))
                ORDER BY
                    CASE WHEN p.name = ? COLLATE NOCASE OR c.name = ? COLLATE NOCASE THEN 0 ELSE 1 END,
                    p.updated_at DESC
                LIMIT ?
                """,
                (like, like, search_long_text, like, like, query, query, limit),
            ).fetchall()
            tasks = db.execute(
                """
                SELECT t.*, c.name AS client_name, p.name AS project_name
                FROM tasks t
                LEFT JOIN clients c ON c.id = t.client_id
                LEFT JOIN projects p ON p.id = t.project_id
                WHERE t.done = 0 AND (t.title LIKE ? OR t.details LIKE ? OR c.name LIKE ? OR p.name LIKE ?)
                ORDER BY
                    CASE WHEN c.name = ? COLLATE NOCASE OR p.name = ? COLLATE NOCASE THEN 0 ELSE 1 END,
                    t.created_at DESC
                LIMIT ?
                """,
                (like, like, like, like, query, query, limit),
            ).fetchall()
            repos = db.execute(
                """
                SELECT r.*, p.name AS project_name
                FROM repositories r
                LEFT JOIN projects p ON p.id = r.project_id
                WHERE r.name LIKE ? OR r.local_path LIKE ? OR r.remote_url LIKE ? OR p.name LIKE ?
                ORDER BY
                    CASE WHEN r.name = ? COLLATE NOCASE OR p.name = ? COLLATE NOCASE THEN 0 ELSE 1 END,
                    r.updated_at DESC
                LIMIT ?
                """,
                (like, like, like, like, query, query, limit),
            ).fetchall()
            notes = db.execute(
                """
                SELECT m.*, c.name AS client_name, p.name AS project_name
                FROM memories m
                LEFT JOIN clients c ON c.id = m.client_id
                LEFT JOIN projects p ON p.id = m.project_id
                WHERE c.name LIKE ?
                   OR p.name LIKE ?
                   OR (? = 1 AND (m.subject LIKE ? OR m.content LIKE ?))
                ORDER BY m.created_at DESC LIMIT ?
                """,
                (like, like, search_long_text, like, like, limit),
            ).fetchall()
        else:
            clients = db.execute("SELECT * FROM clients ORDER BY updated_at DESC LIMIT ?", (limit,)).fetchall()
            projects = db.execute(
                """
                SELECT p.*, c.name AS client_name
                FROM projects p
                LEFT JOIN clients c ON c.id = p.client_id
                ORDER BY p.updated_at DESC LIMIT ?
                """,
                (limit,),
            ).fetchall()
            tasks = db.execute(
                """
                SELECT t.*, c.name AS client_name, p.name AS project_name
                FROM tasks t
                LEFT JOIN clients c ON c.id = t.client_id
                LEFT JOIN projects p ON p.id = t.project_id
                WHERE t.done = 0
                ORDER BY t.created_at DESC LIMIT ?
                """,
                (limit,),
            ).fetchall()
            repos = db.execute(
                """
                SELECT r.*, p.name AS project_name
                FROM repositories r
                LEFT JOIN projects p ON p.id = r.project_id
                ORDER BY r.updated_at DESC LIMIT ?
                """,
                (limit,),
            ).fetchall()
            notes = db.execute(
                """
                SELECT m.*, c.name AS client_name, p.name AS project_name
                FROM memories m
                LEFT JOIN clients c ON c.id = m.client_id
                LEFT JOIN projects p ON p.id = m.project_id
                ORDER BY m.created_at DESC LIMIT ?
                """,
                (limit,),
            ).fetchall()

    return {
        "clients": [row_to_dict(row) for row in clients],
        "projects": [row_to_dict(row) for row in projects],
        "tasks": [row_to_dict(row) for row in tasks],
        "repositories": [row_to_dict(row) for row in repos],
        "memories": [row_to_dict(row) for row in notes],
    }


def ensure_client_id(db, client_name):
    client_name = clean_text(client_name)
    if not client_name:
        return None
    client = find_client(db, client_name)
    if client:
        return client["id"]
    cursor = db.execute("INSERT INTO clients (name) VALUES (?)", (client_name,))
    return cursor.lastrowid


def ensure_project_id(db, project_name, client_id):
    project_name = clean_text(project_name)
    if not project_name:
        return None
    project = find_project(db, project_name)
    if project:
        if client_id and not project["client_id"]:
            db.execute(
                "UPDATE projects SET client_id = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?",
                (client_id, project["id"]),
            )
        return project["id"]
    cursor = db.execute(
        "INSERT INTO projects (client_id, name) VALUES (?, ?)",
        (client_id, project_name),
    )
    return cursor.lastrowid


def merge_notes(existing, new):
    existing = clean_text(existing)
    new = clean_text(new)
    if not new:
        return existing
    if not existing:
        return new
    if new.lower() in existing.lower():
        return existing
    return f"{existing}\n{new}"


def clean_text(value):
    return " ".join(str(value or "").strip().split())


def derive_repo_name(local_path, remote_url):
    source = clean_text(local_path) or clean_text(remote_url)
    if not source:
        return ""
    name = source.rstrip("/\\").split("/")[-1].split("\\")[-1]
    return name.removesuffix(".git")