# Script rà soát toàn bộ sao kê ngân hàng và đối soát tài chính Công ty Định Sơn
import os
import sys
import glob
import re
from datetime import datetime
import openpyxl
import xlrd
import pdfplumber
import pypdf

sys.path.insert(0, os.path.abspath("."))
from app.core.postgres.erp_client import ErpDatabaseClient

BASE_DIR = r"C:\Projects\DSCons\Finance"
COMPANY_ID = "0753d827-90e1-4894-9c16-266ca572c5eb"

def categorize_and_match(desc, direction, amount, projects_map, invoices_map):
    desc_upper = desc.upper()
    cat = "OTHER"
    matched_proj_id = None
    matched_inv_id = None
    counterparty = ""

    # 1. Chuyển tiền nội bộ (Internal Transfer)
    if any(k in desc_upper for k in ["CHUYEN SANG TK", "NHAN TU 13456888888", "NHAN TU 889028888", "CHUYEN SANG TK TECHCOMBANK", "CHUYEN SANG TK VPBANK", "CHUYEN SANG TK ACB"]):
        cat = "INTERNAL_TRANSFER"
        counterparty = "Nội bộ Công ty Định Sơn"

    # 2. Vốn chủ sở hữu / Rút quỹ tiền mặt
    elif any(k in desc_upper for k in ["NGUYEN SI SON NOP TIEN", "RUT VE QUY TIEN MAT", "RUT TIEN TU TAI KHOAN", "RUT TIEN MAT"]):
        cat = "OWNER_DRAW_CAPITAL"
        counterparty = "Nguyễn Sĩ Sơn (Giám đốc)"

    # 3. Nộp thuế Nhà nước
    elif any(k in desc_upper for k in ["NTDT+KB", "KBNN KIEN THUY", "NOP THUE", "KHO BAC"]):
        if direction == "outflow":
            cat = "TAX_PAYMENT"
            counterparty = "Kho Bạc Nhà Nước Kiến Thụy"
        else:
            cat = "REVENUE_COLLECTION"
            counterparty = "Kho Bạc Nhà Nước (Giải ngân công trình)"

    # 4. Phí ngân hàng & Lãi / Vay
    elif any(k in desc_upper for k in ["THU PHI", "PHI BAO LANH", "TRA LAI SO DU", "THANH TOAN NO GOC", "THANH TOAN LAI", "THU KY QUY"]):
        cat = "BANK_FEE_LOAN"
        counterparty = "Ngân hàng"

    # 5. Tiền thu từ công trình (Inflow)
    elif direction == "inflow":
        cat = "REVENUE_COLLECTION"
        if "DA DO" in desc_upper or "THUY LOI" in desc_upper:
            counterparty = "Công ty TNHH MTV KTCT Thủy Lợi Đa Độ"
        elif "KIEN MINH" in desc_upper:
            counterparty = "UBND / Phòng Kinh Tế Xã Kiến Minh"
        elif "VIMC" in desc_upper:
            counterparty = "Công ty Cổ phần VIMC Logistics"
        elif "BAO LOC" in desc_upper:
            counterparty = "Công ty TNHH Đầu tư Xây dựng Bảo Lộc"
        elif "BAO LONG" in desc_upper:
            counterparty = "Công ty TNHH Phát triển TM & XD Bảo Long"
        elif "RANG DONG" in desc_upper:
            counterparty = "Công ty Cổ phần Xây dựng Rạng Đông"
        elif "DUYEN HAI" in desc_upper or "THIEN DUYEN" in desc_upper:
            counterparty = "Công ty Cổ phần Xây dựng Duyên Hải"
        elif "MINH TIEN" in desc_upper:
            counterparty = "Công ty TNHH Vận tải & Xây dựng Minh Tiến"
        elif "HUNG THINH" in desc_upper:
            counterparty = "Công ty TNHH TMDV & Đầu tư Hưng Thịnh"
        else:
            counterparty = "Khách hàng / Chủ đầu tư"

    # 6. Chi trả nhà cung cấp / thầu phụ (Outflow)
    elif direction == "outflow":
        cat = "SUPPLIER_PAYMENT"
        if "MANH HUNG" in desc_upper:
            counterparty = "Công ty TNHH TM SX Thép Mạnh Hùng"
        elif "TAM PHUC HUNG" in desc_upper:
            counterparty = "Công ty TNHH TM Tâm Phúc Hưng"
        elif "THIEN CHI" in desc_upper:
            counterparty = "Công ty TNHH Gốm Thiện Chí"
        elif "BACH HUNG" in desc_upper:
            counterparty = "Công ty TNHH Bách Hưng 89 Stone"
        elif "TUAN VINH" in desc_upper:
            counterparty = "Công ty TNHH Thủy lực Tuấn Vinh"
        elif "QUANG TRUNG" in desc_upper:
            counterparty = "Công ty TNHH TM Vận tải & Du lịch Quang Trung"
        elif "TRUNG NGHIA" in desc_upper:
            counterparty = "Công ty TNHH TM & XD Công trình Trung Nghĩa"
        elif "TRUNG KIEN" in desc_upper:
            counterparty = "Công ty TNHH Trung Kiên"
        elif "MAI HOA" in desc_upper:
            counterparty = "Công ty TNHH Mai Hoa"
        elif "DUC ANH" in desc_upper:
            counterparty = "Công ty TNHH Đầu tư & TMDV Đức Anh"
        elif "HOANG PHUC" in desc_upper:
            counterparty = "Công ty TNHH Xăng dầu Hoàng Phúc"
        elif "BAO LONG" in desc_upper:
            counterparty = "Công ty TNHH Phát triển TM & XD Bảo Long"
        elif "VIMC" in desc_upper:
            counterparty = "Công ty Cổ phần VIMC Logistics"
        else:
            counterparty = "Nhà cung cấp / Đối tác"

    # Match projects
    for p_name, p_id in projects_map.items():
        if p_name.lower() in desc.lower():
            matched_proj_id = p_id
            break
        # Match keywords
        if "ben kem" in desc.lower() and "ben kem" in p_name.lower():
            matched_proj_id = p_id
            break
        if "dong dam" in desc.lower() and "dong dam" in p_name.lower():
            matched_proj_id = p_id
            break
        if "dai phong" in desc.lower() and "dai phong" in p_name.lower():
            matched_proj_id = p_id
            break

    # Match invoices
    inv_matches = re.findall(r"\b(?:HD|H\u0110|HOA DON|SO)\s*[:#]?\s*([0-9]{1,8})\b", desc_upper)
    if inv_matches:
        for im in inv_matches:
            num_padded = f"{int(im):08d}"
            if num_padded in invoices_map:
                matched_inv_id = invoices_map[num_padded]
                break

    return cat, counterparty, matched_proj_id, matched_inv_id

