"""CRUD operations for status stage definitions."""

from __future__ import annotations

import logging

from database.connection_manager import ConnectionManager

logger = logging.getLogger(__name__)


class StatusStages:
    """Manage configurable status flows per document type."""

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

    async def list_stages(self, doc_type: str | None = None, source_system: str | None = None) -> list[dict]:
        query = "SELECT * FROM v3q_config.status_stage_definitions WHERE 1=1"
        params: dict = {}
        if doc_type:
            query += " AND doc_type = :doc_type"
            params["doc_type"] = doc_type
        if source_system:
            query += " AND source_system = :source_system"
            params["source_system"] = source_system
        query += " ORDER BY doc_type, source_system, stage_order"
        rows = await self._cm.execute(query, params)
        return [dict(r) for r in rows]

    async def create(self, doc_type: str, source_system: str, stage_order: int, stage_key: str, stage_label: str) -> dict:
        row = await self._cm.fetch_one(
            """INSERT INTO v3q_config.status_stage_definitions
               (doc_type, source_system, stage_order, stage_key, stage_label)
               VALUES (:doc_type, :source_system, :stage_order, :stage_key, :stage_label)
               ON CONFLICT (doc_type, source_system, stage_order)
               DO UPDATE SET stage_key = EXCLUDED.stage_key, stage_label = EXCLUDED.stage_label
               RETURNING *""",
            {
                "doc_type": doc_type,
                "source_system": source_system,
                "stage_order": stage_order,
                "stage_key": stage_key,
                "stage_label": stage_label,
            },
        )
        return dict(row)

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

    async def get_stage_for_value(self, doc_type: str, source_system: str, value: str) -> dict | None:
        row = await self._cm.fetch_one(
            """SELECT * FROM v3q_config.status_stage_definitions
               WHERE doc_type = :doc_type AND source_system = :source_system AND stage_key = :value""",
            {"doc_type": doc_type, "source_system": source_system, "value": value},
        )
        return dict(row) if row else None
