#!/usr/bin/env python3
"""Cut API Bay upload CSVs from the Acme seed (acme-database.sql).

Usage: python3 -I cut-apibay-csvs.py <path-to-acme-database.sql> <out-dir>

Extracts department, employee and attendance rows from the seed's
single-line INSERT statements and writes one CSV per table, first row
headers. Ids are materialized 1..N in insert order, exactly as SERIAL
assigns them, so the CSVs match what the seed loads into Postgres.
SQL NULL becomes an empty cell; TRUE/FALSE become true/false.
"""
import csv, re, sys

TABLES = {
    "department": ["id", "name", "cost_centre", "head_email", "created_at"],
    "employee": ["id", "full_name", "email", "department_id", "manager_id",
                 "title", "hired_on", "left_on", "phone", "is_active"],
    "attendance": ["id", "employee_id", "on_date", "hours", "source"],
}

LINE = re.compile(r"^INSERT INTO (\w+) \(([^)]*)\) VALUES \((.*)\);$")


def split_values(s):
    """Split a SQL VALUES payload on top-level commas, honoring '' escapes."""
    out, buf, in_str, i = [], [], False, 0
    while i < len(s):
        c = s[i]
        if in_str:
            if c == "'":
                if i + 1 < len(s) and s[i + 1] == "'":  # escaped quote
                    buf.append("'")
                    i += 1
                else:
                    in_str = False
            else:
                buf.append(c)
        elif c == "'":
            in_str = True
        elif c == ",":
            out.append("".join(buf).strip())
            buf = []
        elif c != " " or buf:
            buf.append(c)
        i += 1
    out.append("".join(buf).strip())
    return out


def convert(tok):
    if tok == "NULL":
        return ""
    if tok in ("TRUE", "FALSE"):
        return tok.lower()
    return tok


def main(sql_path, out_dir):
    rows = {t: [] for t in TABLES}
    with open(sql_path, encoding="utf-8") as f:
        for line in f:
            m = LINE.match(line.rstrip("\n"))
            if not m or m.group(1) not in TABLES:
                continue
            table, cols, vals = m.group(1), m.group(2), m.group(3)
            got = [c.strip() for c in cols.split(",")]
            want = TABLES[table][1:]  # seed inserts omit id (SERIAL)
            if got != want:
                sys.exit(f"{table}: column list drifted: {got}")
            toks = split_values(vals)
            if len(toks) != len(want):
                sys.exit(f"{table}: {len(toks)} values for {len(want)} columns: {line[:120]}")
            rows[table].append([str(len(rows[table]) + 1)] + [convert(t) for t in toks])
    for table, hdr in TABLES.items():
        path = f"{out_dir}/acme-{table}.csv"
        with open(path, "w", newline="", encoding="utf-8") as f:
            w = csv.writer(f)
            w.writerow(hdr)
            w.writerows(rows[table])
        print(f"{path}: {len(rows[table])} rows")


if __name__ == "__main__":
    main(sys.argv[1], sys.argv[2])