/**
 * Hard-delete Query → Booking → Settlement → Invoice → ticket-inventory
 * rows for every tenant. Tables, schema, indexes, and sequences stay.
 *
 * Never soft-deletes (no `is_deleted = true`). Never DROP TABLE / TRUNCATE.
 * Every statement is a physical `DELETE FROM` via raw SQL (bypasses TypeORM).
 *
 * When to use
 *   Client asked to wipe live ops data and keep masters (hotels, activities,
 *   ticket types, suppliers, users). Not a full DB reset.
 *
 * Do not use
 *   npm run db:wipe                 → wipe-all.ts (also deletes masters)
 *   scripts/wipe-transactional.sql  → older incomplete wipe; missing invoices,
 *                                     ticket inventory, cash, credit ledgers
 *
 * Usage (from nest-dmc/, uses .env DB_*)
 *   npm run db:wipe:query-booking-finance
 *   CONFIRM_WIPE=YES npm run db:wipe:query-booking-finance
 *
 * Safety
 *   Without CONFIRM_WIPE=YES this prints the plan and exits (no DELETE).
 *   All statements run in one TypeORM transaction — any FK error rolls back.
 *   Take a database backup before running against a server.
 *
 * Ticket PDFs / images under uploads/{subdomain}/ are not deleted.
 */

import { DataSource, type EntityManager } from 'typeorm';
import * as dotenv from 'dotenv';

dotenv.config();

/**
 * Hard DELETE FROM only. Never `is_deleted`, never DROP, never TRUNCATE.
 * Child → parent order — do not reorder; later rows RESTRICT-reference earlier ones.
 */
const WIPE_STATEMENTS: readonly string[] = [
  // 1. Ticket inventory (not activity_tickets catalog)
  `DELETE FROM activity_ticket_journeys`,
  `DELETE FROM activity_ticket_items`,
  `DELETE FROM activity_ticket_batches`,

  // 2. Customer / agent / supplier credit (delete ledger before collections/payouts — RESTRICT)
  `DELETE FROM customer_credit_ledger_movements`,
  `DELETE FROM customer_credit_ledger`,

  // 3. Movement-chart ops cash (RESTRICT → settlement_payments + item suppliers)
  `DELETE FROM service_payment_collection_confirmations`,
  `DELETE FROM service_payment_collections`,
  // batches.booking_cash_collection_id → collections (child first). Do not swap.
  `DELETE FROM booking_cash_collection_batches`,
  `DELETE FROM booking_cash_collections`,
  // md_cash_collections.booking_id → bookings ON DELETE CASCADE. Explicit
  // delete so the leftover count can prove the table is empty.
  `DELETE FROM md_cash_collections`,

  // 4. Payment Settlement
  `DELETE FROM settlement_payments`,
  `DELETE FROM payment_settlements`,
  `DELETE FROM booking_settlement_masters`,
  `DELETE FROM payment_collections`,
  `DELETE FROM supplier_payouts`,

  // 5. Accounting invoices only
  `DELETE FROM customer_invoices`,

  // 6. Bookings (RESTRICT on queries/quotes — must die first)
  `DELETE FROM booking_hotel_extras`,
  `DELETE FROM booking_hotel_periods`,
  `DELETE FROM booking_flight_details`,
  `DELETE FROM booking_transport_item_suppliers`,
  `DELETE FROM booking_activity_item_suppliers`,
  `DELETE FROM booking_special_item_suppliers`,
  `DELETE FROM booking_change_requests`,
  `DELETE FROM booking_hotel_items`,
  `DELETE FROM booking_transport_items`,
  `DELETE FROM booking_activity_items`,
  `DELETE FROM booking_special_items`,
  `DELETE FROM bookings`,

  // 7. Queries + quotes
  `DELETE FROM query_quote_hotel_extras`,
  `DELETE FROM query_quote_hotel_periods`,
  `DELETE FROM query_quote_hotels`,
  `DELETE FROM query_quote_transports`,
  `DELETE FROM query_quote_activities`,
  `DELETE FROM query_quote_specials`,
  `DELETE FROM query_quotes`,
  `DELETE FROM queries`,

  // 8. Connected leftovers (no FK)
  `DELETE FROM notifications`,
  // Only entity_types the app writes for this pipeline (AuditEntityType +
  // booking_cash_collection / md_cash_collection / service_payment_collection).
  // Settlements, invoices, payouts, credit, and ticket inventory have no audit.log calls.
  `DELETE FROM audit_logs
    WHERE entity_type IN (
      'query',
      'query_quote',
      'booking',
      'booking_transport_item',
      'booking_activity_item',
      'booking_hotel_item',
      'booking_special_item',
      'change_request',
      'booking_cash_collection',
      'md_cash_collection',
      'service_payment_collection'
    )`,
];

