#!/usr/bin/env python3
"""Parse Supabase/Postgres partnerships INSERT dump → MySQL + JSON for Laravel."""

from __future__ import annotations

import json
import re
from datetime import datetime
from pathlib import Path

ROOT = Path(__file__).resolve().parents[1]
SRC = ROOT / "data" / "supabase_partnerships_insert.sql"
OUT_MYSQL = ROOT / "data" / "mysql_import_partnerships.sql"
OUT_JSON = ROOT / "data" / "partnerships_for_artisan_import.json"
UNIFIED_WS = "b2fd8baf-45df-44b1-be67-d8d0d022e605"
UNIFIED_CREATOR = "00000000-0000-4000-8000-import-creator"


def parse_tuple_body(body: str) -> list[str | None | int]:
    """Parse comma-separated SQL values (null, 'quoted', integers)."""
    vals: list[str | None | int] = []
    i = 0
    n = len(body)
    while i < n:
        while i < n and body[i] in " \t\n\r,":
            i += 1
        if i >= n:
            break
        if body[i : i + 4] == "null" and (
            i + 4 == n or not (body[i + 4].isalnum() or body[i + 4] == "_")
        ):
            vals.append(None)
            i += 4
            continue
        if body[i] == "'":
            i += 1
            buf: list[str] = []
            while i < n:
                if body[i] == "'" and i + 1 < n and body[i + 1] == "'":
                    buf.append("'")
                    i += 2
                    continue
                if body[i] == "'":
                    i += 1
                    break
                buf.append(body[i])
                i += 1
            vals.append("".join(buf))
            continue
        j = i
        while j < n and body[j] not in ", \t\n\r":
            j += 1
        token = body[i:j].strip()
        i = j
        if token.isdigit() or (token.startswith("-") and token[1:].isdigit()):
            vals.append(int(token))
        else:
            raise ValueError(f"Unexpected token near {i}: {token!r}")
    return vals


def split_rows(inner: str) -> list[str]:
    """Split VALUES body `(r1), (r2), ...` into tuple bodies (comma-separated values, no outer parens)."""
    parts = inner.split("), (")
    bodies: list[str] = []
    for idx, p in enumerate(parts):
        body = p.strip()
        if idx == 0 and body.startswith("("):
            body = body[1:]
        if idx == len(parts) - 1 and body.endswith(")"):
            body = body[:-1]
        bodies.append(body)
    return bodies


def sql_str(s: str) -> str:
    return "'" + s.replace("\\", "\\\\").replace("'", "''") + "'"


def sql_ts(s: str) -> str:
    s = s.replace("+00", "").strip()
    if "." in s:
        s = s.split(".")[0]
    return sql_str(s)


def main() -> None:
    raw = SRC.read_text(encoding="utf-8")
    m = re.search(r"VALUES\s*(.+)\s*;\s*$", raw, re.S)
    if not m:
        raise SystemExit("Could not find VALUES clause")
    inner = m.group(1).strip()

    tuple_bodies = split_rows(inner)
    rows_out: list[dict] = []

    mysql_lines: list[str] = [
        "-- Partnerships migrated from Supabase/Postgres.",
        "-- Assumes workspace id = 1 and influencer user id = 2 (spies295@gmail.com).",
        "-- Run after users/workspaces/members are seeded.",
        "SET NAMES utf8mb4;",
        "",
        "USE `novares_collabs-app`;",
        "",
        "INSERT INTO `partnerships` (",
        "  `legacy_uuid`, `workspace_id`, `restaurant_name`, `instagram_handle`, `instagram_url`,",
        "  `status`, `scheduled_date`, `scheduled_time`, `inclusions`, `guests`, `notes`, `location`, `post_url`,",
        "  `created_by`, `managed_by_restaurant`, `created_at`, `updated_at`",
        ") VALUES",
    ]

    value_lines: list[str] = []

    for body in tuple_bodies:
        v = parse_tuple_body(body)
        if len(v) != 17:
            raise SystemExit(f"Expected 17 columns, got {len(v)}: {v[:5]}...")

        (
            legacy_id,
            _old_ws,
            restaurant,
            ig_handle,
            ig_url,
            status,
            sched_date,
            sched_time,
            inclusions,
            guests,
            notes,
            location,
            post_url,
            _old_creator,
            _managed_rest,
            created_at,
            updated_at,
        ) = v

        if inclusions is None or inclusions == "":
            inc_sql = "NULL"
        else:
            inc_sql = f"CAST({sql_str(inclusions)} AS JSON)"

        def lit(x: str | None) -> str:
            return "NULL" if x is None else sql_str(x)

        def dlit(x: str | None) -> str:
            return "NULL" if x is None else sql_str(str(x))

        glit = "NULL" if guests is None else str(int(guests))

        line = (
            "("
            f"{lit(str(legacy_id))}, 1, {lit(str(restaurant))}, {lit(ig_handle)}, {lit(ig_url)}, "
            f"{lit(str(status))}, {dlit(sched_date)}, {dlit(sched_time)}, "
            f"{inc_sql}, {glit}, {lit(notes)}, {lit(location)}, {lit(post_url)}, "
            f"2, NULL, {sql_ts(str(created_at))}, {sql_ts(str(updated_at))}"
            ")"
        )
        value_lines.append(line)

        rows_out.append(
            {
                "id": str(legacy_id),
                "workspace_id": UNIFIED_WS,
                "restaurant_name": str(restaurant),
                "instagram_handle": ig_handle,
                "instagram_url": ig_url,
                "status": str(status),
                "scheduled_date": sched_date,
                "scheduled_time": sched_time,
                # String form so `collabs:import-partnerships` decodeInclusions() parses it (objects in JSON become stdClass otherwise).
                "inclusions": inclusions,
                "guests": guests,
                "notes": notes,
                "location": location,
                "post_url": post_url,
                "created_by": UNIFIED_CREATOR,
                "managed_by_restaurant": _managed_rest,
                "created_at": str(created_at).replace("+00:00", "").replace("+00", ""),
                "updated_at": str(updated_at).replace("+00:00", "").replace("+00", ""),
            }
        )

    for i, vl in enumerate(value_lines):
        suffix = "," if i < len(value_lines) - 1 else ";"
        mysql_lines.append("  " + vl + suffix)

    OUT_MYSQL.write_text("\n".join(mysql_lines) + "\n", encoding="utf-8")

    OUT_JSON.write_text(json.dumps(rows_out, ensure_ascii=False, indent=2) + "\n", encoding="utf-8")

    print(f"Wrote {len(rows_out)} rows → {OUT_MYSQL}")
    print(f"Wrote {OUT_JSON}")


if __name__ == "__main__":
    main()