#!/usr/bin/env python3
"""disc_sync.py — mirror the WowPlatter Discogs metadata clone into RecordGod (Postgres).

Reads the MariaDB clone (the ~30.8k releases the shop stocks) and emits a Postgres load
(schema + COPY data) on stdout. Pipe it into recordgod-db:

  python disc_sync.py | gzip | ssh root@<vps> 'gunzip | docker exec -i recordgod-db psql -U recordgod -d recordgod -q'

Convention (per John, 2026-06-22):
  • genre/style ids are WowPlatter's OWN incremental ids — Discogs has no genre/style tables.
    They're copied FAITHFULLY because the collections (filing rules) reference them. Names ride along
    for display only. (Collections store them as "ID,0" strings — the LOCATOR parses that, not this.)
  • release_id / artist_id / label_id / master_id ARE real Discogs ids — the stable join keys.
  • Augmented WowPlatter columns (beatport/apple/wiki/etc.) are dropped here; we keep what RecordGod
    needs for locator + navigator + display + barcode. Images come from DealGod (not stored here).
  • Masters are barely populated in WowPlatter (72) — copied as-is; backfill from the Ultra later.
"""
import pathlib
import re
import sys

import pymysql

WP_CONFIG = "/opt/homebrew/var/www/monsterrobot.localsite/wp-config.php"
SOCK = "/opt/homebrew/var/mysql/mysql.sock"

SCHEMA = """
SET synchronous_commit = off;
DROP TABLE IF EXISTS disc_release, disc_release_genre, disc_release_style, disc_release_label,
  disc_release_format, disc_release_identifier, disc_release_artist, disc_release_track,
  disc_genre, disc_style, disc_label, disc_artist, disc_master, disc_master_genre, disc_master_style CASCADE;
CREATE TABLE disc_genre  (id int PRIMARY KEY, name text);
CREATE TABLE disc_style  (id int PRIMARY KEY, parent_genre_id int, name text);
CREATE TABLE disc_label  (id bigint PRIMARY KEY, name text, profile text);
CREATE TABLE disc_artist (id bigint PRIMARY KEY, name text, realname text);
CREATE TABLE disc_master (id bigint PRIMARY KEY, title text, year int, main_release bigint);
CREATE TABLE disc_release (
  id bigint PRIMARY KEY, title text, artists_sort text, released text, country text, year int,
  notes text, format_quantity int, description text, apple_id text, estimated_weight numeric,
  master_id bigint, thumb text, rating_count int, rating_average numeric, search_text text,
  genre_ids int[], style_ids int[]);
CREATE TABLE disc_release_genre  (release_id bigint, genre_id int, genre_name text);
CREATE TABLE disc_release_style  (release_id bigint, style_id int, style_name text);
CREATE TABLE disc_release_label  (release_id bigint, label_id bigint, label_name text, catno text);
CREATE TABLE disc_release_format (release_id bigint, name text, qty int, descriptions text);
CREATE TABLE disc_release_identifier (release_id bigint, type text, value text);
CREATE TABLE disc_release_artist (release_id bigint, artist_id bigint, artist_name text, role text, join_string text, position text);
CREATE TABLE disc_master_genre (master_id bigint, genre_id int, genre_name text);
CREATE TABLE disc_master_style (master_id bigint, style_id int, style_name text);
CREATE TABLE disc_release_track (release_id bigint, sequence int, position text, title text, duration text, track_id text, apple_id text, apple_preview text, bpm numeric, musical_key text);
"""

INDEXES = """
CREATE INDEX ON disc_release USING gin (genre_ids);
CREATE INDEX ON disc_release USING gin (style_ids);
CREATE INDEX ON disc_release (master_id);
CREATE INDEX ON disc_release_genre (release_id);
CREATE INDEX ON disc_release_style (release_id);
CREATE INDEX ON disc_release_label (release_id);
CREATE INDEX ON disc_release_identifier (value);
CREATE INDEX ON disc_release_artist (release_id);
CREATE INDEX ON disc_release_artist (artist_id);
CREATE INDEX ON disc_release_track (release_id);
ANALYZE disc_release;
"""


def creds():
    t = pathlib.Path(WP_CONFIG).read_text()
    g = lambda k: re.search(rf"'{k}',\s*'([^']*)'", t).group(1)
    return g("DB_NAME"), g("DB_USER"), g("DB_PASSWORD")


def cp(v):
    """Format one value for COPY text format."""
    if v is None or v == "":
        return r"\N"
    return (str(v).replace("\\", "\\\\").replace("\t", "\\t")
            .replace("\n", "\\n").replace("\r", "\\r"))


def iarr(ids):
    """int[] COPY literal — {2,6} — or NULL. Skips any null ids in the list."""
    vals = [str(int(i)) for i in (ids or []) if i is not None]
    return "{" + ",".join(vals) + "}" if vals else r"\N"


def copy_block(out, table, cols, rows, fmt):
    out.write(f"COPY {table} ({','.join(cols)}) FROM stdin;\n")
    for r in rows:
        out.write("\t".join(fmt(r)) + "\n")
    out.write("\\.\n\n")
    print(f"  {table:26} {len(rows):>7,}", file=sys.stderr)


