/**
 * Backfill runner — executes scripts/backfill-pricing.sql against the
 * DB the backend .env points at. One-shot data fix to populate any
 * (route × vehicle type) and (activity × ticket × age_group) rows that
 * were skipped before the auto-sync logic landed.
 *
 * Usage:
 *   npm run db:backfill:pricing                          # dry-run plan
 *   CONFIRM_BACKFILL=YES npm run db:backfill:pricing     # actually apply
 *
 * Safety:
 *   - Requires CONFIRM_BACKFILL=YES to apply; otherwise prints the plan
 *     and exits. Prevents accidental writes against a wrong DB.
 *   - Wrapped in a single transaction in the SQL — any failure rolls back.
 *   - Uses WHERE NOT EXISTS guards so re-runs insert nothing.
 */

import { DataSource } from 'typeorm';
import * as fs from 'node:fs';
import * as path from 'node:path';
import * as dotenv from 'dotenv';

dotenv.config();

const SQL_FILE = path.resolve(__dirname, 'backfill-pricing.sql');

async function main() {
  const sql = fs.readFileSync(SQL_FILE, 'utf8');

  const host = process.env.DB_HOST || 'localhost';
  const port = Number(process.env.DB_PORT || 5432);
  const username = process.env.DB_USERNAME || 'postgres';
  const database = process.env.DB_NAME || 'dmc_ops';

  console.log('================================================');
  console.log(' DMC db:backfill:pricing                         ');
  console.log(' Fills missing transport_pricing + activity_pricing rows');
  console.log('================================================');
  console.log(` host: ${host}:${port}`);
  console.log(` db:   ${database}`);
  console.log(` user: ${username}`);
  console.log('');
  console.log(' Will INSERT (where missing):');
  console.log('   - transport_pricing for every active');
  console.log('     (transport_service × vehicle_type) per tenant');
  console.log('   - activity_pricing for every active');
  console.log('     (activity × ticket × enabled age_group) per tenant');
  console.log(' Will SKIP rows that already exist (WHERE NOT EXISTS).');
  console.log(' Will SKIP destinations without currency_id.');
  console.log('');

  if (process.env.CONFIRM_BACKFILL !== 'YES') {
    console.log('Dry-run mode. Re-run with CONFIRM_BACKFILL=YES to execute.');
    console.log('   e.g.  CONFIRM_BACKFILL=YES npm run db:backfill:pricing');
    process.exit(0);
  }

  const ds = new DataSource({
    type: 'postgres',
    host,
    port,
    username,
    password: process.env.DB_PASSWORD || '',
    database,
  });

  await ds.initialize();
  console.log('-> connected, executing backfill...');
  try {
    // The SQL emits 3 SELECTs in order:
    //   [0] transport_pricing_inserted (count of new rows)
    //   [1] activity_pricing_inserted  (count of new rows)
    //   [2] totals after backfill
    // node-postgres returns each as a separate result set.
    const result = await ds.query(sql);

    if (Array.isArray(result)) {
      // typeorm flattens to last result; print whatever we get.
      console.log('\nResult:');
      console.table(result);
    }

    // Re-query the totals so output is stable regardless of how the
    // driver materialises multi-statement results.
    const totals = await ds.query(`
      SELECT
        (SELECT COUNT(*) FROM transport_pricing WHERE is_deleted = false) AS transport_pricing_total,
        (SELECT COUNT(*) FROM activity_pricing  WHERE is_deleted = false) AS activity_pricing_total
    `);
    console.log('\nFinal row counts:');
    console.table(totals);
  } finally {
    await ds.destroy();
  }
  console.log('Done.');
}

main().catch((err) => {
  console.error('Backfill failed:', err.message);
  process.exit(1);
});