"""Migration: FTS5 virtual table on Requirements(code, recommendation).

Creates requirements_fts with content= shadow table and sync triggers.
Safe to run multiple times (idempotent).
"""
from __future__ import annotations

import sqlite3
from pathlib import Path

DB_PATH = Path(__file__).resolve().parent.parent / "fmcg.db"


def run() -> None:
    con = sqlite3.connect(DB_PATH)
    cur = con.cursor()

    cur.executescript("""
        -- Drop old artifacts if re-running
        DROP TABLE IF EXISTS requirements_fts;
        DROP TRIGGER IF EXISTS requirements_ai;
        DROP TRIGGER IF EXISTS requirements_au;
        DROP TRIGGER IF EXISTS requirements_ad;

        -- FTS5 virtual table (content= keeps data in the real table, not duplicated)
        CREATE VIRTUAL TABLE requirements_fts USING fts5(
            code,
            recommendation,
            content=requirements,
            content_rowid=id
        );

        -- Populate from existing rows
        INSERT INTO requirements_fts(rowid, code, recommendation)
            SELECT id, code, COALESCE(recommendation, '') FROM requirements;

        -- Sync trigger: INSERT
        CREATE TRIGGER requirements_ai AFTER INSERT ON requirements BEGIN
            INSERT INTO requirements_fts(rowid, code, recommendation)
                VALUES (new.id, new.code, COALESCE(new.recommendation, ''));
        END;

        -- Sync trigger: UPDATE
        CREATE TRIGGER requirements_au AFTER UPDATE ON requirements BEGIN
            INSERT INTO requirements_fts(requirements_fts, rowid, code, recommendation)
                VALUES ('delete', old.id, old.code, COALESCE(old.recommendation, ''));
            INSERT INTO requirements_fts(rowid, code, recommendation)
                VALUES (new.id, new.code, COALESCE(new.recommendation, ''));
        END;

        -- Sync trigger: DELETE
        CREATE TRIGGER requirements_ad AFTER DELETE ON requirements BEGIN
            INSERT INTO requirements_fts(requirements_fts, rowid, code, recommendation)
                VALUES ('delete', old.id, old.code, COALESCE(old.recommendation, ''));
        END;
    """)

    con.commit()
    con.close()
    print("FTS5 requirements_fts created with sync triggers.")


if __name__ == "__main__":
    run()