"""FDI durable schema — system of record for financial documents."""

from __future__ import annotations

from app.database import get_connection, table_columns


def init_fdi_tables() -> None:
    """Create FDI tables (idempotent). Called from SaaS init."""
    with get_connection() as conn:
        conn.executescript(
            """
            CREATE TABLE IF NOT EXISTS fdi_documents (
                id                  TEXT PRIMARY KEY,
                org_id              TEXT NOT NULL,
                agent_id            TEXT NOT NULL,
                kb_doc_id           TEXT,
                filename            TEXT NOT NULL,
                storage_uri         TEXT,
                sha256              TEXT,
                mime_type           TEXT,
                doc_type            TEXT NOT NULL DEFAULT 'unknown',
                doc_type_confidence REAL NOT NULL DEFAULT 0,
                status              TEXT NOT NULL DEFAULT 'pending',
                page_count          INTEGER NOT NULL DEFAULT 0,
                ocr_used            INTEGER NOT NULL DEFAULT 0,
                parse_confidence    REAL NOT NULL DEFAULT 0,
                currency_primary    TEXT,
                period_start        TEXT,
                period_end          TEXT,
                issuer_name         TEXT,
                holder_name         TEXT,
                source_type         TEXT,
                error               TEXT,
                created_at          TEXT NOT NULL,
                indexed_at          TEXT
            );

            CREATE TABLE IF NOT EXISTS fdi_assertions (
                id              TEXT PRIMARY KEY,
                org_id          TEXT NOT NULL,
                document_id     TEXT NOT NULL,
                kind            TEXT NOT NULL,
                label           TEXT,
                value_numeric   REAL,
                value_text      TEXT,
                unit            TEXT,
                currency        TEXT,
                source          TEXT NOT NULL DEFAULT 'extracted',
                confidence      REAL NOT NULL DEFAULT 0,
                evidence_json   TEXT,
                created_at      TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS fdi_line_items (
                id                      TEXT PRIMARY KEY,
                org_id                  TEXT NOT NULL,
                document_id             TEXT NOT NULL,
                page_no                 INTEGER,
                row_idx                 INTEGER,
                event_date              TEXT,
                posting_date            TEXT,
                description_raw         TEXT,
                description_norm        TEXT,
                amount                  REAL,
                debit                   REAL,
                credit                  REAL,
                balance_after           REAL,
                direction               TEXT,
                currency                TEXT,
                counterparty_entity_id  TEXT,
                account_entity_id       TEXT,
                category                TEXT,
                event_class             TEXT NOT NULL DEFAULT 'other',
                extras_json             TEXT,
                confidence              REAL NOT NULL DEFAULT 0,
                created_at              TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS fdi_identifiers (
                id              TEXT PRIMARY KEY,
                org_id          TEXT NOT NULL,
                document_id     TEXT,
                line_item_id    TEXT,
                id_type         TEXT NOT NULL,
                value_raw       TEXT NOT NULL,
                value_norm      TEXT NOT NULL,
                confidence      REAL NOT NULL DEFAULT 0,
                created_at      TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS fdi_entities (
                id              TEXT PRIMARY KEY,
                org_id          TEXT NOT NULL,
                entity_type     TEXT NOT NULL,
                canonical_name  TEXT NOT NULL,
                attributes_json TEXT,
                created_at      TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS fdi_entity_aliases (
                id              TEXT PRIMARY KEY,
                org_id          TEXT NOT NULL,
                entity_id       TEXT NOT NULL,
                alias           TEXT NOT NULL,
                source          TEXT,
                weight          REAL NOT NULL DEFAULT 1,
                created_at      TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS fdi_entity_links (
                id              TEXT PRIMARY KEY,
                org_id          TEXT NOT NULL,
                left_id         TEXT NOT NULL,
                right_id        TEXT NOT NULL,
                rel_type        TEXT NOT NULL,
                document_id     TEXT,
                confidence      REAL NOT NULL DEFAULT 0,
                created_at      TEXT NOT NULL
            );

            CREATE INDEX IF NOT EXISTS idx_fdi_docs_org_agent
                ON fdi_documents(org_id, agent_id);
            CREATE INDEX IF NOT EXISTS idx_fdi_docs_kb
                ON fdi_documents(kb_doc_id);
            CREATE INDEX IF NOT EXISTS idx_fdi_docs_type
                ON fdi_documents(org_id, doc_type);
            CREATE INDEX IF NOT EXISTS idx_fdi_lines_doc
                ON fdi_line_items(document_id);
            CREATE INDEX IF NOT EXISTS idx_fdi_lines_org_date
                ON fdi_line_items(org_id, event_date);
            CREATE INDEX IF NOT EXISTS idx_fdi_lines_counterparty
                ON fdi_line_items(counterparty_entity_id);
            CREATE INDEX IF NOT EXISTS idx_fdi_ids_lookup
                ON fdi_identifiers(org_id, id_type, value_norm);
            CREATE INDEX IF NOT EXISTS idx_fdi_ids_doc
                ON fdi_identifiers(document_id);
            CREATE INDEX IF NOT EXISTS idx_fdi_assertions_doc
                ON fdi_assertions(document_id);
            CREATE INDEX IF NOT EXISTS idx_fdi_entities_org
                ON fdi_entities(org_id, entity_type);
            CREATE INDEX IF NOT EXISTS idx_fdi_aliases_org
                ON fdi_entity_aliases(org_id, alias);
            """
        )
        # Future additive columns go here via table_columns pattern.
        _ = table_columns(conn, "fdi_documents")