import pdfplumber
import pandas as pd
import re
import os

PDF_DIR = "/private/var/folders/t6/_sx_03q50_v9w2jpnq683jmw0000gn/T/claude-run-vl4i11ig"
pdfs = [
    ("Q1 2025", "Q1_2025_Revenue_Acme_Corp.pdf"),
    ("Q2 2025", "Q2_2025_Revenue_Acme_Corp.pdf"),
    ("Q3 2025", "Q3_2025_Revenue_Acme_Corp_SCAN.pdf"),
]

# Regex for revenue lines: Category Region Amount YoY
line_pattern = re.compile(
    r"^(Enterprise Software|Cloud Services|Professional Services)\s+"
    r"(North America|EMEA|APAC)\s+"
    r"([\d,]+)\s+"
    r"(\+[\d.]+%)\s*$"
)
total_pattern = re.compile(r"^TOTAL\s+Q\d\s+2025\s+REVENUE\s+([\d,]+)\s+(\+[\d.]+%)")

all_rows = []
all_texts = {}
all_notes = {}
totals = {}

for quarter, fname in pdfs:
    path = os.path.join(PDF_DIR, fname)
    with pdfplumber.open(path) as pdf:
        text = pdf.pages[0].extract_text()
    all_texts[quarter] = text

    # Parse notes
    notes_section = text.split("Notes:")[-1].strip() if "Notes:" in text else ""
    all_notes[quarter] = notes_section

    for line in text.split("\n"):
        line = line.strip()
        m = line_pattern.match(line)
        if m:
            amount_str = m.group(3).replace(",", "")
            all_rows.append({
                "Quarter": quarter,
                "Category": m.group(1),
                "Region": m.group(2),
                "Amount ($)": int(amount_str),
                "YoY Change": m.group(4),
                "Source": fname,
            })
        tm = total_pattern.match(line)
        if tm:
            totals[quarter] = {
                "amount": int(tm.group(1).replace(",", "")),
                "yoy": tm.group(2),
            }

df = pd.DataFrame(all_rows)

# Verify totals
print("=" * 80)
print("ACME CORPORATION - CONSOLIDATED PARTNER QUARTERLY REVENUE (Q1-Q3 2025)")
print("Extracted from 3 PDF reports for board review")
print("=" * 80)

# --- CONSOLIDATED TABLE ---
print("\n## CONSOLIDATED REVENUE BY QUARTER, CATEGORY & REGION\n")
print(df.to_string(index=False))

# --- SUMMARY BY QUARTER ---
print("\n\n## QUARTERLY TOTALS\n")
q_summary = df.groupby("Quarter")["Amount ($)"].sum().reset_index()
q_summary = q_summary.sort_values("Quarter")
for _, row in q_summary.iterrows():
    q = row["Quarter"]
    computed = row["Amount ($)"]
    reported = totals.get(q, {})
    match = "MATCH" if reported.get("amount") == computed else "MISMATCH"
    print(f"  {q}: ${computed:>12,}  (reported: ${reported.get('amount',0):,}, YoY: {reported.get('yoy','N/A')})  [{match}]")

ytd = q_summary["Amount ($)"].sum()
print(f"\n  YTD (Q1-Q3): ${ytd:>12,}")

# --- SUMMARY BY CATEGORY ---
print("\n\n## REVENUE BY CATEGORY (Q1-Q3 2025)\n")
cat_summary = df.groupby("Category")["Amount ($)"].sum().sort_values(ascending=False).reset_index()
for _, row in cat_summary.iterrows():
    pct = row["Amount ($)"] / ytd * 100
    print(f"  {row['Category']:<25} ${row['Amount ($)']:>12,}  ({pct:.1f}%)")

# --- SUMMARY BY REGION ---
print("\n\n## REVENUE BY REGION (Q1-Q3 2025)\n")
reg_summary = df.groupby("Region")["Amount ($)"].sum().sort_values(ascending=False).reset_index()
for _, row in reg_summary.iterrows():
    pct = row["Amount ($)"] / ytd * 100
    print(f"  {row['Region']:<25} ${row['Amount ($)']:>12,}  ({pct:.1f}%)")

# --- QoQ GROWTH ---
print("\n\n## QUARTER-OVER-QUARTER GROWTH\n")
q_vals = q_summary.set_index("Quarter")["Amount ($)"]
pairs = [("Q1 2025", "Q2 2025"), ("Q2 2025", "Q3 2025")]
for prev, curr in pairs:
    growth = (q_vals[curr] - q_vals[prev]) / q_vals[prev] * 100
    print(f"  {prev} -> {curr}: +${q_vals[curr] - q_vals[prev]:,} ({growth:+.1f}%)")

# --- AUDIT: TEXT EXTRACTS ---
print("\n\n" + "=" * 80)
print("AUDIT SECTION - FULL TEXT EXTRACTS FROM EACH PDF")
print("=" * 80)
for quarter, fname in pdfs:
    print(f"\n{'─' * 80}")
    print(f"SOURCE: {fname}")
    print(f"QUARTER: {quarter}")
    is_scan = "SCAN" in fname
    print(f"TYPE: {'Scanned document' if is_scan else 'Digital PDF'}")
    print(f"{'─' * 80}")
    print(all_texts[quarter])

# --- AUDIT: NOTES ---
print(f"\n\n{'=' * 80}")
print("AUDIT SECTION - QUARTERLY NOTES EXTRACTED FROM REPORTS")
print("=" * 80)
for quarter, fname in pdfs:
    print(f"\n[{quarter}] ({fname})")
    print(all_notes[quarter])

# --- AUDIT: INTEGRITY CHECKS ---
print(f"\n\n{'=' * 80}")
print("AUDIT SECTION - DATA INTEGRITY CHECKS")
print("=" * 80)
print(f"\n  Files processed: {len(pdfs)}")
print(f"  Line items extracted: {len(df)}")
print(f"  Categories: {df['Category'].nunique()} ({', '.join(df['Category'].unique())})")
print(f"  Regions: {df['Region'].nunique()} ({', '.join(df['Region'].unique())})")
all_ok = True
for q in ["Q1 2025", "Q2 2025", "Q3 2025"]:
    computed = q_vals[q]
    reported = totals[q]["amount"]
    status = "OK" if computed == reported else "FAIL"
    if status == "FAIL":
        all_ok = False
    print(f"  {q} sum check: computed ${computed:,} vs reported ${reported:,} -> {status}")
print(f"\n  Overall integrity: {'ALL CHECKS PASSED' if all_ok else 'ISSUES DETECTED'}")

# Save CSV
csv_path = os.path.join(PDF_DIR, "consolidated_revenue.csv")
df.to_csv(csv_path, index=False)
print(f"\n  CSV exported: {csv_path}")