-- AccessMiner AI — schéma SQLite du fichier projet .amproj
-- Un .amproj représente un projet AccessMiner : une mission, un client, un écosystème.
-- Un projet peut contenir plusieurs bases Access (multi-bases, ADR-004).

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;

------------------------------------------------------------
-- Méta du projet lui-même
------------------------------------------------------------
CREATE TABLE IF NOT EXISTS project (
    id              INTEGER PRIMARY KEY CHECK (id = 1),
    name            TEXT NOT NULL,
    client          TEXT,
    created_at      TEXT NOT NULL,
    accessminer_version TEXT NOT NULL,
    schema_version  INTEGER NOT NULL DEFAULT 1
);

------------------------------------------------------------
-- Une "source" = une base Access scannée dans ce projet
------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sources (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    display_name    TEXT NOT NULL,             -- nom sans extension, normalisé
    original_path   TEXT NOT NULL,             -- chemin d'origine (pour traçabilité)
    size_bytes      INTEGER,
    scanned_at      TEXT NOT NULL,
    schema_time_s   REAL,
    com_time_s      REAL,
    scan_status     TEXT NOT NULL DEFAULT 'ok' -- ok|partial|failed
);

------------------------------------------------------------
-- Objets : unité de base du dictionnaire. Tout est un objet.
-- kind : 'table' | 'query' | 'form' | 'report' | 'macro' | 'module'
--      | 'column' | 'index' | 'relation' | 'event'
------------------------------------------------------------
CREATE TABLE IF NOT EXISTS objects (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    source_id       INTEGER NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
    kind            TEXT NOT NULL,
    name            TEXT NOT NULL,
    normalized_name TEXT NOT NULL,             -- sans accents, lower, trim
    parent_id       INTEGER REFERENCES objects(id) ON DELETE CASCADE,
    properties      TEXT,                      -- JSON : propriétés spécifiques au kind
    content         TEXT,                      -- code VBA, SQL, macro XML, etc.
    UNIQUE (source_id, kind, name, parent_id)
);

CREATE INDEX IF NOT EXISTS idx_objects_source_kind ON objects(source_id, kind);
CREATE INDEX IF NOT EXISTS idx_objects_normalized ON objects(normalized_name);
CREATE INDEX IF NOT EXISTS idx_objects_parent ON objects(parent_id);

------------------------------------------------------------
-- Edges : graphe de dépendances entre objets
-- edge_kind :
--   'uses_table'   (requête/form/report utilise une table)
--   'uses_query'   (requête/form/report utilise une requête)
--   'calls'        (VBA appelle une procédure)
--   'triggers'     (événement appelle un module)
--   'links_fk'     (FK formelle Access.Relations)
--   'joins_on'     (FK implicite reconstruite à partir d'un JOIN SQL)
------------------------------------------------------------
CREATE TABLE IF NOT EXISTS edges (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    source_obj_id   INTEGER NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
    target_obj_id   INTEGER NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
    edge_kind       TEXT NOT NULL,
    confidence      REAL NOT NULL DEFAULT 1.0, -- 1.0 = certain, 0.x = implicite/heuristique
    context         TEXT                        -- JSON : origine du lien (query id, ligne VBA, etc.)
);

CREATE INDEX IF NOT EXISTS idx_edges_source ON edges(source_obj_id, edge_kind);
CREATE INDEX IF NOT EXISTS idx_edges_target ON edges(target_obj_id, edge_kind);

------------------------------------------------------------
-- Runs : historique des scans (pour traçabilité et debug)
------------------------------------------------------------
CREATE TABLE IF NOT EXISTS runs (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    started_at      TEXT NOT NULL,
    ended_at        TEXT,
    status          TEXT NOT NULL,             -- running|ok|failed|partial
    sources_count   INTEGER,
    details         TEXT                        -- JSON : logs, erreurs par objet
);

------------------------------------------------------------
-- Erreurs de scan non fatales, par source, pour diagnostic a froid
-- phase       : 'extract_schema' | 'extract_com' | 'persist_*'
-- error_kind  : 'macro_failed' | 'form_failed' | 'module_failed' | ...
-- object_name : nom de l'objet qui a echoue (si applicable)
------------------------------------------------------------
CREATE TABLE IF NOT EXISTS scan_errors (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    source_id       INTEGER NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
    phase           TEXT NOT NULL,
    error_kind      TEXT NOT NULL,
    object_name     TEXT,
    message         TEXT
);

CREATE INDEX IF NOT EXISTS idx_scan_errors_source ON scan_errors(source_id);
CREATE INDEX IF NOT EXISTS idx_scan_errors_kind ON scan_errors(error_kind);

------------------------------------------------------------
-- Annotations utilisateur (post-it sur un objet, ADR MVP)
------------------------------------------------------------
CREATE TABLE IF NOT EXISTS annotations (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    object_id       INTEGER NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
    author          TEXT,
    created_at      TEXT NOT NULL,
    body            TEXT NOT NULL
);

------------------------------------------------------------
-- Recherche plein texte (FTS5) sur le contenu et le nom
------------------------------------------------------------
CREATE VIRTUAL TABLE IF NOT EXISTS objects_fts USING fts5(
    name,
    normalized_name,
    content,
    content='objects',
    content_rowid='id',
    tokenize = 'unicode61 remove_diacritics 2'
);

-- Triggers pour maintenir l'index FTS à jour
CREATE TRIGGER IF NOT EXISTS objects_ai AFTER INSERT ON objects BEGIN
    INSERT INTO objects_fts(rowid, name, normalized_name, content)
    VALUES (new.id, new.name, new.normalized_name, coalesce(new.content,''));
END;
CREATE TRIGGER IF NOT EXISTS objects_ad AFTER DELETE ON objects BEGIN
    INSERT INTO objects_fts(objects_fts, rowid, name, normalized_name, content)
    VALUES ('delete', old.id, old.name, old.normalized_name, coalesce(old.content,''));
END;
CREATE TRIGGER IF NOT EXISTS objects_au AFTER UPDATE ON objects BEGIN
    INSERT INTO objects_fts(objects_fts, rowid, name, normalized_name, content)
    VALUES ('delete', old.id, old.name, old.normalized_name, coalesce(old.content,''));
    INSERT INTO objects_fts(rowid, name, normalized_name, content)
    VALUES (new.id, new.name, new.normalized_name, coalesce(new.content,''));
END;
