@echo off & choice /M "Are you sure you want to run The Great PDF Slicer 3000?" & if not errorlevel 2 (call python -x "%~f0" %*) & pause & exit /b
from pypdf import PdfReader, PdfWriter
import pandas as pd
import re
import os

# ---------------- CONFIG ----------------
# Auto-detect Excel file
excel_files = [f for f in os.listdir('.') if f.lower().endswith(('.xlsx', '.xls')) and not f.startswith('~$')]
if not excel_files:
    print("❌ Error: No Excel file (.xlsx or .xls) found in the folder.")
    exit(1)
EXCEL_FILE = excel_files[0]

# Auto-detect PDF files
pdf_files = [f for f in os.listdir('.') if f.lower().endswith('.pdf')]
if not pdf_files:
    print("❌ Error: No PDF files found in the folder.")
    exit(1)

print(f"📂 Auto-detected Excel: {EXCEL_FILE}")
print(f"📄 Found {len(pdf_files)} PDF files to process.")

# ---------------- NORMALIZE ----------------
def normalize(val):
    return str(val).strip().upper()

# ---------------- READ EXCEL ----------------
print("📖 Reading Excel mappings...")
try:
    df = pd.read_excel(EXCEL_FILE)
except Exception as e:
    print(f"❌ Error reading Excel: {e}")
    exit(1)

# Auto-detect columns
voucher_col = None
ledger_col = None

for col in df.columns:
    col_clean = str(col).strip().upper()
    if "VOUCHER NO" in col_clean:
        voucher_col = col
    elif "LEDGER NAME" in col_clean:
        ledger_col = col

if not voucher_col or not ledger_col:
    print(f"❌ Error: Could not find required headers 'Voucher No' or 'Ledger Name'.")
    print(f"Found columns: {list(df.columns)}")
    exit(1)

print(f"🔍 Using columns: '{voucher_col}' and '{ledger_col}'")

# Build lookup map
df["Voucher_Key"] = df[voucher_col].apply(normalize)
voucher_to_company = dict(zip(df["Voucher_Key"], df[ledger_col]))
print(f"✅ Loaded {len(voucher_to_company)} mappings from Excel")

# ---------------- PROCESS ALL PDFs ----------------
invoice_writers = {}  # company -> PdfWriter
credit_writers = {}   # company -> PdfWriter

for pdf_path in pdf_files:
    print(f"\n🔍 Processing file: {pdf_path}")
    try:
        reader = PdfReader(pdf_path)
        print(f"   Pages to analyze: {len(reader.pages)}")

        for i, page in enumerate(reader.pages):
            text = page.extract_text() or ""
            
            # 1. Check for Invoice
            inv_match = re.search(r"Invoice\s*:\s*([A-Za-z0-9\-\/]+?)(?=Date|\s|$)", text, re.IGNORECASE)
            
            # 2. Check for Credit Note
            crn_match = re.search(r"(?:Cr\.\s*Note\s*:\s*)?(\d{2}-\d{2}/CRN\d+?)(?=Date|\s|$)", text, re.IGNORECASE)
            
            found_type = None
            voucher_no = None
            
            if inv_match:
                found_type = "Invoice"
                voucher_no = normalize(inv_match.group(1))
            elif crn_match:
                found_type = "CreditNote"
                voucher_no = normalize(crn_match.group(1))
            
            if not found_type:
                company = "Unknown"
                if company not in invoice_writers:
                    invoice_writers[company] = PdfWriter()
                invoice_writers[company].add_page(page)
                continue

            # Get company name
            company = voucher_to_company.get(voucher_no, "Unknown")
            
            # Clean company name
            company = re.sub(r'[\\/*?:"<>|]', '', str(company)).strip()
            if not company: company = "Unknown"
            
            # Add to respective writer
            if found_type == "Invoice":
                if company not in invoice_writers:
                    invoice_writers[company] = PdfWriter()
                invoice_writers[company].add_page(page)
            else:
                if company not in credit_writers:
                    credit_writers[company] = PdfWriter()
                credit_writers[company].add_page(page)

            if (i + 1) % 200 == 0:
                print(f"   Processed {i+1} pages...")
    except Exception as e:
        print(f"⚠️ Error processing {pdf_path}: {e}")

# ---------------- SAVE OUTPUT ----------------
print("💾 Saving merged PDFs...")

all_companies = set(invoice_writers.keys()) | set(credit_writers.keys())

for company in all_companies:
    os.makedirs(company, exist_ok=True)
    
    # Save Invoices
    if company in invoice_writers:
        output_path = os.path.join(company, f"{company}_Invoices.pdf")
        with open(output_path, "wb") as f:
            invoice_writers[company].write(f)
            
    # Save Credit Notes
    if company in credit_writers:
        output_path = os.path.join(company, f"{company}_CreditNotes.pdf")
        with open(output_path, "wb") as f:
            credit_writers[company].write(f)

print("\n🎉 DONE — All Invoices and Credit Notes have been merged company-wise!")