#!/usr/bin/env python3
"""
AFIR FEGA CSV importer — produces pipe-TSV ingestible by the same SQL
loader as the FEADR XLSX path. Schema is identical to FEADR (15 columns):
beneficiar, last_name, mama_cui, localitate, cod_masura, obiectiv,
data_start, data_end, fega_op, fega_total, feadr_op, feadr_total,
op_amount, cofinantare, ue_total.

FEGA CSV from AFIR portal uses:
- comma-separated columns (English decimal, e.g. "4802.43")
- CSV header row: DenumireBeneficiar,NumeFamilie,Cui,Localicate,Masura,
  ObiectivSpecific,DataIncepere,DataSfarsit,CuantumOperationeFEGA,
  CuantumTotalFega,CuantumOperatiuneFEADR,CuantumtotalFEADR,
  CuantumAferentOperatiune,CuantumTotalCofinantareBeneficiar,
  CuantumtotalUEBenefeciar
"""

from __future__ import annotations
import csv
import re
import sys


def norm_num(v):
    if v is None:
        return ""
    s = str(v).strip()
    if not s:
        return ""
    # FEGA uses English format already ("4802.43") — comma swap unnecessary
    # but tolerate Romanian-style as defensive measure.
    if "," in s and "." not in s:
        s = s.replace(",", ".")
    elif "," in s and "." in s:
        s = s.replace(".", "").replace(",", ".")
    return s.replace("|", "/")


def norm_text(v):
    if v is None:
        return ""
    s = str(v).strip()
    if not s:
        return ""
    s = s.replace("|", "/").replace("\t", " ").replace("\r", " ").replace("\n", " ")
    s = re.sub(r"\s+", " ", s)
    s = s.replace("\\", "\\\\")
    return s


def main():
    if len(sys.argv) != 3:
        print("usage: import-afir-fega-csv.py INPUT.csv OUTPUT.tsv", file=sys.stderr)
        sys.exit(2)
    in_path, out_path = sys.argv[1], sys.argv[2]

    n_data = 0
    n_skipped = 0

    # FEGA CSV files include 1+ header rows. Auto-skip until we see a
    # row that doesn't start with "DenumireBeneficiar".
    with open(in_path, "r", encoding="utf-8-sig", errors="replace", newline="") as fin, \
         open(out_path, "w", encoding="utf-8") as fout:
        reader = csv.reader(fin)
        for r in reader:
            if not r:
                continue
            # Skip header row(s)
            if r[0].strip().lower().startswith("denumirebeneficiar"):
                continue
            # Pad to 15 columns
            cells = r + [""] * (15 - len(r))
            beneficiar = norm_text(cells[0])
            if not beneficiar:
                n_skipped += 1
                continue
            out = [
                beneficiar,
                norm_text(cells[1]),    # last_name
                norm_text(cells[2]),    # mama_cui (FEGA Cui)
                norm_text(cells[3]),    # localitate (Localicate typo in source)
                norm_text(cells[4]),    # cod_masura (Masura)
                norm_text(cells[5]),    # obiectiv (ObiectivSpecific)
                norm_text(cells[6]),    # data_start (DataIncepere)
                norm_text(cells[7]),    # data_end (DataSfarsit)
                norm_num(cells[8]),     # fega_op
                norm_num(cells[9]),     # fega_total
                norm_num(cells[10]),    # feadr_op
                norm_num(cells[11]),    # feadr_total
                norm_num(cells[12]),    # op_amount (CuantumAferentOperatiune)
                norm_num(cells[13]),    # cofinantare (CuantumTotalCofinantareBeneficiar)
                norm_num(cells[14]),    # ue_total (CuantumtotalUEBenefeciar)
            ]
            fout.write("|".join(out) + "\n")
            n_data += 1
            if n_data % 100000 == 0:
                print(f"[afir-fega-import] wrote {n_data} rows", file=sys.stderr)

    print(f"[afir-fega-import] done: {n_data} rows · {n_skipped} skipped", file=sys.stderr)


if __name__ == "__main__":
    main()