/**
 * Seeds the S-2026-00045 reported scenario into edr_dev (idempotent).
 * References SF45-* ("Scenario Fortyfive"). Rows are kept as evidence.
 *
 *   SELECT * FROM freight.bookings WHERE reference LIKE 'SF45-%';
 */
const { Client } = require('pg');

const YARD = {
  DCT: 'fc558b95-da28-4fc3-8348-311a290c34ae',
  DIRE: 'f7b1686f-d43e-42aa-bc6a-d3d5849296c9',
  GMP: '61ae1e66-c229-4dcd-9851-b2b9424f3a95', // KALITY = GMP (Gelan)
};
const PERISHABLE = 'a5991d3a-d690-4b7e-98fd-ea3333aa16e7'; // NW5 30T / PW2 20T
const FT40 = '349072e7-8a90-4c03-b682-08976abfd7e8';
const COMPANY = '300d5510-e3a5-4858-bc28-6e3beda8ca80';

const BOOKINGS = [
  { ref: 'SF45-1', type: 'CONTAINER', from: 'DCT', to: 'DIRE', qty: 9,
    note: 'S45: DCT->Dire 9x40ft' },
  { ref: 'SF45-2', type: 'CONTAINER', from: 'DCT', to: 'DIRE', qty: 19,
    note: 'S45: DCT->Dire 19x40ft' },
  { ref: 'SF45-3', type: 'CONTAINER', from: 'DCT', to: 'DIRE', qty: 19,
    note: 'S45: DCT->Dire 19x40ft (second)' },
  { ref: 'SF45-4', type: 'BULK', from: 'DCT', to: 'GMP', tons: 695,
    note: 'S45: DCT->GMP 695T Perishable - THE ONE NOT BEING SELECTED' },
  { ref: 'SF45-5', type: 'CONTAINER', from: 'DIRE', to: 'GMP', qty: 42,
    note: 'S45: Dire->GMP 42x40ft' },
];

async function main() {
  const client = new Client({
    host: process.env.DB_HOST || '10.18.7.207',
    port: Number(process.env.DB_PORT || 5432),
    database: process.env.DB_NAME || 'edr_dev',
    user: process.env.DB_USER || 'postgres',
    password: process.env.DB_PASSWORD || 'dcba@1234',
  });
  await client.connect();
  const out = [];

  for (const b of BOOKINGS) {
    const isBulk = b.type === 'BULK';
    const vgmPerUnit = 26;
    const totalVgm = isBulk ? b.tons : b.qty * vgmPerUnit;

    const existing = await client.query(
      `SELECT id FROM freight.bookings WHERE reference = $1`, [b.ref]);
    let id;
    if (existing.rows.length) {
      id = existing.rows[0].id;
      await client.query(
        `UPDATE freight.bookings
            SET cargo_total_weight_vgm=$2, cargo_type_id=$3, origin_yard_id=$4,
                destination_yard_id=$5, freight_type=$6, trade_direction='IMPORT',
                status='CLEARANCE_READY', payment_status='PENDING',
                scheduling_status='ELIGIBLE', train_schedule_id=NULL,
                cargo_free_text=$7, updated_at=now()
          WHERE id=$1`,
        [id, totalVgm, isBulk ? PERISHABLE : null, YARD[b.from], YARD[b.to],
         b.type, b.note]);
    } else {
      const res = await client.query(
        `INSERT INTO freight.bookings (
           reference, status, total_amount, payment_status, contract_type,
           trade_direction, equipment_return, cargo_total_weight_vgm, is_hazardous,
           payment_currency, version_number, priority_score, origin_yard_id,
           destination_yard_id, cargo_type_id, cargo_free_text, freight_type,
           scheduling_status, is_government, customs_clearing_enabled, is_reefer,
           booking_type, is_split, company_id, created_at, updated_at
         ) VALUES ($1,'CLEARANCE_READY',0,'PENDING','NEW','IMPORT','WITHOUT_RETURN',
                   $2,false,'ETB',1,0,$3,$4,$5,$6,$7,'ELIGIBLE',false,false,false,
                   'ONE_TIME',false,$8, now(), now())
         RETURNING id`,
        [b.ref, totalVgm, YARD[b.from], YARD[b.to],
         isBulk ? PERISHABLE : null, b.note, b.type, COMPANY]);
      id = res.rows[0].id;
    }

    await client.query(`DELETE FROM freight.booking_container WHERE booking_id=$1`, [id]);
    if (!isBulk) {
      await client.query(
        `INSERT INTO freight.booking_container
           (booking_id, container_type_id, quantity, vgm_per_unit_tons,
            total_vgm_tons, wagons_required, container_size, created_at, updated_at)
         VALUES ($1,$2,$3,$4,$5,$6,'40ft', now(), now())`,
        [id, FT40, b.qty, vgmPerUnit, b.qty * vgmPerUnit, b.qty]);
    }
    out.push({ ref: b.ref, id, note: b.note });
  }

  console.table(out);
  await client.end();
}

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