"""Cross-entity document tracking (Beleg tracking).

Tracks documents (sales orders, invoices, purchase orders, etc.) across
multiple legal entities on different ERP systems:
- SAP Business One (B1)
- SAP ECC
- Infor

Links related documents by recurring identifiers (order numbers, PO numbers,
customer references) across different systems to show the full process chain.
"""

from __future__ import annotations

import logging
from typing import Any

from database.connection_manager import ConnectionManager

logger = logging.getLogger(__name__)


class DocumentTracker:
    """Track documents across multiple ERPs and legal entities."""

    def __init__(self, cm: ConnectionManager) -> None:
        self._cm = cm

    async def ensure_tracking_tables(self) -> None:
        """Create tracking tables if they don't exist."""
        await self._cm.execute_write("""
            CREATE TABLE IF NOT EXISTS v3q_data.document_chain (
                id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                chain_reference TEXT NOT NULL,
                description     TEXT,
                created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
                updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
            )
        """)

        await self._cm.execute_write("""
            CREATE TABLE IF NOT EXISTS v3q_data.document_links (
                id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                chain_id        UUID NOT NULL REFERENCES v3q_data.document_chain(id) ON DELETE CASCADE,
                source_system   TEXT NOT NULL,
                entity_name     TEXT NOT NULL,
                doc_type        TEXT NOT NULL,
                doc_number      TEXT NOT NULL,
                doc_date        DATE,
                reference_number TEXT,
                customer_vendor TEXT,
                total_amount    NUMERIC,
                currency        TEXT,
                status          TEXT,
                local_table     TEXT,
                source_row_id   TEXT,
                metadata        JSONB,
                created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
                UNIQUE(source_system, entity_name, doc_type, doc_number)
            )
        """)

        await self._cm.execute_write("""
            CREATE INDEX IF NOT EXISTS idx_doc_links_chain
            ON v3q_data.document_links(chain_id)
        """)
        await self._cm.execute_write("""
            CREATE INDEX IF NOT EXISTS idx_doc_links_reference
            ON v3q_data.document_links(reference_number)
        """)
        await self._cm.execute_write("""
            CREATE INDEX IF NOT EXISTS idx_doc_links_doc_number
            ON v3q_data.document_links(doc_number)
        """)

    async def get_chain_id_for_doc(self, doc_number: str) -> str | None:
        """Find the chain_id for a document number, if it's linked in document_links."""
        row = await self._cm.fetch_one(
            "SELECT chain_id FROM v3q_data.document_links WHERE doc_number = :doc_number LIMIT 1",
            {"doc_number": doc_number},
        )
        return str(row["chain_id"]) if row else None

    async def search_document(self, search_term: str) -> list[dict]:
        """Search for a document across all systems by number or reference.

        Searches doc_number and reference_number fields.
        """
        return await self._cm.execute("""
            SELECT
                dl.*,
                dc.chain_reference,
                dc.description AS chain_description
            FROM v3q_data.document_links dl
            LEFT JOIN v3q_data.document_chain dc ON dc.id = dl.chain_id
            WHERE dl.doc_number ILIKE :term
               OR dl.reference_number ILIKE :term
               OR dc.chain_reference ILIKE :term
            ORDER BY dl.doc_date DESC NULLS LAST
        """, {"term": f"%{search_term}%"})

    async def get_chain(self, chain_id: str) -> dict | None:
        """Get a full document chain with all linked documents."""
        chain = await self._cm.fetch_one(
            "SELECT * FROM v3q_data.document_chain WHERE id = :id",
            {"id": chain_id},
        )
        if not chain:
            return None

        links = await self._cm.execute("""
            SELECT * FROM v3q_data.document_links
            WHERE chain_id = :cid
            ORDER BY doc_date ASC NULLS LAST
        """, {"cid": chain_id})

        return {**chain, "documents": links}

    async def link_documents(
        self,
        chain_reference: str,
        documents: list[dict],
        description: str | None = None,
    ) -> dict:
        """Create or update a document chain linking related documents.

        documents: [{
            "source_system": "sap_b1",
            "entity_name": "US Entity",
            "doc_type": "sales_order",
            "doc_number": "SO-12345",
            "doc_date": "2024-01-15",
            "reference_number": "PO-67890",
            ...
        }]
        """
        # Find or create chain
        existing = await self._cm.fetch_one(
            "SELECT * FROM v3q_data.document_chain WHERE chain_reference = :ref",
            {"ref": chain_reference},
        )

        if existing:
            chain_id = str(existing["id"])
            await self._cm.execute_write(
                "UPDATE v3q_data.document_chain SET updated_at = now() WHERE id = :id",
                {"id": chain_id},
            )
        else:
            chain = await self._cm.fetch_one(
                "INSERT INTO v3q_data.document_chain (chain_reference, description) VALUES (:ref, :desc) RETURNING *",
                {"ref": chain_reference, "desc": description},
            )
            chain_id = str(chain["id"])

        # Insert/update document links
        for doc in documents:
            await self._cm.execute_write("""
                INSERT INTO v3q_data.document_links
                    (chain_id, source_system, entity_name, doc_type, doc_number,
                     doc_date, reference_number, customer_vendor, total_amount,
                     currency, status, local_table, source_row_id, metadata)
                VALUES (:chain_id, :source_system, :entity_name, :doc_type, :doc_number,
                        :doc_date, :reference_number, :customer_vendor, :total_amount,
                        :currency, :status, :local_table, :source_row_id, :metadata)
                ON CONFLICT (source_system, entity_name, doc_type, doc_number)
                DO UPDATE SET
                    chain_id = EXCLUDED.chain_id,
                    doc_date = EXCLUDED.doc_date,
                    reference_number = EXCLUDED.reference_number,
                    total_amount = EXCLUDED.total_amount,
                    status = EXCLUDED.status,
                    metadata = EXCLUDED.metadata
            """, {
                "chain_id": chain_id,
                "source_system": doc["source_system"],
                "entity_name": doc["entity_name"],
                "doc_type": doc["doc_type"],
                "doc_number": doc["doc_number"],
                "doc_date": doc.get("doc_date"),
                "reference_number": doc.get("reference_number"),
                "customer_vendor": doc.get("customer_vendor"),
                "total_amount": doc.get("total_amount"),
                "currency": doc.get("currency"),
                "status": doc.get("status"),
                "local_table": doc.get("local_table"),
                "source_row_id": doc.get("source_row_id"),
                "metadata": __import__("json").dumps(doc.get("metadata")) if doc.get("metadata") else None,
            })

        return {"chain_id": chain_id, "chain_reference": chain_reference, "document_count": len(documents)}

    async def auto_link_by_reference(self) -> int:
        """Automatically find and link documents that share reference numbers.

        Scans document_links for matching reference_number ↔ doc_number pairs
        across different systems and creates chains.
        """
        # Find documents where one's reference_number matches another's doc_number
        matches = await self._cm.execute("""
            SELECT
                a.id AS a_id, a.doc_number AS a_doc, a.source_system AS a_sys,
                b.id AS b_id, b.doc_number AS b_doc, b.source_system AS b_sys,
                a.reference_number
            FROM v3q_data.document_links a
            JOIN v3q_data.document_links b
                ON a.reference_number = b.doc_number
                AND a.source_system != b.source_system
            WHERE a.chain_id IS NULL OR b.chain_id IS NULL
               OR a.chain_id != b.chain_id
        """)

        linked = 0
        for match in matches:
            ref = match["reference_number"]
            chain = await self._cm.fetch_one(
                "SELECT id FROM v3q_data.document_chain WHERE chain_reference = :ref",
                {"ref": ref},
            )
            if not chain:
                chain = await self._cm.fetch_one(
                    "INSERT INTO v3q_data.document_chain (chain_reference) VALUES (:ref) RETURNING id",
                    {"ref": ref},
                )

            chain_id = str(chain["id"])
            await self._cm.execute_write(
                "UPDATE v3q_data.document_links SET chain_id = :cid WHERE id IN (:a, :b)",
                {"cid": chain_id, "a": match["a_id"], "b": match["b_id"]},
            )
            linked += 1

        return linked
