# --- install (if needed) ---
# !pip install pdfplumber pandas openpyxl

import re
import os
import pandas as pd
import pdfplumber
import openpyxl
from openpyxl.styles import Font, Alignment
from openpyxl.worksheet.table import Table, TableStyleInfo

# -----------------------------
# Paths
# -----------------------------
pdf_path = r"C:\Users\jmulhall\Downloads\DTB_janfeb2026.pdf"
out_xlsx = "meditech_trial_balance_parsed_0630.xlsx"

# -----------------------------
# Regex
# -----------------------------
money_re = re.compile(r"-?\d{1,3}(?:,\d{3})*(?:\.\d{2})")
acct_re = re.compile(r"^(?P<account>\d{2}\.\d{5}\.\d{5})\s+-\s+(?P<fund>\d{2})\s+(?P<name>.+)$")
journal_re = re.compile(
    r"^(?P<journal>[A-Z][A-Z0-9/]*)\s+"
    r"(?P<date>\d{2}/\d{2}/\d{2})(?P<bch>\d{1,2})?\s+"
    r"(?P<entry>\d+(?:-\d+)?)\b"
)

# -----------------------------
# Helpers
# -----------------------------
def parse_money(s):
    if not s:
        return None
    m = money_re.search(s)
    return float(m.group(0).replace(",", "")) if m else None

def is_page_header_noise(s):
    s = " ".join(s.split()).upper()
    return (
        ("FISCAL CALENDAR" in s or "FIRST CALENDAR" in s)
        and ("ROUND MONEY" in s or "TRIAL" in s)
    )

def clean_description(desc):
    if not desc or is_page_header_noise(desc):
        return ""
    patterns = [
        r"FISCAL\s+CALENDAR\s+[A-Z]{3,6}\s*,?\s*[A-Z]{3}\s+20\d{2}",
        r"FIRST\s+CALENDAR\s+[A-Z]{3,6}\s*,?\s*[A-Z]{3}\s+20\d{2}",
        r"ROUND\s+MONEY\s*:\s*\d+\.\d+",
        r"\bTRIAL\b",
    ]
    for p in patterns:
        desc = re.sub(p, "", desc, flags=re.IGNORECASE)
    return " ".join(desc.split())

# -----------------------------
# Parse PDF
# -----------------------------
transactions = []
acct_summaries = []
report_title_parts = {"fiscal": None, "period": None, "trial": None}

with pdfplumber.open(pdf_path) as pdf:
    for page in pdf.pages:
        text = page.extract_text(layout=True) or ""
        lines = text.splitlines()

        # Capture report title once
        for ln in lines:
            s = ln.strip()
            if report_title_parts["fiscal"] is None and "Fiscal Calendar" in s:
                report_title_parts["fiscal"] = " ".join(s.split())
            if report_title_parts["period"] is None and re.search(r"\b[A-Z]{3}\s+20\d{2}\b", s):
                report_title_parts["period"] = " ".join(s.split())
            if report_title_parts["trial"] is None and s.upper() == "TRIAL":
                report_title_parts["trial"] = "TRIAL"

        # Locate headers
        txn_header = acct_header = None
        for i, ln in enumerate(lines):
            if "JOURNAL" in ln and "DESCRIPTION" in ln and "DEBITS" in ln:
                txn_header = i
            if "ACCOUNT" in ln and "OPEN" in ln and "CLOSE" in ln:
                acct_header = i

        deb_pos = cred_pos = desc_pos = None
        open_pos = adeb_pos = acredit_pos = change_pos = close_pos = None

        if txn_header is not None:
            h = lines[txn_header]
            deb_pos = h.find("DEBITS")
            cred_pos = h.find("CREDITS", deb_pos)
            desc_pos = h.find("DESCRIPTION", cred_pos)

        if acct_header is not None:
            h = lines[acct_header]
            open_pos = h.find("OPEN")
            adeb_pos = h.find("DEBITS", open_pos)
            acredit_pos = h.find("CREDITS", adeb_pos)
            change_pos = h.find("CHANGE", acredit_pos)
            close_pos = h.find("CLOSE", change_pos)

        current_account = None
        current_account_name = None
        pending_totals = False
        last_txn = None
        i = 0

        while i < len(lines):
            ln = lines[i]
            s = ln.strip()

            if not s or is_page_header_noise(s):
                i += 1
                continue

            # ACCOUNT header
            m = acct_re.match(s)
            if m:
                current_account = m.group("account")
                current_account_name = m.group("name").strip()
                pending_totals = True
                last_txn = None
                i += 1
                continue

            # ACCOUNT totals
            if pending_totals and money_re.search(s):
                nums = [float(x.replace(",", "")) for x in money_re.findall(s)]
                nums += [None] * 5
                acct_summaries.append({
                    "ACCOUNT_NAME": current_account_name,
                    "ACCOUNT": current_account,
                    "OPEN": nums[0],
                    "TOTAL_DEBITS": nums[1],
                    "TOTAL_CREDITS": nums[2],
                    "NET_CHANGE": nums[3],
                    "CLOSE": nums[4],
                })
                pending_totals = False
                i += 1
                continue

            # TRANSACTION
            m = journal_re.match(s)
            if m and current_account:
                journal = m.group("journal")
                date = m.group("date")
                bch = int(m.group("bch")) if m.group("bch") else None
                entry = m.group("entry")

                debit = credit = None
                desc = ""

                if deb_pos is not None and cred_pos is not None:
                    debit = parse_money(ln[deb_pos:cred_pos])
                    credit = parse_money(ln[cred_pos:desc_pos])
                    desc = ln[desc_pos:].strip()
                else:
                    after = s[m.end():].strip()
                    nums = money_re.findall(after)
                    if nums:
                        debit = float(nums[0].replace(",", ""))
                    desc = money_re.sub("", after).strip()

                row = {
                    "ACCOUNT_NAME": current_account_name,
                    "ACCOUNT": current_account,
                    "JOURNAL": journal,
                    "DATE": date,
                    "BCH": bch,
                    "ENTRY": entry,
                    "DEBITS": debit,
                    "CREDITS": credit,
                    "DESCRIPTION": clean_description(desc),
                }
                transactions.append(row)
                last_txn = row
                i += 1
                continue

            # Continuation DESCRIPTION
            if last_txn:
                add = clean_description(s)
                if add:
                    last_txn["DESCRIPTION"] += " " + add
                i += 1
                continue

            i += 1

# -----------------------------
# Finalize
# -----------------------------
title = ", ".join(v for v in report_title_parts.values() if v)

df_txn = pd.DataFrame(transactions)
df_acct = pd.DataFrame(acct_summaries)

# -----------------------------
# Export XLSX
# -----------------------------
with pd.ExcelWriter(out_xlsx, engine="openpyxl") as writer:
    df_txn.to_excel(writer, "Transactions", index=False, startrow=2)
    df_acct.to_excel(writer, "Account Summary", index=False, startrow=2)

wb = openpyxl.load_workbook(out_xlsx)
for sheet in ["Transactions", "Account Summary"]:
    ws = wb[sheet]
    ws["A1"] = title
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=ws.max_column)
    ws["A1"].font = Font(bold=True)
    ws.freeze_panes = "A4"

wb.save(out_xlsx)

print("✔ Parsed successfully")
print("Transactions:", df_txn.shape)
print("Account Summary:", df_acct.shape)
print("Output:", out_xlsx)