const REMAINING_COUNTS_SQL = `
  SELECT 'activity_ticket_batches' AS table_name, COUNT(*)::bigint AS remaining FROM activity_ticket_batches
  UNION ALL SELECT 'activity_ticket_items', COUNT(*) FROM activity_ticket_items
  UNION ALL SELECT 'activity_ticket_journeys', COUNT(*) FROM activity_ticket_journeys
  UNION ALL SELECT 'customer_credit_ledger', COUNT(*) FROM customer_credit_ledger
  UNION ALL SELECT 'customer_credit_ledger_movements', COUNT(*) FROM customer_credit_ledger_movements
  UNION ALL SELECT 'service_payment_collection_confirmations', COUNT(*) FROM service_payment_collection_confirmations
  UNION ALL SELECT 'service_payment_collections', COUNT(*) FROM service_payment_collections
  UNION ALL SELECT 'booking_cash_collection_batches', COUNT(*) FROM booking_cash_collection_batches
  UNION ALL SELECT 'booking_cash_collections', COUNT(*) FROM booking_cash_collections
  UNION ALL SELECT 'md_cash_collections', COUNT(*) FROM md_cash_collections
  UNION ALL SELECT 'settlement_payments', COUNT(*) FROM settlement_payments
  UNION ALL SELECT 'payment_settlements', COUNT(*) FROM payment_settlements
  UNION ALL SELECT 'booking_settlement_masters', COUNT(*) FROM booking_settlement_masters
  UNION ALL SELECT 'payment_collections', COUNT(*) FROM payment_collections
  UNION ALL SELECT 'supplier_payouts', COUNT(*) FROM supplier_payouts
  UNION ALL SELECT 'customer_invoices', COUNT(*) FROM customer_invoices
  UNION ALL SELECT 'booking_hotel_extras', COUNT(*) FROM booking_hotel_extras
  UNION ALL SELECT 'booking_hotel_periods', COUNT(*) FROM booking_hotel_periods
  UNION ALL SELECT 'booking_flight_details', COUNT(*) FROM booking_flight_details
  UNION ALL SELECT 'booking_transport_item_suppliers', COUNT(*) FROM booking_transport_item_suppliers
  UNION ALL SELECT 'booking_activity_item_suppliers', COUNT(*) FROM booking_activity_item_suppliers
  UNION ALL SELECT 'booking_special_item_suppliers', COUNT(*) FROM booking_special_item_suppliers
  UNION ALL SELECT 'booking_change_requests', COUNT(*) FROM booking_change_requests
  UNION ALL SELECT 'booking_hotel_items', COUNT(*) FROM booking_hotel_items
  UNION ALL SELECT 'booking_transport_items', COUNT(*) FROM booking_transport_items
  UNION ALL SELECT 'booking_activity_items', COUNT(*) FROM booking_activity_items
  UNION ALL SELECT 'booking_special_items', COUNT(*) FROM booking_special_items
  UNION ALL SELECT 'bookings', COUNT(*) FROM bookings
  UNION ALL SELECT 'query_quote_hotel_extras', COUNT(*) FROM query_quote_hotel_extras
  UNION ALL SELECT 'query_quote_hotel_periods', COUNT(*) FROM query_quote_hotel_periods
  UNION ALL SELECT 'query_quote_hotels', COUNT(*) FROM query_quote_hotels
  UNION ALL SELECT 'query_quote_transports', COUNT(*) FROM query_quote_transports
  UNION ALL SELECT 'query_quote_activities', COUNT(*) FROM query_quote_activities
  UNION ALL SELECT 'query_quote_specials', COUNT(*) FROM query_quote_specials
  UNION ALL SELECT 'query_quotes', COUNT(*) FROM query_quotes
  UNION ALL SELECT 'queries', COUNT(*) FROM queries
  UNION ALL SELECT 'notifications', COUNT(*) FROM notifications
  UNION ALL SELECT 'audit_logs (pipeline leftover)', COUNT(*) FROM audit_logs
    WHERE entity_type IN (
      'query', 'query_quote', 'booking',
      'booking_transport_item', 'booking_activity_item',
      'booking_hotel_item', 'booking_special_item',
      'change_request', 'booking_cash_collection', 'md_cash_collection',
      'service_payment_collection'
    )
  UNION ALL SELECT 'activity_tickets (keep)', COUNT(*) FROM activity_tickets
  UNION ALL SELECT 'activities (keep)', COUNT(*) FROM activities
  UNION ALL SELECT 'hotels (keep)', COUNT(*) FROM hotels
  UNION ALL SELECT 'suppliers (keep)', COUNT(*) FROM suppliers
  UNION ALL SELECT 'users (keep)', COUNT(*) FROM users
  UNION ALL SELECT 'settlement_receipt_sequences (keep)', COUNT(*) FROM settlement_receipt_sequences
  UNION ALL SELECT 'tenant_sequences (keep)', COUNT(*) FROM tenant_sequences
  ORDER BY table_name
`;

