#!/usr/bin/env node
/*
 * Backfill support_requests.app_id for legacy rows.
 *
 * Iterates every row where app_id IS NULL AND booking_id IS NOT NULL,
 * looks up the booking via the EasyGDS core API (same helper the server
 * uses at runtime), and stamps app_id.
 *
 * Also sets created_by_context for rows that don't have one yet — best-effort,
 * only when we can correlate created_by to a known platform_users row.
 *
 * Safe to re-run: it skips rows whose app_id is already populated.
 *
 * Usage:
 *   cd trip-glide-control
 *   node server/scripts/backfill-support-app-id.cjs
 */

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

// Load .env from project root (same pattern as inspect-booking-contact.cjs).
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 IAM_URL = process.env.EASYGDS_IAM_URL || 'https://iam.stg-services.easygds.com/api';
const CORE_URL = process.env.EASYGDS_CORE_URL || 'https://core.stg-services.easygds.com/api';
const EMAIL = process.env.EASYGDS_EMAIL || 'crm@serviceaccount.easygds.com';
const PASSWORD = process.env.EASYGDS_PASSWORD || 'm27qwe9';

async function authenticateUpstream() {
  const res = await fetch(IAM_URL + '/auth', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({
      scope_type: 'System', scope_id: '',
      email: EMAIL, secret_key: PASSWORD, flow_type: 'Basic',
    }),
  });
  const j = await res.json();
  if (!j.access_token) throw new Error('Upstream auth failed: ' + JSON.stringify(j));
  return j.access_token;
}

async function fetchBookingAppId(token, bookingId) {
  const res = await fetch(CORE_URL + '/bookings?id=' + encodeURIComponent(bookingId), {
    headers: { Authorization: 'Bearer ' + token },
  });
  const j = await res.json().catch(() => ({}));
  const root = j?.result || j || {};
  return root.app_id || root.app?.id || null;
}

async function main() {
  const pool = mysql.createPool({
    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: parseInt(process.env.DB_PORT || '3306'),
    waitForConnections: true,
    connectionLimit: 5,
  });

  console.log('→ Authenticating to EasyGDS…');
  const token = await authenticateUpstream();
  console.log('✓ Upstream token acquired');

  const [rows] = await pool.query(
    `SELECT id, booking_id FROM support_requests
     WHERE app_id IS NULL AND booking_id IS NOT NULL AND booking_id != ''`
  );
  console.log(`→ ${rows.length} rows need backfilling`);

  let done = 0;
  let failed = 0;
  for (const row of rows) {
    try {
      const appId = await fetchBookingAppId(token, row.booking_id);
      if (appId) {
        await pool.query('UPDATE support_requests SET app_id = ? WHERE id = ?', [appId, row.id]);
        done++;
        if (done % 25 === 0) console.log(`  … ${done} updated`);
      } else {
        failed++;
      }
    } catch (e) {
      failed++;
      console.warn(`  ! ${row.id}: ${e.message}`);
    }
  }

  console.log(`✓ Backfill complete — ${done} updated, ${failed} skipped/failed`);

  // Report remaining gaps.
  const [[{ cnt }]] = await pool.query(
    'SELECT COUNT(*) AS cnt FROM support_requests WHERE app_id IS NULL AND booking_id IS NOT NULL AND booking_id != ""'
  );
  console.log(`  remaining NULL app_id rows (with booking_id): ${cnt}`);

  await pool.end();
}

main().catch((e) => { console.error(e); process.exit(1); });
