from __future__ import annotations

import logging
from pathlib import Path
from typing import Any

logger = logging.getLogger("dscons.postgres.takeoff.crud")


class TakeoffCrudMixin:
    """Takeoff session CRUD database operations."""

    def create_drawing_takeoff(self, payload: dict[str, Any]) -> dict[str, Any]:
        """Create or register a new drawing takeoff record."""
        sql = """
            INSERT INTO erp_drawing_takeoffs (
                project_id, document_id, drawing_code, drawing_title,
                file_path, file_type, file_size_bytes, page_count,
                scale_ratio, drawing_type, takeoff_status, preview_image_url,
                created_by
            )
            VALUES (
                %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s
            )
            RETURNING *;
        """
        params = (
            payload.get("project_id"),
            payload.get("document_id"),
            payload.get("drawing_code", "DWG-AUTO"),
            payload.get("drawing_title", "Bản vẽ thiết kế"),
            payload.get("file_path", ""),
            payload.get("file_type", "pdf"),
            payload.get("file_size_bytes", 0),
            payload.get("page_count", 1),
            payload.get("scale_ratio", "1:100"),
            payload.get("drawing_type", "civil_building"),
            payload.get("takeoff_status", "pending"),
            payload.get("preview_image_url"),
            payload.get("created_by", "system"),
        )
        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute(sql, params)
            row = cur.fetchone()
            conn.commit()
            return dict(row)

    def get_drawing_takeoff(self, takeoff_id: str) -> dict[str, Any] | None:
        """Fetch drawing takeoff by ID."""
        sql = "SELECT * FROM erp_drawing_takeoffs WHERE id = %s;"
        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute(sql, (takeoff_id,))
            row = cur.fetchone()
            return dict(row) if row else None

    def update_drawing_takeoff(
        self, takeoff_id: str, updates: dict[str, Any]
    ) -> dict[str, Any] | None:
        """Update drawing takeoff header information."""
        if not updates:
            return self.get_drawing_takeoff(takeoff_id)

        set_clauses = []
        params = []
        for k, v in updates.items():
            set_clauses.append(f"{k} = %s")
            params.append(v)

        params.append(takeoff_id)
        sql = f"""
            UPDATE erp_drawing_takeoffs
            SET {", ".join(set_clauses)}, updated_at = NOW()
            WHERE id = %s
            RETURNING *;
        """
        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute(sql, tuple(params))
            row = cur.fetchone()
            conn.commit()
            return dict(row) if row else None

    def list_drawing_takeoffs(
        self,
        project_id: str | None = None,
        takeoff_status: str | None = None,
        limit: int = 50,
    ) -> list[dict[str, Any]]:
        """List drawing takeoffs with optional filtering and auto-reconciliation of stale tasks."""
        # Auto-reconcile stale processing takeoffs (> 5 minutes) to prevent infinite UI hangs
        try:
            with self.get_connection() as conn:
                with conn.cursor() as cur:
                    cur.execute("""
                        UPDATE erp_drawing_takeoffs
                        SET takeoff_status = 'failed',
                            ai_report_message = 'Tiến trình bóc tách bị gián đoạn do khởi động lại hệ thống hoặc quá thời gian xử lý. Sếp có thể bấm "Bóc Tách Lại AI Vision" để chạy lại hoặc "Xóa Bản Vẽ".'
                        WHERE takeoff_status IN ('processing', 'pending')
                          AND created_at < NOW() - INTERVAL '5 minutes';
                    """)
                    conn.commit()
        except Exception as e:
            logger.warning(
                "[TAKEOFF_REPO] Stale takeoffs reconciliation warning: %s", e
            )

        where_clauses = []
        params = []

        if project_id:
            where_clauses.append("project_id = %s")
            params.append(project_id)
        if takeoff_status:
            where_clauses.append("takeoff_status = %s")
            params.append(takeoff_status)

        where_sql = f"WHERE {' AND '.join(where_clauses)}" if where_clauses else ""
        sql = f"""
            SELECT * FROM erp_drawing_takeoffs
            {where_sql}
            ORDER BY created_at DESC
            LIMIT %s;
        """
        params.append(limit)

        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute(sql, tuple(params))
            rows = cur.fetchall()
            return [dict(r) for r in rows]

    def delete_drawing_takeoff(self, takeoff_id: str) -> bool:
        """Delete drawing takeoff record and related items & physical files."""
        with self.get_connection() as conn:
            with conn.cursor() as cur:
                cur.execute(
                    "DELETE FROM erp_drawing_takeoff_items WHERE takeoff_id = %s;",
                    (takeoff_id,),
                )
                cur.execute(
                    "DELETE FROM erp_drawing_takeoffs WHERE id = %s RETURNING id, file_path;",
                    (takeoff_id,),
                )
                deleted = cur.fetchone()
                conn.commit()
                if deleted and deleted.get("file_path"):
                    try:
                        p = Path(deleted["file_path"])
                        if p.exists():
                            p.unlink()
                    except Exception as e:
                        logger.warning(
                            "[TAKEOFF] Không thể xóa file vật lý %s: %s",
                            deleted["file_path"],
                            e,
                        )
                return bool(deleted)

    def delete_all_drawing_takeoffs(self, project_id: str | None = None) -> int:
        """Bulk delete old takeoff records (Super Admin only)."""
        with self.get_connection() as conn:
            with conn.cursor() as cur:
                if project_id:
                    cur.execute(
                        """
                        DELETE FROM erp_drawing_takeoff_items 
                        WHERE takeoff_id IN (SELECT id FROM erp_drawing_takeoffs WHERE project_id = %s);
                    """,
                        (project_id,),
                    )
                    cur.execute(
                        "DELETE FROM erp_drawing_takeoffs WHERE project_id = %s RETURNING id;",
                        (project_id,),
                    )
                else:
                    cur.execute("DELETE FROM erp_drawing_takeoff_items;")
                    cur.execute("DELETE FROM erp_drawing_takeoffs RETURNING id;")
                rows = cur.fetchall()
                conn.commit()
                return len(rows)