"""Infrastructure adapter for project event store operations.

Implements ProjectEventStorePort using PostgreSQL via ErpDatabaseClient.
SQL queries migrated from the former inline SQL in cost_sync_listener.py.
"""
from __future__ import annotations

import logging

from app.core.postgres.erp_client import ErpDatabaseClient
from app.modules.projects.domain.ports.project_repository_port import ProjectEventStorePort

logger = logging.getLogger("dscons.projects.event_store")


class PostgresProjectEventStore(ProjectEventStorePort):
    """PostgreSQL-backed implementation of ProjectEventStorePort."""

    def __init__(self, db: ErpDatabaseClient | None = None) -> None:
        self._db = db or ErpDatabaseClient()

    def is_event_processed(self, project_id: str, event_type: str, reference_id: str) -> bool:
        """Check idempotency table for previously processed events."""
        with self._db.get_connection() as conn, conn.cursor() as cur:
            cur.execute(
                """
                SELECT id FROM erp_project_processed_events
                WHERE project_id = %s AND event_type = %s AND reference_id = %s
                """,
                (project_id, event_type, reference_id),
            )
            return cur.fetchone() is not None

    def add_cost_to_wbs(self, wbs_id: str, project_id: str, amount: float) -> int:
        """Add actual cost to a WBS element. Returns number of rows affected."""
        with self._db.get_connection() as conn, conn.cursor() as cur:
            cur.execute(
                """
                UPDATE erp_project_wbs
                SET actual_cost_vnd = COALESCE(actual_cost_vnd, 0) + %s,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = %s AND project_id = %s
                """,
                (amount, wbs_id, project_id),
            )
            conn.commit()
            return cur.rowcount

    def record_processed_event(self, project_id: str, event_type: str, reference_id: str) -> None:
        """Record an event as processed for idempotency."""
        with self._db.get_connection() as conn, conn.cursor() as cur:
            cur.execute(
                """
                INSERT INTO erp_project_processed_events (project_id, event_type, reference_id)
                VALUES (%s, %s, %s)
                """,
                (project_id, event_type, reference_id),
            )
            conn.commit()