#!/usr/bin/env python3
"""
Build a single mailing list CSV from users_may.sql.

Literal export — every row, single file.
Output: users_may.csv  (email, first_name, last_name, unsubscribed)
"""

import csv
import sys
from pathlib import Path

sys.path.insert(0, "/Users/tomassimkus/VS/marketing_tools")
from build_marketing_table import parse_sql_values

BASE = Path("/Users/tomassimkus/VS/marketing_tools")

USERS_COLS = [
    "id", "group_id", "username", "password", "last_password_change",
    "pass_bcrypt", "email", "email_verified", "email_verification",
    "two_factor_enabled", "totp_secret", "totp_enabled",
    "two_factor_type", "title", "realname", "url", "facebook",
    "twitter", "msn", "google", "location", "country", "signature",
    "disp_topics", "disp_posts", "email_setting", "notify_with_post",
    "notify_pm_full", "auto_notify", "show_smilies", "show_img",
    "show_img_sig", "show_avatars", "show_sig", "timezone", "dst",
    "time_format", "date_format", "language", "style", "backstage_color",
    "num_posts", "num_pms", "last_post", "last_search",
    "last_email_sent", "last_report_sent", "registered",
    "registration_ip", "last_visit", "admin_note", "activate_string",
    "activate_key", "use_pm", "notify_pm", "tokens", "sub_expires",
    "first_run", "accent", "set_avatar", "receive_newsletters",
    "owner_id", "owner_set_by", "owner_set_at", "owner_set_reason",
]


def main():
    text = (BASE / "users_may.sql").read_text(encoding="utf-8", errors="replace")
    rows = list(parse_sql_values(text))

    out_rows = []
    for r in rows:
        if len(r) != len(USERS_COLS):
            continue
        u = dict(zip(USERS_COLS, r))
        out_rows.append({
            "email": (u.get("email") or "").strip(),
            "first_name": (u.get("username") or "").strip(),
            "last_name": "",
            "unsubscribed": "false",
        })

    cols = ["email", "first_name", "last_name", "unsubscribed"]
    path = BASE / "users_may.csv"
    with path.open("w", newline="", encoding="utf-8") as f:
        writer = csv.DictWriter(f, fieldnames=cols)
        writer.writeheader()
        writer.writerows(out_rows)

    print(f"  {len(out_rows):,} rows -> {path.name}")


if __name__ == "__main__":
    main()