"""Match sync_events against subscriptions and queue notifications."""

from __future__ import annotations

import logging
from datetime import datetime, timezone

from database.connection_manager import ConnectionManager
from notifications.subscription_manager import SubscriptionManager
from tracking.document_tracker import DocumentTracker

logger = logging.getLogger(__name__)


class EventProcessor:
    """Process unprocessed sync_events → create notification_log entries."""

    def __init__(self, cm: ConnectionManager, sub_mgr: SubscriptionManager) -> None:
        self._cm = cm
        self._sub = sub_mgr
        self._tracker = DocumentTracker(cm)

    async def process_pending_events(self) -> int:
        """Match unprocessed sync_events to subscriptions, create notification_log entries.

        Returns count of notifications queued.
        """
        # Fetch unprocessed events
        events = await self._cm.execute(
            """SELECT * FROM v3q_data.sync_events
               WHERE processed = false
               ORDER BY detected_at""",
        )

        if not events:
            return 0

        queued = 0
        # Group events by doc_number for batching
        doc_events: dict[str, list[dict]] = {}
        for event in events:
            doc_num = event.get("doc_number")
            if doc_num:
                doc_events.setdefault(doc_num, []).append(dict(event))

        # Also try to match events without doc_number by looking up row_key
        # in the document tracking system (fallback for legacy/unmapped tables)
        events_without_doc = [dict(e) for e in events if not e.get("doc_number")]
        for event in events_without_doc:
            doc_num = await self._resolve_doc_number(event)
            if doc_num:
                doc_events.setdefault(doc_num, []).append(event)
                # Update the event with the resolved doc_number
                await self._cm.execute_write(
                    "UPDATE v3q_data.sync_events SET doc_number = :doc_number WHERE id = :id",
                    {"doc_number": doc_num, "id": str(event["id"])},
                )

        # Enrich events that have doc_number but no chain_id
        await self._populate_chain_ids()

        # Match each doc_number's events against subscriptions
        for doc_number, evts in doc_events.items():
            subs = await self._sub.find_matching(doc_number)
            for sub in subs:
                # Check stage filter
                notify_stages = sub.get("notify_on_stages")
                relevant_events = evts
                if notify_stages:
                    relevant_events = [
                        e for e in evts
                        if e.get("new_value") in notify_stages or not notify_stages
                    ]

                if not relevant_events:
                    continue

                # Build notification subject from the changes
                changes_summary = self._summarize_changes(relevant_events)
                subject = f"[V3Q.AI] Status update for {doc_number}: {changes_summary}"

                await self._cm.execute_write(
                    """INSERT INTO v3q_config.notification_log
                       (subscription_id, event_id, email, doc_number, subject, status)
                       VALUES (:sub_id, :event_id, :email, :doc_number, :subject, 'pending')""",
                    {
                        "sub_id": str(sub["id"]),
                        "event_id": str(relevant_events[0]["id"]),
                        "email": sub["email"],
                        "doc_number": doc_number,
                        "subject": subject,
                    },
                )
                queued += 1

        # Mark all events as processed
        event_ids = [str(e["id"]) for e in events]
        if event_ids:
            placeholders = ", ".join(f"'{eid}'" for eid in event_ids)
            await self._cm.execute_write(
                f"UPDATE v3q_data.sync_events SET processed = true WHERE id IN ({placeholders})"
            )

        logger.info(f"Event processing: {len(events)} events → {queued} notifications queued")
        return queued

    async def _resolve_doc_number(self, event: dict) -> str | None:
        """Try to resolve a doc_number from event row_key using document tracking."""
        try:
            import json
            row_key = event.get("row_key", {})
            if isinstance(row_key, str):
                row_key = json.loads(row_key)
            # Look for common SAP document number fields
            for field in ["VBELN", "EBELN", "BELNR", "doc_number", "order_number"]:
                if field in row_key and row_key[field]:
                    return str(row_key[field]).strip()
        except Exception:
            pass
        return None

    async def _populate_chain_ids(self) -> None:
        """Find chain_ids for events that have doc_number but no chain_id."""
        orphaned = await self._cm.execute(
            "SELECT DISTINCT doc_number FROM v3q_data.sync_events WHERE doc_number IS NOT NULL AND chain_id IS NULL",
        )
        for row in orphaned:
            doc_num = row["doc_number"]
            try:
                chain_id = await self._tracker.get_chain_id_for_doc(doc_num)
                if chain_id:
                    await self._cm.execute_write(
                        "UPDATE v3q_data.sync_events SET chain_id = :chain_id WHERE doc_number = :doc_number AND chain_id IS NULL",
                        {"chain_id": chain_id, "doc_number": doc_num},
                    )
            except Exception:
                pass  # document_links table may not exist yet

    def _summarize_changes(self, events: list[dict]) -> str:
        """Create a short summary of what changed."""
        if len(events) == 1:
            e = events[0]
            col = e.get("column_name", "field")
            old = e.get("old_value", "?")
            new = e.get("new_value", "?")
            if e.get("change_type") == "added":
                return f"{col} set to {new}"
            elif e.get("change_type") == "removed":
                return f"{col} removed (was {old})"
            return f"{col} changed from {old} to {new}"
        return f"{len(events)} fields changed"
