#!/usr/bin/env node
/**
 * Export Supabase tables → JSON files.
 *
 * Usage:
 *   node server/scripts/export-from-supabase.cjs
 *
 * Environment:
 *   SUPABASE_URL                 — Supabase project URL (default: read from ../.env)
 *   SUPABASE_SERVICE_ROLE_KEY    — service role key (REQUIRED to read RLS-protected tables like `profiles`)
 *   SUPABASE_ANON_KEY            — fallback key if service role is not provided
 *
 * Output:
 *   server/scripts/exports/<table>.json   — raw JSON dump
 *   server/scripts/exports/manifest.json  — list of tables, row counts, timestamps
 *
 * All tables are fetched in pages of 1000 rows each (Supabase's default max).
 */

const fs = require('fs');
const path = require('path');
const { createClient } = require('@supabase/supabase-js');

// ─── Config ─────────────────────────────────────────────

// Try to load .env from the project root (two levels up)
const envPath = path.join(__dirname, '..', '..', '.env');
if (fs.existsSync(envPath)) {
  for (const line of fs.readFileSync(envPath, 'utf-8').split('\n')) {
    const m = line.match(/^\s*([A-Z_]+)\s*=\s*"?([^"\n]*?)"?\s*$/);
    if (m && !process.env[m[1]]) process.env[m[1]] = m[2];
  }
}

const SUPABASE_URL =
  process.env.SUPABASE_URL ||
  process.env.VITE_SUPABASE_URL ||
  'https://jekjcnekyqgiyxiqznju.supabase.co';

const SUPABASE_KEY =
  process.env.SUPABASE_SERVICE_ROLE_KEY ||
  process.env.SUPABASE_ANON_KEY ||
  process.env.VITE_SUPABASE_PUBLISHABLE_KEY;

if (!SUPABASE_KEY) {
  console.error('✗ Missing SUPABASE_SERVICE_ROLE_KEY (or anon key). Aborting.');
  process.exit(1);
}

const usingServiceRole = !!process.env.SUPABASE_SERVICE_ROLE_KEY;
console.log(`→ Using ${usingServiceRole ? 'SERVICE ROLE' : 'ANON'} key`);
if (!usingServiceRole) {
  console.warn('⚠  Anon key cannot read RLS-protected tables like `profiles`.');
  console.warn('⚠  Set SUPABASE_SERVICE_ROLE_KEY in your environment for a full export.');
}

const supabase = createClient(SUPABASE_URL, SUPABASE_KEY, {
  auth: { persistSession: false },
});

// Tables to export. Add/remove as needed.
// These are the most important ones — the full list of 60+ tables is overkill.
const TABLES = [
  // ── Users & orgs (highest priority) ──
  'profiles',
  'companies',
  'user_permissions',
  'user_settings',

  // ── Support / CS ──
  'customer_requests',
  'messages',
  'notifications',

  // ── Bookings ──
  'bookings',
  'booking_contacts',
  'booking_flights',
  'booking_hotels',
  'booking_passengers',
  'booking_products',
  'booking_payments',
  'booking_payment_lines',
  'booking_flight_segments',
  'booking_notes',
  'booking_reviews',
  'api_data_capture',

  // ── Emails ──
  'incoming_emails',
  'email_messages',
  'email_conversations',
  'email_queue',
  'email_templates',
  'email_tracking',
  'email_logs',
  'email_inbox_config',
  'email_parsing_rules',

  // ── Config ──
  'smtp_config',
  'system_settings',
  'supplier_configs',

  // ── Activity ──
  'activity_views',
  'documents',
];

const OUT_DIR = path.join(__dirname, 'exports');
if (!fs.existsSync(OUT_DIR)) fs.mkdirSync(OUT_DIR, { recursive: true });

// ─── Fetch helpers ──────────────────────────────────────

async function fetchTable(table) {
  const all = [];
  const PAGE = 1000;
  let from = 0;

  while (true) {
    const { data, error, count } = await supabase
      .from(table)
      .select('*', { count: from === 0 ? 'exact' : undefined })
      .range(from, from + PAGE - 1);

    if (error) {
      throw new Error(`${table}: ${error.message}`);
    }
    if (!data || data.length === 0) break;
    all.push(...data);
    if (data.length < PAGE) break;
    from += PAGE;
    process.stdout.write(`    …${all.length}`);
  }
  return all;
}

// ─── Main ───────────────────────────────────────────────

(async () => {
  const manifest = {
    exported_at: new Date().toISOString(),
    source: SUPABASE_URL,
    used_service_role: usingServiceRole,
    tables: {},
  };

  let ok = 0;
  let failed = 0;

  for (const table of TABLES) {
    process.stdout.write(`→ ${table.padEnd(28)} `);
    try {
      const rows = await fetchTable(table);
      const outPath = path.join(OUT_DIR, `${table}.json`);
      fs.writeFileSync(outPath, JSON.stringify(rows, null, 2));
      manifest.tables[table] = { rows: rows.length, file: `${table}.json` };
      console.log(`✓ ${rows.length} rows`);
      ok++;
    } catch (e) {
      manifest.tables[table] = { error: e.message };
      console.log(`✗ ${e.message}`);
      failed++;
    }
  }

  fs.writeFileSync(
    path.join(OUT_DIR, 'manifest.json'),
    JSON.stringify(manifest, null, 2)
  );

  console.log(`\n✓ Done — ${ok} tables exported, ${failed} failed`);
  console.log(`  Output: ${OUT_DIR}`);
  console.log(`  Manifest: ${path.join(OUT_DIR, 'manifest.json')}`);

  if (manifest.tables.profiles?.error) {
    console.log('\n⚠  `profiles` failed. This usually means you need to set SUPABASE_SERVICE_ROLE_KEY:');
    console.log('   1. Go to https://supabase.com/dashboard/project/<your-project>/settings/api');
    console.log('   2. Copy the `service_role` secret (NOT the anon key)');
    console.log('   3. Re-run:  SUPABASE_SERVICE_ROLE_KEY="..." node server/scripts/export-from-supabase.cjs');
  }
})();
