from __future__ import annotations

import logging
from typing import Any

logger = logging.getLogger(__name__)


class ErpFinancialMixin:
    def list_financial_transactions(
        self, project_id: str | None = None, limit: int = 50
    ) -> list[dict[str, Any]]:
        """Lấy danh sách các giao dịch dòng tiền thu/chi thực tế từ sổ cái hoặc hóa đơn điện tử GDT."""
        sql_manual = """
            SELECT f.*, p.project_name as project_name
            FROM erp_financial_transactions f
            LEFT JOIN projects p ON f.project_id = p.id
            WHERE 1=1
        """
        params_manual: list[Any] = []
        if project_id:
            sql_manual += " AND f.project_id = %s"
            params_manual.append(project_id)
        sql_manual += (
            f" ORDER BY f.transaction_date DESC, f.created_at DESC LIMIT {limit};"
        )

        with self.get_connection() as conn:
            with conn.cursor() as cur:
                cur.execute(sql_manual, params_manual)
                rows = cur.fetchall()
                if rows:
                    return rows

                # Nguồn sự thật 100%: Trích xuất trực tiếp từ 1.384 hóa đơn Cổng Thuế GDT
                sql_invoices = """
                    SELECT 
                        i.id::text as id,
                        '0202111150' as company_id,
                        i.matched_project_id as project_id,
                        COALESCE(i.invoice_number, 'HD-' || SUBSTRING(i.id::text, 1, 8)) as transaction_code,
                        CASE 
                            WHEN i.direction = 'input' THEN 'CHI_PHI_MUA_VAO' 
                            WHEN i.direction = 'output' THEN 'DOANH_THU_XUAT_RA' 
                            ELSE 'HOA_DON' 
                        END as transaction_type,
                        CASE WHEN i.direction = 'input' THEN 'out' ELSE 'in' END as direction,
                        i.total_amount_vnd as amount,
                        COALESCE(i.issue_date, i.created_at::date) as transaction_date,
                        CASE WHEN i.direction = 'input' THEN i.seller_name ELSE i.buyer_name END as beneficiary_or_payer,
                        i.invoice_number,
                        'Chuyển khoản' as payment_method,
                        NULL as dossier_reference_id,
                        COALESCE(i.reconciliation_status, i.status, 'da_ghi_so') as accounting_status,
                        0.0 as ai_risk_score,
                        COALESCE(i.notes, i.seller_name) as notes,
                        NULL as created_by_employee_id,
                        COALESCE(p.project_name, 'HĐ-2026 Công trình chung') as project_name
                    FROM erp_invoices i
                    LEFT JOIN projects p ON i.matched_project_id = p.id
                    WHERE i.total_amount_vnd > 0
                """
                params_inv: list[Any] = []
                if project_id:
                    sql_invoices += " AND i.matched_project_id = %s"
                    params_inv.append(project_id)
                sql_invoices += f" ORDER BY transaction_date DESC NULLS LAST, i.created_at DESC LIMIT {limit};"

                cur.execute(sql_invoices, params_inv)
                return cur.fetchall()

    def list_system_activities(self, limit: int = 10) -> list[dict[str, Any]]:
        """Lấy danh sách các hoạt động vận hành và kiểm toán thực tế từ AI, Cổng Thuế GDT và Tự Trị DHS."""
        sql = f"""
            SELECT * FROM (
                SELECT 
                    'ai_agent' as activity_source,
                    COALESCE(agent_code, 'system') as actor_code,
                    CASE 
                        WHEN agent_code = 'minh' THEN 'Minh (Pháp lý)'
                        WHEN agent_code = 'thao' THEN 'Thảo (Dự toán)'
                        WHEN agent_code = 'tung' THEN 'Tùng (Kiểm toán)'
                        WHEN agent_code = 'quynh' THEN 'Quỳnh (Kế toán)'
                        WHEN agent_code = 'nam' THEN 'Nam (Tài chính)'
                        WHEN agent_code = 'hung' THEN 'Hùng (Hiện trường)'
                        WHEN agent_code = 'lan' THEN 'Lan (Khách hàng)'
                        WHEN agent_code = 'phuc' THEN 'Phúc (Tiến độ)'
                        WHEN agent_code = 'thuy' THEN 'Thủy (Trợ lý)'
                        ELSE 'Hệ thống AI'
                    END as actor_name,
                    'Tra cứu & xử lý tri thức: ' || SUBSTRING(COALESCE(query_preview, 'Hoàn thành tác vụ RAG'), 1, 80) as description,
                    created_at
                FROM erp_ai_agent_usage_logs
                UNION ALL
                SELECT 
                    'invoice_audit' as activity_source,
                    'bot_tax' as actor_code,
                    'Cổng Thuế GDT' as actor_name,
                    'Đồng bộ hóa đơn điện tử GDT (' || COALESCE(action_type, 'SYNC') || ')' as description,
                    created_at
                FROM erp_invoice_audit_logs
                UNION ALL
                SELECT 
                    'autonomous_healing' as activity_source,
                    COALESCE(agent_code, 'dhs') as actor_code,
                    'Hệ Thống Tự Trị DHS' as actor_name,
                    'Chuẩn hóa dữ liệu: ' || SUBSTRING(COALESCE(reason, 'Audit bản ghi'), 1, 80) as description,
                    created_at
                FROM erp_autonomous_healing_logs
            ) combined_activities
            ORDER BY created_at DESC
            LIMIT {limit};
        """
        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute(sql)
            return cur.fetchall()

    def get_kpi_summary(self) -> dict[str, Any]:
        """Tổng hợp chỉ số KPI vận hành thực tế cho Bảng Điều Hành."""
        with self.get_connection() as conn:
            with conn.cursor() as cur:
                # 1. Projects
                cur.execute(
                    "SELECT count(*) as total, count(*) FILTER (WHERE status = 'in_progress') as active FROM projects;"
                )
                proj_row = cur.fetchone()
                total_projects = proj_row["total"] if proj_row else 0
                active_projects = proj_row["active"] if proj_row else 0

                cur.execute(
                    "SELECT project_name FROM projects WHERE status = 'in_progress' ORDER BY created_at DESC LIMIT 3;"
                )
                top_projs = [r["project_name"] for r in cur.fetchall()]

                # 2. Equipment
                cur.execute(
                    "SELECT count(*) as total, count(*) FILTER (WHERE status = 'active' OR status = 'available') as active FROM erp_equipment;"
                )
                equip_row = cur.fetchone()
                total_equipment = equip_row["total"] if equip_row else 0
                active_equipment = equip_row["active"] if equip_row else 0

                # 3. Invoices & Revenue
                cur.execute(
                    "SELECT count(*) as total, COALESCE(sum(total_amount_vnd), 0) as sum_amount FROM erp_invoices;"
                )
                inv_row = cur.fetchone()
                total_invoices = inv_row["total"] if inv_row else 0
                total_invoices_amount = float(inv_row["sum_amount"]) if inv_row else 0.0

                return {
                    "total_projects": total_projects,
                    "active_projects": active_projects,
                    "active_project_names": top_projs,
                    "total_equipment": total_equipment,
                    "active_equipment": active_equipment,
                    "total_invoices": total_invoices,
                    "total_invoices_amount_vnd": total_invoices_amount,
                }

    def create_financial_transaction(self, payload: dict[str, Any]) -> dict[str, Any]:
        """Tạo giao dịch thu/chi tài chính mới."""
        sql = """
            INSERT INTO erp_financial_transactions (
                company_id, project_id, transaction_code, transaction_type,
                direction, amount, transaction_date, beneficiary_or_payer,
                invoice_number, payment_method, dossier_reference_id,
                accounting_status, ai_risk_score, notes, created_by_employee_id
            ) VALUES (
                %(company_id)s, %(project_id)s, %(transaction_code)s, %(transaction_type)s,
                %(direction)s, %(amount)s, %(transaction_date)s, %(beneficiary_or_payer)s,
                %(invoice_number)s, %(payment_method)s, %(dossier_reference_id)s,
                %(accounting_status)s, %(ai_risk_score)s, %(notes)s, %(created_by_employee_id)s
            ) RETURNING *;
        """
        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute(sql, payload)
            conn.commit()
            return cur.fetchone()

    def list_bank_transactions(
        self,
        bank_name: str | None = None,
        account_number: str | None = None,
        project_id: str | None = None,
        category: str | None = None,
        limit: int = 100,
    ) -> list[dict[str, Any]]:
        """Tra cứu danh sách giao dịch sao kê từ 4 tài khoản ngân hàng thực tế của Định Sơn."""
        sql = """
            SELECT b.*, p.project_name, i.invoice_number as matched_invoice_number
            FROM erp_bank_transactions b
            LEFT JOIN projects p ON b.matched_project_id = p.id
            LEFT JOIN erp_invoices i ON b.matched_invoice_id = i.id
            WHERE 1=1
        """
        params: list[Any] = []
        if bank_name:
            sql += " AND b.bank_name ILIKE %s"
            params.append(f"%{bank_name}%")
        if account_number:
            sql += " AND b.account_number = %s"
            params.append(account_number)
        if project_id:
            sql += " AND b.matched_project_id = %s"
            params.append(project_id)
        if category:
            sql += " AND b.transaction_category = %s"
            params.append(category)

        sql += f" ORDER BY b.transaction_date DESC, b.created_at DESC LIMIT {limit};"

        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute(sql, params)
            return cur.fetchall()

    def get_bank_reconciliation_summary(self) -> dict[str, Any]:
        """Báo cáo tổng hợp số dư và đối soát dòng tiền sao kê ngân hàng toàn công ty."""
        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute("""
                SELECT bank_name, account_number,
                       count(*) as txn_count,
                       sum(case when direction = 'inflow' then amount else 0 end) as total_in,
                       sum(case when direction = 'outflow' then amount else 0 end) as total_out,
                       min(transaction_date) as start_date,
                       max(transaction_date) as end_date
                FROM erp_bank_transactions
                GROUP BY bank_name, account_number
                ORDER BY total_in DESC;
            """)
            banks = cur.fetchall()

            cur.execute("""
                SELECT transaction_category,
                       count(*) as txn_count,
                       sum(case when direction = 'inflow' then amount else 0 end) as total_in,
                       sum(case when direction = 'outflow' then amount else 0 end) as total_out
                FROM erp_bank_transactions
                GROUP BY transaction_category
                ORDER BY sum(amount) DESC;
            """)
            categories = cur.fetchall()

            return {
                "banks": banks,
                "categories": categories,
            }