#!/bin/zsh
#
# Seed the shared master catalogue schema (master_projects) from a petav3
# production dump — catalogue family ONLY.
#
#   docs/planning/master-catalogue-shared-database-plan.md §9.5 step 5
#
# This is the one legitimate bulk copy: the initial load of an EMPTY master.
# Every later refresh is an identity-keyed upsert (§6a), never a truncate.
#
# Ids and uuids are preserved verbatim, which is what lets petav3's own site
# tables keep their existing catalog_project_id / catalog_floor_plan_id values
# with no remap (§9.2).
#
# Usage:
#   MYSQL_PWD='<master_projects password>' scripts/seed-master-catalogue.sh
#   MYSQL_PWD='...' scripts/seed-master-catalogue.sh /path/to/other-dump.sql
#
# The password is the one commented out in .env under "# Remote master".
# It is passed via MYSQL_PWD so it never lands in the process list.
#
set -e

DUMP="${1:-/Users/yongzhi/petav3_34_87_149_195-2026_08_18_16_01_10-dump.sql}"
MYSQL_BIN="${MYSQL_BIN:-/opt/homebrew/bin/mysql}"
HOST="${MASTER_HOST:-34.87.149.195}"
USER="${MASTER_USER:-master_projects}"
DB="${MASTER_DB:-master_projects}"

# The catalogue family, per §9.1. countries + data_providers travel too: the
# canonical rows carry integer FKs into them, and a per-deployment seed order
# would silently diverge those ids. `media` carries the catalogue-owned
# uploads that catalog_media.media_id references.
TABLES="catalog_projects,catalog_project_sources,catalog_floor_plans,catalog_floor_plan_sources,catalog_floor_plan_analytics,catalog_media,catalog_ai_contents,catalog_project_developers,developers,developer_relationships,catalog_buildings,catalog_building_sources,catalog_units,catalog_unit_sources,catalog_unit_valuations,catalog_doc_pages,catalog_identity_locks,catalog_sync_runs,market_transactions,market_schools,market_area_profiles,market_area_benchmarks,market_airbnbs,market_price_indices,market_catalysts,market_property_agents,countries,data_providers,media"

if [ ! -f "$DUMP" ]; then
    echo "Dump not found: $DUMP" >&2
    exit 1
fi

if [ -z "$MYSQL_PWD" ]; then
    echo "Set MYSQL_PWD to the ${USER} password (see the commented block in .env)." >&2
    exit 1
fi

echo "Seeding ${USER}@${HOST}/${DB} from $(basename "$DUMP")"
echo "Tables: $(echo "$TABLES" | tr ',' '\n' | wc -l | tr -d ' ') of the catalogue family"
echo "This streams ~2.6 GB and takes a few minutes."

# Filter the dump to the catalogue family. GTID_PURGED / SQL_LOG_BIN lines are
# dropped: they need privileges this user does not have (and must not have),
# and the target has gtid_mode OFF.
awk -v tables="$TABLES" '
BEGIN { n = split(tables, a, ","); for (i = 1; i <= n; i++) keep[a[i]] = 1; inhdr = 1; use = 0 }
/^-- Table structure for table `/ {
    inhdr = 0
    name = $0
    sub(/^-- Table structure for table `/, "", name)
    sub(/`.*/, "", name)
    use = (name in keep)
}
/GTID_PURGED|SQL_LOG_BIN/ { next }
{ if (inhdr || use) print }
' "$DUMP" | "$MYSQL_BIN" --connect-timeout=20 -h"$HOST" -u"$USER" "$DB"

# The seed drops and recreates the catalogue tables but NOT `migrations`, so
# every catalogue migration stays recorded as run while its effect has just
# been wiped — `migrate` then reports "Nothing to migrate" against a schema
# that is missing its columns. Clear those records so they re-apply.
"$MYSQL_BIN" --connect-timeout=20 -h"$HOST" -u"$USER" "$DB" -e \
    "delete from migrations where migration like '2026_08_18_%';" 2>/dev/null || true

echo "Catalogue migration records cleared — re-run:"
echo "  php artisan migrate --path=database/migrations/catalogue --database=catalogue"
echo
echo "Seed complete. Verifying:"
"$MYSQL_BIN" --connect-timeout=20 -h"$HOST" -u"$USER" "$DB" -e "
select
  (select count(*) from catalog_projects)     as projects,
  (select count(*) from catalog_floor_plans)  as floor_plans,
  (select count(*) from catalog_media)        as media_rows,
  (select count(*) from developers)           as developers,
  (select count(*) from market_transactions)  as market_txn,
  (select count(*) from countries)            as countries,
  (select count(*) from data_providers)       as providers;"

echo
echo "Expected (from the prod snapshot loaded locally on 2026-08-18):"
echo "  projects 37688 | floor_plans 37858 | media_rows 64780 | developers 4193 | market_txn 2346941"
echo "  (+951 once the Dubai/AE catalogue is in the dump: countries must then"
echo "   show 3 — MY, HK, AE — and data_providers must include 'propertyfinder')"
echo
echo "Publication does NOT survive a re-seed (published_at is NULL in the prod"
echo "dump). After migrate, re-publish the parity markets:"
echo "  php artisan catalogue:hk-publish-parity"
echo "  php artisan catalogue:ae-publish-parity"
echo
echo "Verify pages: /my/new-projects /hk/new-projects /ae/new-projects"
echo
echo "Next: flip the MASTER_DB_* block in .env to the commented remote values."