def main():
    name, user, pw = creds()
    my = pymysql.connect(unix_socket=SOCK, user=user, password=pw, database=name,
                         cursorclass=pymysql.cursors.DictCursor)
    c = my.cursor()
    out = sys.stdout
    out.write(SCHEMA)
    P = "wp_rmp_disc_"
    print("emitting:", file=sys.stderr)

    # genre / style lookups (WowPlatter's own ids)
    c.execute(f"SELECT id, name FROM {P}genre")
    copy_block(out, "disc_genre", ["id", "name"], c.fetchall(), lambda r: [cp(r["id"]), cp(r["name"])])
    c.execute(f"SELECT id, parent_genre_id, name FROM {P}style")
    copy_block(out, "disc_style", ["id", "parent_genre_id", "name"], c.fetchall(),
               lambda r: [cp(r["id"]), cp(r["parent_genre_id"]), cp(r["name"])])

    # per-release genre/style id aggregation (for the fast-match arrays on disc_release)
    rg, rs = {}, {}
    c.execute(f"SELECT release_id, genre_id FROM {P}release_genre")
    for r in c.fetchall():
        rg.setdefault(r["release_id"], []).append(r["genre_id"])
    c.execute(f"SELECT release_id, style_id FROM {P}release_style")
    for r in c.fetchall():
        rs.setdefault(r["release_id"], []).append(r["style_id"])

    # releases
    c.execute(f"""SELECT id, title, artists_sort, released, country, year, notes, format_quantity,
                  description, apple_id, estimated_weight, master_id, thumb, rating_count,
                  rating_average, search_text FROM {P}release""")
    cols = ["id", "title", "artists_sort", "released", "country", "year", "notes", "format_quantity",
            "description", "apple_id", "estimated_weight", "master_id", "thumb", "rating_count",
            "rating_average", "search_text", "genre_ids", "style_ids"]
    copy_block(out, "disc_release", cols, c.fetchall(),
               lambda r: [cp(r[k]) for k in cols[:-2]] + [iarr(rg.get(r["id"])), iarr(rs.get(r["id"]))])

    # join + lookup tables
    c.execute(f"SELECT release_id, genre_id, genre_name FROM {P}release_genre")
    copy_block(out, "disc_release_genre", ["release_id", "genre_id", "genre_name"], c.fetchall(),
               lambda r: [cp(r["release_id"]), cp(r["genre_id"]), cp(r["genre_name"])])
    c.execute(f"SELECT release_id, style_id, style_name FROM {P}release_style")
    copy_block(out, "disc_release_style", ["release_id", "style_id", "style_name"], c.fetchall(),
               lambda r: [cp(r["release_id"]), cp(r["style_id"]), cp(r["style_name"])])
    c.execute(f"SELECT release_id, label_id, label_name, catno FROM {P}release_label")
    copy_block(out, "disc_release_label", ["release_id", "label_id", "label_name", "catno"], c.fetchall(),
               lambda r: [cp(r["release_id"]), cp(r["label_id"]), cp(r["label_name"]), cp(r["catno"])])
    c.execute(f"SELECT id, name, profile FROM {P}label")
    copy_block(out, "disc_label", ["id", "name", "profile"], c.fetchall(),
               lambda r: [cp(r["id"]), cp(r["name"]), cp(r["profile"])])
    c.execute(f"SELECT release_id, name, qty, descriptions FROM {P}release_format")
    copy_block(out, "disc_release_format", ["release_id", "name", "qty", "descriptions"], c.fetchall(),
               lambda r: [cp(r["release_id"]), cp(r["name"]), cp(r["qty"]), cp(r["descriptions"])])
    c.execute(f"SELECT release_id, type, value FROM {P}release_identifier")
    copy_block(out, "disc_release_identifier", ["release_id", "type", "value"], c.fetchall(),
               lambda r: [cp(r["release_id"]), cp(r["type"]), cp(r["value"])])
    c.execute(f"SELECT release_id, artist_id, artist_name, role, join_string, position FROM {P}release_artist")
    copy_block(out, "disc_release_artist",
               ["release_id", "artist_id", "artist_name", "role", "join_string", "position"], c.fetchall(),
               lambda r: [cp(r["release_id"]), cp(r["artist_id"]), cp(r["artist_name"]),
                          cp(r["role"]), cp(r["join_string"]), cp(r["position"])])
    # only artists referenced by our releases (the full artist table is 226k)
    c.execute(f"SELECT id, name, realname FROM {P}artist WHERE id IN (SELECT DISTINCT artist_id FROM {P}release_artist)")
    copy_block(out, "disc_artist", ["id", "name", "realname"], c.fetchall(),
               lambda r: [cp(r["id"]), cp(r["name"]), cp(r["realname"])])
    c.execute(f"SELECT release_id, sequence, position, title, duration, track_id, apple_id, bpm, musical_key FROM {P}release_track")
    copy_block(out, "disc_release_track",
               ["release_id", "sequence", "position", "title", "duration", "track_id", "apple_id", "bpm", "musical_key"],
               c.fetchall(),
               lambda r: [cp(r["release_id"]), cp(r["sequence"]), cp(r["position"]), cp(r["title"]),
                          cp(r["duration"]), cp(r["track_id"]), cp(r["apple_id"]), cp(r["bpm"]), cp(r["musical_key"])])
    # masters (sparse — copy what exists)
    c.execute(f"SELECT id, title, year, main_release FROM {P}master")
    copy_block(out, "disc_master", ["id", "title", "year", "main_release"], c.fetchall(),
               lambda r: [cp(r["id"]), cp(r["title"]), cp(r["year"]), cp(r["main_release"])])
    c.execute(f"SELECT master_id, genre_id, genre_name FROM {P}master_genre")
    copy_block(out, "disc_master_genre", ["master_id", "genre_id", "genre_name"], c.fetchall(),
               lambda r: [cp(r["master_id"]), cp(r["genre_id"]), cp(r["genre_name"])])
    c.execute(f"SELECT master_id, style_id, style_name FROM {P}master_style")
    copy_block(out, "disc_master_style", ["master_id", "style_id", "style_name"], c.fetchall(),
               lambda r: [cp(r["master_id"]), cp(r["style_id"]), cp(r["style_name"])])

    out.write(INDEXES)
    print("done.", file=sys.stderr)


if __name__ == "__main__":
    main()
