from __future__ import annotations

import logging
import openpyxl
import os
import shutil
from datetime import datetime
from decimal import Decimal
from typing import Any, Dict, List, Optional

from app.modules.projects.domain.ports.project_repository_port import ProjectExcelSyncPort

logger = logging.getLogger("ExcelSyncService")


class ExcelSyncService:
    """Dịch vụ đồng bộ an toàn hai chiều giữa Excel và PostgreSQL DB."""

    EXCEL_PATH = "C:/Projects/DSCons/CongTrinh/Bảng theo dõi công trình.xlsx"
    BACKUP_DIR = "C:/Projects/DSCons/CongTrinh/Backups"

    def __init__(self, repo: ProjectExcelSyncPort | None = None):
        if repo is None:
            from app.core.postgres.erp_client import ErpDatabaseClient
            repo = ErpDatabaseClient()
        self.db = repo

    def backup_excel_file(self) -> str:
        """Tạo bản sao lưu an toàn cho file Excel trước khi đồng bộ."""
        os.makedirs(self.BACKUP_DIR, exist_ok=True)
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        backup_path = os.path.join(self.BACKUP_DIR, f"Bang_theo_doi_cong_trinh_backup_{timestamp}.xlsx")
        shutil.copy2(self.EXCEL_PATH, backup_path)
        logger.info(f"Đã tạo bản sao lưu Excel tại: {backup_path}")
        return backup_path

    def sync_db_to_excel(self) -> Dict[str, Any]:
        """Đồng bộ từ Database sang Excel:
        Lấy các giá trị đã thẩm tra từ DB và ghi vào Excel, giữ nguyên định dạng, không lệch dòng.
        """
        backup_file = self.backup_excel_file()
        wb = openpyxl.load_workbook(self.EXCEL_PATH)

        updated_count = 0
        with self.db.get_connection() as conn:
            with conn.cursor() as cur:
                cur.execute("""
                    SELECT project_code, project_name, contract_number, contract_value
                    FROM projects
                    ORDER BY project_code;
                """)
                db_projects = {r["project_code"]: r for r in cur.fetchall()}

        for sname in ["2026", "2025", "2024", "2023", "2022"]:
            if sname not in wb.sheetnames:
                continue
            ws = wb[sname]
            gt_col = 14 if sname == "2024" else 7
            hd_col = 5

            for r in range(5, ws.max_row + 1):
                stt = ws.cell(r, 1).value
                if not stt:
                    continue
                try:
                    stt_int = int(stt)
                    code = f"DA-{sname}-{stt_int:02d}"
                    if code in db_projects:
                        p = db_projects[code]
                        if p["contract_value"] is not None:
                            target_val = float(p["contract_value"])
                            curr_val = ws.cell(r, gt_col).value
                            if curr_val != target_val:
                                ws.cell(r, gt_col).value = target_val
                                updated_count += 1
                        if p["contract_number"] and not ws.cell(r, hd_col).value:
                            ws.cell(r, hd_col).value = p["contract_number"]
                except Exception:
                    continue

        wb.save(self.EXCEL_PATH)
        logger.info(f"Đã đồng bộ {updated_count} ô dữ liệu từ DB vào Excel!")

        return {
            "success": True,
            "updated_cells": updated_count,
            "backup_file": backup_file,
            "excel_path": self.EXCEL_PATH,
            "synced_at": datetime.now().isoformat()
        }

    def sync_excel_to_db(self, dry_run: bool = True) -> Dict[str, Any]:
        """Đọc file Excel và đồng bộ an toàn vào CSDL (với chế độ Dry-run bảo vệ)."""
        wb = openpyxl.load_workbook(self.EXCEL_PATH, data_only=True)
        changes = []

        with self.db.get_connection() as conn:
            with conn.cursor() as cur:
                cur.execute("SELECT id, project_code, contract_value FROM projects;")
                db_projects = {r["project_code"]: r for r in cur.fetchall()}

                for sname in ["2026", "2025", "2024", "2023", "2022"]:
                    if sname not in wb.sheetnames:
                        continue
                    ws = wb[sname]
                    gt_col = 14 if sname == "2024" else 7

                    for r in range(5, ws.max_row + 1):
                        stt = ws.cell(r, 1).value
                        if not stt:
                            continue
                        try:
                            stt_int = int(stt)
                            code = f"DA-{sname}-{stt_int:02d}"
                            if code in db_projects:
                                c_val = ws.cell(r, gt_col).value
                                if isinstance(c_val, (int, float)) and c_val > 0:
                                    db_item = db_projects[code]
                                    db_val = float(db_item["contract_value"] or 0)
                                    if abs(db_val - float(c_val)) > 1000:
                                        changes.append({
                                            "project_code": code,
                                            "old_value": db_val,
                                            "new_value": float(c_val),
                                            "sheet": sname,
                                            "row": r
                                        })
                                        if not dry_run:
                                            cur.execute("""
                                                UPDATE projects
                                                SET contract_value = %s,
                                                    budget_amount = %s,
                                                    updated_at = NOW()
                                                WHERE project_code = %s;
                                            """, (Decimal(str(c_val)), Decimal(str(c_val)), code))
                        except Exception:
                            continue

                if not dry_run:
                    conn.commit()

        return {
            "dry_run": dry_run,
            "detected_changes_count": len(changes),
            "changes": changes,
            "synced_at": datetime.now().isoformat()
        }