#!/bin/bash
# =============================================================================
# extract_schema.sh — Extrae schema real de la BD, un fichero por objeto
# Uso: PGPASSWORD='pass' ./database/extract_schema.sh host port user dbname
# =============================================================================

set -e

DB_HOST="${1:?Uso: PGPASSWORD='pass' $0 host port user dbname}"
DB_PORT="${2:?Falta puerto}"
DB_USER="${3:?Falta usuario}"
DB_NAME="${4:?Falta nombre de BD}"

BASE="$(cd "$(dirname "$0")" && pwd)"
mkdir -p "$BASE/schema"/{types,tables,views,functions,seed}
mkdir -p "$BASE/migrations/archive"

# Ejecuta una query y devuelve resultado limpio (sin cabeceras ni separadores)
q() { psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -A -c "$1"; }

echo "Conectando..."
q "SELECT version();" > /dev/null
echo "OK — $(q "SELECT current_database();")"
echo ""

# ─── DOMINIOS ─────────────────────────────────────────────────────────────────

echo "── Dominios ──"
q "SELECT typname FROM pg_type
   WHERE typtype = 'd' AND typnamespace = 'public'::regnamespace
   ORDER BY typname;" | while IFS= read -r name; do
  [ -z "$name" ] && continue
  q "SELECT
       'CREATE DOMAIN public.' || t.typname || ' AS ' ||
       pg_catalog.format_type(t.typbasetype, t.typtypmod) ||
       CASE WHEN t.typnotnull THEN E'\n    NOT NULL' ELSE '' END ||
       COALESCE(
         E'\n    CONSTRAINT ' || c.conname || ' ' || pg_get_constraintdef(c.oid),
         ''
       ) || ';'
     FROM pg_type t
     LEFT JOIN pg_constraint c ON c.contypid = t.oid
     WHERE t.typname = '$name'
       AND t.typnamespace = 'public'::regnamespace;" \
    > "$BASE/schema/types/${name}.sql"
  echo "  ✓ $name"
done

# ─── ENUMS ────────────────────────────────────────────────────────────────────

echo ""
echo "── Enums ──"
q "SELECT typname FROM pg_type
   WHERE typtype = 'e' AND typnamespace = 'public'::regnamespace
   ORDER BY typname;" | while IFS= read -r name; do
  [ -z "$name" ] && continue
  q "SELECT
       'DO \$\$ BEGIN' || chr(10) ||
       '  CREATE TYPE public.' || t.typname || ' AS ENUM (' ||
       string_agg(quote_literal(e.enumlabel), ', ' ORDER BY e.enumsortorder) ||
       ');' || chr(10) ||
       'EXCEPTION WHEN duplicate_object THEN NULL;' || chr(10) ||
       'END \$\$;'
     FROM pg_type t
     JOIN pg_enum e ON e.enumtypid = t.oid
     WHERE t.typname = '$name'
       AND t.typnamespace = 'public'::regnamespace
     GROUP BY t.typname;" \
    > "$BASE/schema/types/${name}.sql"
  echo "  ✓ $name"
done

# ─── TABLAS ───────────────────────────────────────────────────────────────────

echo ""
echo "── Tablas ──"
q "SELECT tablename FROM pg_tables
   WHERE schemaname = 'public'
   ORDER BY tablename;" | while IFS= read -r tbl; do
  [ -z "$tbl" ] && continue

  COLS=$(q "SELECT string_agg(
              '    ' || a.attname || ' ' ||
              pg_catalog.format_type(a.atttypid, a.atttypmod) ||
              CASE WHEN a.attnotnull THEN ' NOT NULL' ELSE '' END ||
              COALESCE(' DEFAULT ' || pg_get_expr(ad.adbin, ad.adrelid), ''),
              E',\n'
              ORDER BY a.attnum)
            FROM pg_attribute a
            LEFT JOIN pg_attrdef ad
              ON ad.adrelid = a.attrelid AND ad.adnum = a.attnum
            WHERE a.attrelid = 'public.$tbl'::regclass
              AND a.attnum > 0
              AND NOT a.attisdropped;")

  CONSTRAINTS=$(q "SELECT COALESCE(
                     string_agg(
                       E',\n    CONSTRAINT ' || c.conname || ' ' ||
                       pg_get_constraintdef(c.oid, true),
                       ''
                       ORDER BY c.contype, c.conname),
                     '')
                   FROM pg_constraint c
                   WHERE c.conrelid = 'public.$tbl'::regclass
                     AND c.contype IN ('p','u','f','c');")

  {
    echo "CREATE TABLE public.$tbl"
    echo "("
    printf '%s' "$COLS$CONSTRAINTS"
    echo ""
    echo ");"
  } > "$BASE/schema/tables/${tbl}.sql"

  echo "  ✓ $tbl"
