"""CRUD operations for notification subscriptions."""

from __future__ import annotations

import logging
from datetime import datetime, timezone

from database.connection_manager import ConnectionManager

logger = logging.getLogger(__name__)


class SubscriptionManager:
    """Manage email notification subscriptions for document tracking."""

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

    async def create(
        self,
        email: str,
        doc_number: str,
        chain_id: str | None = None,
        source_system: str | None = None,
        doc_type: str | None = None,
        notify_on_stages: list[str] | None = None,
    ) -> dict:
        row = await self._cm.fetch_one(
            """INSERT INTO v3q_config.subscriptions
               (email, doc_number, chain_id, source_system, doc_type, notify_on_stages)
               VALUES (:email, :doc_number, :chain_id, :source_system, :doc_type, :notify_on_stages)
               RETURNING *""",
            {
                "email": email,
                "doc_number": doc_number,
                "chain_id": chain_id,
                "source_system": source_system,
                "doc_type": doc_type,
                "notify_on_stages": notify_on_stages,
            },
        )
        logger.info(f"Created subscription for {email} on doc {doc_number}")
        return dict(row)

    async def list_subscriptions(
        self, email: str | None = None, doc_number: str | None = None
    ) -> list[dict]:
        query = "SELECT * FROM v3q_config.subscriptions WHERE is_active = true"
        params: dict = {}
        if email:
            query += " AND email = :email"
            params["email"] = email
        if doc_number:
            query += " AND doc_number = :doc_number"
            params["doc_number"] = doc_number
        query += " ORDER BY created_at DESC"
        rows = await self._cm.execute(query, params)
        return [dict(r) for r in rows]

    async def get(self, subscription_id: str) -> dict | None:
        row = await self._cm.fetch_one(
            "SELECT * FROM v3q_config.subscriptions WHERE id = :id",
            {"id": subscription_id},
        )
        return dict(row) if row else None

    async def update(self, subscription_id: str, **kwargs) -> dict | None:
        allowed = {"email", "doc_number", "chain_id", "source_system", "doc_type", "notify_on_stages", "is_active"}
        updates = {k: v for k, v in kwargs.items() if k in allowed}
        if not updates:
            return await self.get(subscription_id)

        set_clause = ", ".join(f"{k} = :{k}" for k in updates)
        updates["id"] = subscription_id
        updates["updated_at"] = datetime.now(timezone.utc).isoformat()
        row = await self._cm.fetch_one(
            f"UPDATE v3q_config.subscriptions SET {set_clause}, updated_at = :updated_at WHERE id = :id RETURNING *",
            updates,
        )
        return dict(row) if row else None

    async def delete(self, subscription_id: str) -> bool:
        await self._cm.execute_write(
            "DELETE FROM v3q_config.subscriptions WHERE id = :id",
            {"id": subscription_id},
        )
        return True

    async def find_matching(self, doc_number: str, source_system: str | None = None) -> list[dict]:
        """Find active subscriptions matching a document number."""
        query = "SELECT * FROM v3q_config.subscriptions WHERE is_active = true AND doc_number = :doc_number"
        params: dict = {"doc_number": doc_number}
        if source_system:
            query += " AND (source_system IS NULL OR source_system = :source_system)"
            params["source_system"] = source_system
        rows = await self._cm.execute(query, params)
        return [dict(r) for r in rows]
