#!/usr/bin/env node
/**
 * Import exported Supabase JSON files → MySQL.
 *
 * Usage:
 *   node server/scripts/import-to-mysql.cjs [--only=profiles,companies]
 *
 * Prereqs:
 *   1. Run server/scripts/export-from-supabase.cjs first to populate
 *      server/scripts/exports/*.json.
 *   2. Set DB_HOST, DB_USER, DB_PASSWORD, DB_NAME in the env (or .env at project root).
 *
 * What it does:
 *   • profiles      → platform_users  (field-mapped, with random temp passwords)
 *   • companies     → supabase_companies  (1:1 import, new table)
 *   • other tables  → supabase_<table>    (archived as-is for later use)
 *
 * Passwords:
 *   Supabase stores passwords in `auth.users` which is NOT exported. This script
 *   assigns each imported user a random temporary password and prints them to
 *   the console. Share these with users (or have them reset their password).
 */

const fs = require('fs');
const path = require('path');
const crypto = require('crypto');
const mysql = require('mysql2/promise');
const bcrypt = require('bcryptjs');

// Load .env from project root
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];
  }
}

// CLI args
const args = process.argv.slice(2);
const onlyArg = args.find(a => a.startsWith('--only='));
const only = onlyArg ? onlyArg.split('=')[1].split(',') : null;

const EXPORT_DIR = path.join(__dirname, 'exports');
if (!fs.existsSync(EXPORT_DIR)) {
  console.error(`✗ ${EXPORT_DIR} not found. Run export-from-supabase.cjs first.`);
  process.exit(1);
}

function loadJson(name) {
  const p = path.join(EXPORT_DIR, `${name}.json`);
  if (!fs.existsSync(p)) return null;
  try { return JSON.parse(fs.readFileSync(p, 'utf-8')); }
  catch (e) { console.warn(`  ⚠ Could not parse ${name}.json: ${e.message}`); return null; }
}

function randomPassword(len = 12) {
  return crypto.randomBytes(len).toString('base64').replace(/[+/=]/g, '').slice(0, len);
}

// ─── MySQL connection ───────────────────────────────────

async function connect() {
  return mysql.createConnection({
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'tripovo_support',
    port: Number(process.env.DB_PORT || 3306),
    multipleStatements: true,
  });
}

// ─── profiles → platform_users ──────────────────────────

async function importProfiles(db) {
  const profiles = loadJson('profiles');
  if (!profiles || profiles.length === 0) {
    console.log('  (no profiles to import)');
    return;
  }

  // Make sure platform_users exists
  await db.query(`
    CREATE TABLE IF NOT EXISTS platform_users (
      id VARCHAR(64) PRIMARY KEY,
      email VARCHAR(255) UNIQUE NOT NULL,
      password_hash VARCHAR(255) NOT NULL,
      first_name VARCHAR(100),
      last_name VARCHAR(100),
      role VARCHAR(50) DEFAULT 'agent',
      is_active BOOLEAN DEFAULT TRUE,
      allowed_app_ids JSON NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      last_login TIMESTAMP NULL
    )
  `);

  // Check for existing emails to avoid duplicates
  const [existingRows] = await db.query('SELECT email FROM platform_users');
  const existing = new Set(existingRows.map(r => r.email.toLowerCase()));

  const credentials = [];
  let inserted = 0;
  let skipped = 0;

  for (const p of profiles) {
    if (!p.email) { skipped++; continue; }
    if (existing.has(p.email.toLowerCase())) { skipped++; continue; }

    const tempPwd = randomPassword(10);
    const hash = await bcrypt.hash(tempPwd, 10);

    const role = ['admin', 'manager', 'agent', 'customer_service'].includes(p.role) ? p.role : 'agent';
    const id = p.id || crypto.randomUUID();

    try {
      await db.query(
        `INSERT INTO platform_users (id, email, password_hash, first_name, last_name, role, is_active, created_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          id,
          p.email,
          hash,
          p.first_name || '',
          p.last_name || '',
          role,
          p.is_active !== false && p.status !== 'rejected',
          p.created_at ? new Date(p.created_at) : new Date(),
        ]
      );
      credentials.push({ email: p.email, temp_password: tempPwd, role });
      inserted++;
    } catch (e) {
      console.warn(`  ⚠ ${p.email}: ${e.message}`);
      skipped++;
    }
  }

  console.log(`  ✓ platform_users: ${inserted} inserted, ${skipped} skipped`);

  if (credentials.length > 0) {
    const credPath = path.join(EXPORT_DIR, 'imported-user-credentials.json');
    fs.writeFileSync(credPath, JSON.stringify(credentials, null, 2));
    console.log(`  → Temp passwords written to: ${credPath}`);
  }
}

// ─── Generic archive: supabase_<table> ──────────────────
//
// For every other table, create a "supabase_<name>" table with a single JSON
// column and drop each row in as-is. This preserves the data so you can
// extract/transform it later without needing access to Supabase.

async function archiveTable(db, name) {
  const rows = loadJson(name);
  if (!rows || rows.length === 0) return 0;

  const tableName = `supabase_${name}`.slice(0, 60);
  await db.query(`
    CREATE TABLE IF NOT EXISTS \`${tableName}\` (
      id INT AUTO_INCREMENT PRIMARY KEY,
      source_id VARCHAR(255) NULL,
      data JSON NOT NULL,
      imported_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
  `);

  // Clear & reload — idempotent
  await db.query(`TRUNCATE TABLE \`${tableName}\``);

  // Insert in chunks of 200
  const CHUNK = 200;
  for (let i = 0; i < rows.length; i += CHUNK) {
    const slice = rows.slice(i, i + CHUNK);
    const values = slice.map(r => [r.id || null, JSON.stringify(r)]);
    await db.query(
      `INSERT INTO \`${tableName}\` (source_id, data) VALUES ?`,
      [values]
    );
  }
  return rows.length;
}

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

(async () => {
  const manifest = loadJson('manifest');
  if (!manifest) {
    console.error('✗ exports/manifest.json not found. Run export-from-supabase.cjs first.');
    process.exit(1);
  }

  console.log(`→ Source export: ${manifest.exported_at}`);
  console.log(`→ MySQL: ${process.env.DB_HOST || 'localhost'}:${process.env.DB_PORT || 3306}/${process.env.DB_NAME || 'tripovo_support'}`);

  const db = await connect();

  // 1. Profiles → platform_users (special-case field mapping)
  if (!only || only.includes('profiles')) {
    console.log('\n▸ profiles → platform_users');
    try { await importProfiles(db); }
    catch (e) { console.error(`  ✗ ${e.message}`); }
  }

  // 2. Every other table → supabase_<name> archive
  console.log('\n▸ Archiving other tables into supabase_* archive tables');
  const otherTables = Object.keys(manifest.tables).filter(t => t !== 'profiles');
  for (const name of otherTables) {
    if (only && !only.includes(name)) continue;
    if (manifest.tables[name].error) continue;
    process.stdout.write(`  ${name.padEnd(28)} `);
    try {
      const n = await archiveTable(db, name);
      console.log(`✓ ${n} rows`);
    } catch (e) {
      console.log(`✗ ${e.message}`);
    }
  }

  await db.end();
  console.log('\n✓ Import complete');
})().catch(e => {
  console.error('✗ Fatal:', e);
  process.exit(1);
});