done

# ─── VISTAS ───────────────────────────────────────────────────────────────────

echo ""
echo "── Vistas ──"
q "SELECT viewname FROM pg_views
   WHERE schemaname = 'public'
   ORDER BY viewname;" | while IFS= read -r name; do
  [ -z "$name" ] && continue
  q "SELECT
       'CREATE OR REPLACE VIEW public.$name AS' || chr(10) ||
       pg_get_viewdef('public.$name', true) || ';'" \
    > "$BASE/schema/views/${name}.sql"
  echo "  ✓ $name"
done

# ─── FUNCIONES ────────────────────────────────────────────────────────────────

echo ""
echo "── Funciones ──"
q "SELECT p.proname
   FROM pg_proc p
   JOIN pg_namespace n ON n.oid = p.pronamespace
   WHERE n.nspname = 'public' AND NOT p.proisagg
   ORDER BY p.proname;" | while IFS= read -r name; do
  [ -z "$name" ] && continue
  q "SELECT pg_get_functiondef(p.oid) || ';'
     FROM pg_proc p
     JOIN pg_namespace n ON n.oid = p.pronamespace
     WHERE p.proname = '$name' AND n.nspname = 'public'
     LIMIT 1;" \
    > "$BASE/schema/functions/${name}.sql"
  echo "  ✓ $name"
done

# ─── SEED ─────────────────────────────────────────────────────────────────────
# Usa jsonb_populate_record: lee columnas reales de la BD, no asume ninguna

echo ""
echo "── Seed data ──"

for tbl in courses levels registration_messages email_templates email_dispatch_rules promotion_reasons system_config; do
  PK=$(q "SELECT a.attname
          FROM pg_index i
          JOIN pg_attribute a ON a.attrelid = i.indrelid
            AND a.attnum = ANY(i.indkey)
          WHERE i.indrelid = 'public.$tbl'::regclass
            AND i.indisprimary
          ORDER BY a.attnum
          LIMIT 1;")
  [ -z "$PK" ] && PK="1"

  q "SELECT
       'INSERT INTO public.$tbl' || chr(10) ||
       'SELECT * FROM jsonb_populate_record(NULL::public.$tbl, ' ||
       quote_literal(to_jsonb(t)::text) ||
       ') ON CONFLICT DO NOTHING;'
     FROM public.$tbl t
     ORDER BY $PK;" \
    > "$BASE/schema/seed/${tbl}.sql"
  echo "  ✓ $tbl"
done

# ─── ARCHIVAR MIGRACIONES ─────────────────────────────────────────────────────

echo ""
echo "── Archivando migraciones ──"
if ls "$BASE/migrations/"*.sql 1>/dev/null 2>&1; then
  mv "$BASE/migrations/"*.sql "$BASE/migrations/archive/"
  echo "  ✓ migraciones → migrations/archive/"
else
  echo "  (ya archivadas)"
fi

# ─── GRANTS ───────────────────────────────────────────────────────────────────
# Generates grants.sql for the app user (elfi1953_stc-eu) on all tables,
# sequences, and functions in the public schema.

echo ""
echo "── Grants ──"
{
  echo "-- Grants for app user on all current tables, sequences and functions"
  echo "-- Generated by extract_schema.sh — re-run after each migration"
  echo ""

  # Tables: SELECT, INSERT, UPDATE, DELETE
  q "SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE ON public.' || tablename ||
            ' TO \"elfi1953_stc-eu\";'
     FROM pg_tables
     WHERE schemaname = 'public'
     ORDER BY tablename;"

  echo ""

  # Sequences: USAGE, SELECT
  q "SELECT 'GRANT USAGE, SELECT ON SEQUENCE public.' || sequence_name ||
            ' TO \"elfi1953_stc-eu\";'
     FROM information_schema.sequences
     WHERE sequence_schema = 'public'
     ORDER BY sequence_name;"

  echo ""

  # Functions: EXECUTE
  q "SELECT 'GRANT EXECUTE ON FUNCTION public.' || p.proname ||
            '(' || pg_get_function_identity_arguments(p.oid) || ')' ||
            ' TO \"elfi1953_stc-eu\";'
     FROM pg_proc p
     JOIN pg_namespace n ON n.oid = p.pronamespace
     WHERE n.nspname = 'public' AND NOT p.proisagg
     ORDER BY p.proname;"

} > "$BASE/schema/grants.sql"
echo "  ✓ grants.sql"

echo ""
echo "✓ Schema extraído en database/schema/"