def main():
    db = ErpDatabaseClient()
    with db.get_connection() as conn:
        with conn.cursor() as cur:
            # Load project mapping
            cur.execute("SELECT id, project_name FROM projects;")
            projects_map = {r["project_name"]: r["id"] for r in cur.fetchall()}

            # Load invoices mapping
            cur.execute("SELECT id, invoice_number FROM erp_invoices WHERE invoice_number IS NOT NULL;")
            invoices_map = {r["invoice_number"]: r["id"] for r in cur.fetchall()}

    all_txns = []

    # 1. ACB Excel (2025-2026)
    print("-> Đang bóc tách ACB Excel (2025-2026)...")
    acb_files = glob.glob(os.path.join(BASE_DIR, "**", "*13456888888*.xlsx"), recursive=True)
    for f in acb_files:
        fname = os.path.basename(f)
        try:
            wb = openpyxl.load_workbook(f, data_only=True)
            ws = wb.active
            for r in range(9, ws.max_row + 1):
                eff_date = ws.cell(r, 1).value
                txn_date = ws.cell(r, 2).value
                txn_no = ws.cell(r, 3).value
                desc = ws.cell(r, 4).value
                debit = ws.cell(r, 5).value
                credit = ws.cell(r, 6).value
                bal = ws.cell(r, 7).value
                if not txn_date and not desc: continue

                d = None
                if isinstance(txn_date, datetime): d = txn_date.date()
                elif isinstance(txn_date, str):
                    m = re.search(r"(\d{2})/(\d{2})/(\d{4})", txn_date)
                    if m: d = datetime.strptime(m.group(0), "%d/%m/%Y").date()
                if not d and eff_date:
                    if isinstance(eff_date, datetime): d = eff_date.date()
                    elif isinstance(eff_date, str):
                        m = re.search(r"(\d{2})/(\d{2})/(\d{4})", eff_date)
                        if m: d = datetime.strptime(m.group(0), "%d/%m/%Y").date()
                if not d: continue

                debit_val = float(str(debit).replace(",", "")) if debit and str(debit).strip() else 0.0
                credit_val = float(str(credit).replace(",", "")) if credit and str(credit).strip() else 0.0
                bal_val = float(str(bal).replace(",", "")) if bal and str(bal).strip() else 0.0
                ref = str(txn_no).strip() if txn_no else f"ACB-{d.strftime('%Y%m%d')}-{r}"
                desc_str = str(desc).strip() if desc else ""

                if credit_val > 0:
                    all_txns.append({
                        "bank": "ACB", "account": "13456888888", "date": d, "ref": ref,
                        "direction": "inflow", "amount": credit_val, "balance": bal_val,
                        "desc": desc_str, "file": fname
                    })
                if debit_val > 0:
                    all_txns.append({
                        "bank": "ACB", "account": "13456888888", "date": d, "ref": ref,
                        "direction": "outflow", "amount": debit_val, "balance": bal_val,
                        "desc": desc_str, "file": fname
                    })
        except Exception as e:
            print(f"Lỗi đọc {fname}: {e}")

    # 2. ACB 2024 PDF Statements
    print("-> Đang bóc tách ACB 2024 PDF Statements...")
    acb_pdf_files = sorted(glob.glob(os.path.join(BASE_DIR, "UNC-2024", "*SAOKE_TK_2024*.pdf")))
    for p in acb_pdf_files:
        fname = os.path.basename(p)
        try:
            with pdfplumber.open(p) as pdf:
                for page in pdf.pages:
                    tables = page.extract_tables()
                    for t in tables:
                        for row in t:
                            if not row or len(row) < 5: continue
                            row_str = " ".join(str(c) for c in row if c is not None)
                            date_match = re.search(r"(\d{2}/\d{2}/\d{4})", row_str)
                            if not date_match: continue
                            if any(k in row_str for k in ["Chủ tài khoản", "Ngày hiệu lực", "Thời gian sao kê"]): continue
                            d = datetime.strptime(date_match.group(1), "%d/%m/%Y").date()

                            txn_no = None
                            desc = ""
                            debit_val = 0.0
                            credit_val = 0.0
                            bal_val = 0.0
                            if len(row) >= 7:
                                txn_no = str(row[2]).strip() if row[2] else None
                                desc = str(row[3]).replace("\n", " ").strip() if row[3] else ""
                                debit_raw = str(row[4]).replace(".", "").replace(",", "").strip() if row[4] else ""
                                credit_raw = str(row[5]).replace(".", "").replace(",", "").strip() if row[5] else ""
                                bal_raw = str(row[6]).replace(".", "").replace(",", "").strip() if row[6] else ""
                                if debit_raw and debit_raw.isdigit(): debit_val = float(debit_raw)
                                if credit_raw and credit_raw.isdigit(): credit_val = float(credit_raw)
                                if bal_raw and bal_raw.isdigit(): bal_val = float(bal_raw)

                            ref = txn_no if txn_no else f"ACB-2024-{d.strftime('%Y%m%d')}"
                            if credit_val > 0:
                                all_txns.append({
                                    "bank": "ACB", "account": "13456888888", "date": d, "ref": ref,
                                    "direction": "inflow", "amount": credit_val, "balance": bal_val,
                                    "desc": desc, "file": fname
                                })
                            if debit_val > 0:
                                all_txns.append({
                                    "bank": "ACB", "account": "13456888888", "date": d, "ref": ref,
                                    "direction": "outflow", "amount": debit_val, "balance": bal_val,
                                    "desc": desc, "file": fname
                                })
        except Exception as e:
            print(f"Lỗi đọc {fname}: {e}")

    # 3. Vietcombank (VCB 2042238888)
    print("-> Đang bóc tách Vietcombank (VCB)...")
    vcb_files = glob.glob(os.path.join(BASE_DIR, "**", "*2042238888*.xls"), recursive=True) +                 glob.glob(os.path.join(BASE_DIR, "**", "lich-su-giao-dich*.xls"), recursive=True)
    for f in sorted(set(vcb_files)):
        fname = os.path.basename(f)
        try:
            book = xlrd.open_workbook(f)
            sh = book.sheet_by_index(0)
            for r in range(sh.nrows):
                row = sh.row_values(r)
                if len(row) >= 7 and isinstance(row[0], (int, float)) and row[0] > 0:
                    date_col = str(row[1]).strip()
                    m = re.search(r"(\d{2}/\d{2}/\d{4})", date_col)
                    if not m: continue
                    d = datetime.strptime(m.group(1), "%d/%m/%Y").date()

                    # Extract Doc No
                    doc_m = re.search(r"/\s*([A-Za-z0-9\-_\s]+)", date_col)
                    ref = doc_m.group(1).replace(" ", "") if doc_m else f"VCB-{d.strftime('%Y%m%d')}-{r}"

                    debit_raw = str(row[3]).replace(",", "").strip()
                    credit_raw = str(row[4]).replace(",", "").strip()
                    bal_raw = str(row[5]).replace(",", "").strip()
                    desc = str(row[6]).strip()

                    debit = float(debit_raw) if debit_raw else 0.0
                    credit = float(credit_raw) if credit_raw else 0.0
                    bal = float(bal_raw) if bal_raw else 0.0

                    if credit > 0:
                        all_txns.append({
                            "bank": "VIETCOMBANK", "account": "2042238888", "date": d, "ref": ref,
                            "direction": "inflow", "amount": credit, "balance": bal,
                            "desc": desc, "file": fname
                        })
                    if debit > 0:
                        all_txns.append({
                            "bank": "VIETCOMBANK", "account": "2042238888", "date": d, "ref": ref,
                            "direction": "outflow", "amount": debit, "balance": bal,
                            "desc": desc, "file": fname
                        })
        except Exception as e:
            print(f"Lỗi đọc {fname}: {e}")

    # 4. Techcombank (TCB 07898888)
    print("-> Đang bóc tách Techcombank (TCB)...")
    tcb_pdfs = sorted(glob.glob(os.path.join(BASE_DIR, "**", "xxxxxxxxxx8888*.pdf"), recursive=True))
    for p in tcb_pdfs:
        fname = os.path.basename(p)
        try:
            with pdfplumber.open(p, password="39912136") as pdf:
                for page in pdf.pages:
                    tables = page.extract_tables()
                    for t in tables:
                        for row in t:
                            if not row or len(row) < 5: continue
                            row_str = " ".join(str(c) for c in row if c is not None)
                            date_match = re.search(r"(\d{2}/\d{2}/\d{4})", row_str)
                            if not date_match: continue
                            if any(k in row_str for k in ["Số dư đầu kỳ", "Cộng doanh số", "Số dư cuối"]): continue
                            d = datetime.strptime(date_match.group(1), "%d/%m/%Y").date()

                            ft_match = re.search(r"(FT\w+|MD\w+|\d{8}\.ICP\.\w+|\d{8}-\d{8})", row_str)
                            ref = ft_match.group(0) if ft_match else f"TCB-{d.strftime('%Y%m%d')}"

                            debit_val = 0.0
                            credit_val = 0.0
                            bal_val = 0.0
                            desc = ""
                            for c in row:
                                if c and any(kw in str(c) for kw in ["Thanh toan", "tra tien", "TT", "Cty", "Phong", "chuyen", "Rut", "Phi", "lai"]):
                                    desc = str(c).replace("\n", " ").strip()
                                    break
                            if not desc: desc = row_str[:120]

                            if len(row) >= 7:
                                r5 = str(row[5] or "").replace(",", "").strip()
                                r6 = str(row[6] or "").replace(",", "").strip()
                                r_last = str(row[-1] or "").replace(",", "").strip()
                                try:
                                    if r5: debit_val = float(r5)
                                except ValueError: pass
                                try:
                                    if r6: credit_val = float(r6)
                                except ValueError: pass
                                try:
                                    if r_last: bal_val = float(r_last)
                                except ValueError: pass

                            if credit_val > 0:
                                all_txns.append({
                                    "bank": "TECHCOMBANK", "account": "07898888", "date": d, "ref": ref,
                                    "direction": "inflow", "amount": credit_val, "balance": bal_val,
                                    "desc": desc, "file": fname
                                })
                            if debit_val > 0:
                                all_txns.append({
                                    "bank": "TECHCOMBANK", "account": "07898888", "date": d, "ref": ref,
                                    "direction": "outflow", "amount": debit_val, "balance": bal_val,
                                    "desc": desc, "file": fname
                                })
        except Exception as e:
            print(f"Lỗi đọc {fname}: {e}")

    # 5. VPBank (8667898888)
    print("-> Đang bóc tách VPBank...")
    vpb_files = glob.glob(os.path.join(BASE_DIR, "UNC-2026", "VPBank 2026", "*.xls"))
    for f in sorted(vpb_files):
        fname = os.path.basename(f)
        try:
            book = xlrd.open_workbook(f)
            sh = book.sheet_by_index(0)
            for r in range(17, sh.nrows):
                row = sh.row_values(r)
                if len(row) >= 7 and str(row[0]).strip().isdigit():
                    ref = str(row[1]).strip()
                    date_str = str(row[2]).strip()
                    m = re.search(r"(\d{2}/\d{2}/\d{4})", date_str)
                    if not m: continue
                    d = datetime.strptime(m.group(1), "%d/%m/%Y").date()

                    credit = float(row[3]) if isinstance(row[3], (int, float)) else 0.0
                    debit = float(row[4]) if isinstance(row[4], (int, float)) else 0.0
                    desc = str(row[5]).strip()
                    bal_raw = str(row[6]).replace(",", "").strip()
                    bal = float(bal_raw) if bal_raw else 0.0

                    if credit > 0:
                        all_txns.append({
                            "bank": "VPBANK", "account": "8667898888", "date": d, "ref": ref,
                            "direction": "inflow", "amount": credit, "balance": bal,
                            "desc": desc, "file": fname
                        })
                    if debit > 0:
                        all_txns.append({
                            "bank": "VPBANK", "account": "8667898888", "date": d, "ref": ref,
                            "direction": "outflow", "amount": debit, "balance": bal,
                            "desc": desc, "file": fname
                        })
        except Exception as e:
            print(f"Lỗi đọc {fname}: {e}")

    # 6. UNC PDFs (2021-2023)
    print("-> Đang bóc tách bổ sung các UNC PDF (2021-2023)...")
    for y in ["2021", "2022", "2023"]:
        pat = os.path.join(BASE_DIR, f"UNC-{y}", "**", "*.pdf")
        for u in glob.glob(pat, recursive=True):
            fname = os.path.basename(u)
            try:
                reader = pypdf.PdfReader(u)
                text = reader.pages[0].extract_text()
                date_match = re.search(r"Ngày/Date\s*([0-9]{1,2}/[0-9]{1,2}/[0-9]{4})", text)
                no_match = re.search(r"Số/\s*No\.\s*([A-Za-z0-9\-_]+)", text)
                amt_match = re.search(r"Số tiền bằng số/.*?\s*([0-9\.,]+)\s*VND", text)
                det_match = re.search(r"Nội dung/.*?\s*(.*?)\n\s*Phí chuyển tiền", text, re.DOTALL)
                
                if date_match and amt_match:
                    d = datetime.strptime(date_match.group(1), "%d/%m/%Y").date()
                    amt = float(amt_match.group(1).replace(".", "").replace(",", ""))
                    ref = f"UNC-{no_match.group(1)}" if no_match else f"UNC-{d.strftime('%Y%m%d')}"
                    desc = det_match.group(1).strip() if det_match else fname
                    
                    all_txns.append({
                        "bank": "ACB", "account": "13456888888", "date": d, "ref": ref,
                        "direction": "outflow", "amount": amt, "balance": 0.0,
                        "desc": desc, "file": fname
                    })
            except Exception:
                pass

    print(f"=== TỔNG HỢP TOÀN BỘ GIAO DỊCH TRÍCH XUẤT: {len(all_txns)} GIAO DỊCH ===")

    # Ingest into PostgreSQL database
    with db.get_connection() as conn:
        with conn.cursor() as cur:
            inserted = 0
            updated = 0
            for t in all_txns:
                cat, counterparty, p_id, inv_id = categorize_and_match(
                    t["desc"], t["direction"], t["amount"], projects_map, invoices_map
                )

                cur.execute("""
                    INSERT INTO erp_bank_transactions (
                        company_id, bank_name, account_number, transaction_date,
                        reference_number, direction, amount, running_balance,
                        counterparty_name, description, transaction_category,
                        matched_project_id, matched_invoice_id, source_file
                    ) VALUES (
                        %s, %s, %s, %s,
                        %s, %s, %s, %s,
                        %s, %s, %s,
                        %s, %s, %s
                    )
                    ON CONFLICT (bank_name, account_number, reference_number, transaction_date, amount, direction)
                    DO UPDATE SET
                        description = EXCLUDED.description,
                        transaction_category = EXCLUDED.transaction_category,
                        matched_project_id = COALESCE(EXCLUDED.matched_project_id, erp_bank_transactions.matched_project_id),
                        matched_invoice_id = COALESCE(EXCLUDED.matched_invoice_id, erp_bank_transactions.matched_invoice_id),
                        counterparty_name = EXCLUDED.counterparty_name,
                        source_file = EXCLUDED.source_file;
                """, (
                    COMPANY_ID, t["bank"], t["account"], t["date"],
                    t["ref"], t["direction"], t["amount"], t["balance"],
                    counterparty, t["desc"], cat,
                    p_id, inv_id, t["file"]
                ))
                inserted += 1

            conn.commit()
            print(f"Đã lưu an toàn và đồng bộ thành công {inserted} giao dịch ngân hàng vào PostgreSQL!")

if __name__ == "__main__":
    main()