TicketTracker/tickettracker/db/schema.py

"""
Schéma SQLite pour TicketTracker.

Ce module gère uniquement le DDL (création des tables, vues et index).
Il ne contient pas de logique métier.

Tables :
  receipts     — un ticket de courses par ligne
  items        — articles, liés à leur ticket par FK
Vue :
  price_history — jointure items × receipts pour comparer les prix dans le temps
"""

import sqlite3
from pathlib import Path

# Chemin par défaut : data/tickettracker.db à la racine du projet
DEFAULT_DB_PATH = Path(__file__).parent.parent.parent / "data" / "tickettracker.db"

# ---------------------------------------------------------------------------
# Instructions DDL (CREATE TABLE / INDEX / VIEW)
# ---------------------------------------------------------------------------

_SQL_CREATE_RECEIPTS = """
CREATE TABLE IF NOT EXISTS receipts (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    store        TEXT    NOT NULL,
    date         TEXT    NOT NULL,      -- format ISO 8601 : "2026-02-14"
    total        REAL    NOT NULL,
    delivery_fee REAL,                  -- NULL pour Leclerc (magasin physique)
    order_id     TEXT,                  -- NULL si non disponible
    raw_json     TEXT    NOT NULL,      -- résultat de receipt.to_json() pour debug
    created_at   TEXT    NOT NULL       -- datetime UTC ISO au moment de l'insertion
);
"""

_SQL_CREATE_RECEIPTS_IDX = """
CREATE INDEX IF NOT EXISTS idx_receipts_dedup
    ON receipts (store, date, total);
"""

_SQL_CREATE_ITEMS = """
CREATE TABLE IF NOT EXISTS items (
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    receipt_id       INTEGER NOT NULL REFERENCES receipts(id),
    name_raw         TEXT    NOT NULL,  -- nom tel que sorti du parser
    name_normalized  TEXT,              -- NULL jusqu'au Sprint 3 (normalisation LLM)
    category         TEXT,              -- NULL pour Picnic (pas de catégories dans le mail)
    quantity         REAL    NOT NULL,
    unit             TEXT    NOT NULL,
    unit_price       REAL    NOT NULL,
    total_price      REAL    NOT NULL
);
"""

_SQL_CREATE_ITEMS_IDX = """
CREATE INDEX IF NOT EXISTS idx_items_receipt_id
    ON items (receipt_id);
"""

_SQL_CREATE_ITEMS_NORM_IDX = """
CREATE INDEX IF NOT EXISTS idx_items_name_normalized
    ON items (name_normalized);
"""

_SQL_CREATE_PRODUCT_MATCHES = """
CREATE TABLE IF NOT EXISTS product_matches (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    name_picnic  TEXT    NOT NULL,
    name_leclerc TEXT    NOT NULL,
    score        REAL    NOT NULL,       -- score RapidFuzz 0-100
    status       TEXT    NOT NULL DEFAULT 'pending', -- 'pending'|'validated'|'rejected'
    created_at   TEXT    NOT NULL,
    UNIQUE(name_picnic, name_leclerc)
);
"""

_SQL_CREATE_PRODUCT_MATCHES_IDX = """
CREATE INDEX IF NOT EXISTS idx_product_matches_status
    ON product_matches (status);
"""

_SQL_CREATE_PRICE_HISTORY = """
CREATE VIEW IF NOT EXISTS price_history AS
SELECT
    i.name_normalized,
    r.store,
    r.date,
    i.unit_price,
    i.total_price,
    i.quantity,
    i.unit,
    i.category
FROM items i
JOIN receipts r ON i.receipt_id = r.id;
"""


# ---------------------------------------------------------------------------
# Fonctions publiques
# ---------------------------------------------------------------------------

def get_connection(db_path: str | Path = DEFAULT_DB_PATH) -> sqlite3.Connection:
    """Ouvre une connexion SQLite avec les pragmas requis.

    Active les clés étrangères (désactivées par défaut dans SQLite —
    le pragma doit être réappliqué à chaque nouvelle connexion).
    Configure row_factory = sqlite3.Row pour accéder aux colonnes par nom.

    Args:
        db_path: Chemin vers le fichier .db (créé automatiquement si absent).

    Returns:
        Connexion sqlite3 configurée.
    """
    conn = sqlite3.connect(str(db_path))
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    return conn


def init_db(db_path: str | Path = DEFAULT_DB_PATH) -> None:
    """Crée les tables, index et vues s'ils n'existent pas encore.

    Idempotent : peut être appelé plusieurs fois sans erreur grâce aux
    clauses CREATE TABLE IF NOT EXISTS / CREATE INDEX IF NOT EXISTS.
    Crée le dossier parent (data/) s'il n'existe pas.

    Args:
        db_path: Chemin vers le fichier .db.

    Raises:
        PermissionError: Si le système de fichiers refuse la création du dossier.
    """
    db_path = Path(db_path)
    db_path.parent.mkdir(parents=True, exist_ok=True)

    with get_connection(db_path) as conn:
        conn.execute(_SQL_CREATE_RECEIPTS)
        conn.execute(_SQL_CREATE_RECEIPTS_IDX)
        conn.execute(_SQL_CREATE_ITEMS)
        conn.execute(_SQL_CREATE_ITEMS_IDX)
        conn.execute(_SQL_CREATE_ITEMS_NORM_IDX)
        conn.execute(_SQL_CREATE_PRICE_HISTORY)
        conn.execute(_SQL_CREATE_PRODUCT_MATCHES)
        conn.execute(_SQL_CREATE_PRODUCT_MATCHES_IDX)