#!/usr/bin/env python3
import csv
import re
from decimal import Decimal
from datetime import datetime
from pathlib import Path

RAW_DIR = Path("/home/zaine/master-folder/attachments/bank-statements/nationwide/raw")
CLEANED_DIR = Path("/home/zaine/master-folder/attachments/bank-statements/nationwide/cleaned")

# Only keep digits, dot, minus for money
MONEY_REGEX = re.compile(r"[^\d\.-]")

def clean_money(value: str):
    if not value:
        return None
    # Remove all non-numeric characters
    value = MONEY_REGEX.sub("", value)
    if not value:
        return None
    return Decimal(value)

def parse_date(value: str):
    # Input format: 27 Feb 2026
    return datetime.strptime(value.strip(), "%d %b %Y").strftime("%m/%d/%Y")

def convert(in_path: Path, out_path: Path):
    rows_written = 0

    # Read raw bytes and decode aggressively
    with open(in_path, "rb") as f:
        lines = f.read().decode("utf-8", errors="ignore").splitlines()

    with open(out_path, "w", newline="", encoding="utf-8") as f_out:
        writer = csv.writer(f_out, lineterminator="\r\n")
        writer.writerow(["Date", "Payee", "Description", "Amount"])

        for row in csv.reader(lines):
            if not row:
                continue
            first = row[0].strip()
            if first.startswith("Account") or first == "Date":
                continue
            if len(row) < 5:
                continue

            try:
                date = parse_date(row[0])
            except:
                continue

            desc = row[2].strip()
            paid_out = clean_money(row[3])
            paid_in = clean_money(row[4])

            if paid_out is not None:
                amount = f"{-paid_out:.2f}"
            elif paid_in is not None:
                amount = f"{paid_in:.2f}"
            else:
                continue

            writer.writerow([date, desc, desc, amount])
            rows_written += 1

    return rows_written

def main():
    CLEANED_DIR.mkdir(parents=True, exist_ok=True)

    files = sorted(RAW_DIR.glob("*.csv"))
    if not files:
        print("No CSV files found.")
        return

    print("\nSelect a Nationwide CSV to clean:\n")
    for i, f in enumerate(files, 1):
        print(f"  [{i}] {f.name}")

    choice = int(input("\nEnter number: "))
    in_path = files[choice - 1]
    out_path = CLEANED_DIR / f"{in_path.stem}-cleaned.csv"

    n = convert(in_path, out_path)
    print(f"\n✔ {n} rows written to:\n  {out_path}\n")

if __name__ == "__main__":
    main()