interface RemainingRow {
  table_name: string;
  remaining: string;
}

async function wipe(em: EntityManager): Promise<RemainingRow[]> {
  for (const sql of WIPE_STATEMENTS) {
    await em.query(sql);
  }
  return em.query(REMAINING_COUNTS_SQL) as Promise<RemainingRow[]>;
}

async function main() {
  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(' db:wipe:query-booking-finance');
  console.log(' Hard DELETE FROM only — no soft delete, tables kept.');
  console.log(' Queries, bookings, settlements, invoices,');
  console.log(' ticket inventory — all tenants.');
  console.log(' Keep masters, users, pricing, number sequences.');
  console.log('================================================');
  console.log(` host: ${host}:${port}`);
  console.log(` db:   ${database}`);
  console.log(` user: ${username}`);
  console.log('');
  console.log(' WIPES:');
  console.log('   queries + quotes');
  console.log('   bookings + itinerary + change requests');
  console.log('   settlements, collections, payouts, credit ledgers, ops cash');
  console.log('   customer_invoices');
  console.log('   activity_ticket_batches / items / journeys');
  console.log('   notifications + matching audit_logs');
  console.log('');
  console.log(' KEEPS:');
  console.log('   tenants, users, roles, hotels, activities,');
  console.log('   activity_tickets (catalog), suppliers, banks, pricing,');
  console.log('   tenant_sequences, settlement_receipt_sequences');
  console.log('');

  if (process.env.CONFIRM_WIPE !== 'YES') {
    console.log('Dry-run only — nothing deleted.');
    console.log('Re-run:  CONFIRM_WIPE=YES npm run db:wipe:query-booking-finance');
    process.exit(0);
  }

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

  await ds.initialize();
  console.log('connected — wiping inside one transaction');
  try {
    const remaining = await ds.transaction((em) => wipe(em));
    console.log('\nRow counts after wipe (0 = cleared, keep-tables should be > 0):');
    console.table(remaining);
  } finally {
    await ds.destroy();
  }
  console.log('wipe-query-booking-finance complete');
}

main().catch((err: unknown) => {
  const message = err instanceof Error ? err.message : String(err);
  console.error('wipe-query-booking-finance failed:', message);
  process.exit(1);
});