import { MigrationInterface, QueryRunner } from 'typeorm';

/**
 * Repairs schema drift on databases that were originally built by TypeORM
 * `synchronize` (at an older entity snapshot) and never had their migration
 * history recorded. Such databases have `freight.migrations` empty while most
 * of the schema already exists, so a from-scratch migration run aborts on the
 * first non-idempotent statement and never reaches the columns/tables added
 * after synchronize was last used.
 *
 * The deployment procedure for those databases is:
 *   1. Baseline every pre-existing migration into `freight.migrations`.
 *   2. Run migrations — this file is the only pending one and back-fills the
 *      objects the drift scan found missing.
 *
 * Every statement is idempotent (IF NOT EXISTS / guarded CREATE TYPE), so it is
 * also safe on a clean database where the earlier migrations already created
 * these objects — it simply no-ops.
 */
export class RepairSynchronizeDrift1870000000000
  implements MigrationInterface
{
  name = 'RepairSynchronizeDrift1870000000000';

  public async up(queryRunner: QueryRunner): Promise<void> {
    // --- enum types (derived from entities that never had a source migration) ---
    await queryRunner.query(`DO $$ BEGIN
      CREATE TYPE freight.consignments_cargo_type_enum AS ENUM (
        'CONTAINER', 'BULK_LIQUID', 'BULK_DRY', 'GENERAL', 'REFRIGERATED', 'HAZARDOUS'
      );
    EXCEPTION WHEN duplicate_object THEN null; END $$;`);
    await queryRunner.query(`DO $$ BEGIN
      CREATE TYPE freight.consignments_status_enum AS ENUM (
        'PENDING', 'LOADED', 'IN_TRANSIT', 'AT_DESTINATION', 'DELIVERED', 'RETURNED'
      );
    EXCEPTION WHEN duplicate_object THEN null; END $$;`);
    await queryRunner.query(`DO $$ BEGIN
      CREATE TYPE freight.tracking_events_status_enum AS ENUM (
        'PENDING', 'LOADED', 'IN_TRANSIT', 'AT_DESTINATION', 'DELIVERED', 'RETURNED'
      );
    EXCEPTION WHEN duplicate_object THEN null; END $$;`);

    // --- missing tables ---
    await queryRunner.query(`CREATE TABLE IF NOT EXISTS freight.consignments (
      id uuid NOT NULL DEFAULT gen_random_uuid(),
      booking_id uuid NOT NULL,
      tracking_number varchar(64) NOT NULL,
      cargo_type freight.consignments_cargo_type_enum NOT NULL,
      weight_kg numeric(12, 2) NOT NULL,
      status freight.consignments_status_enum NOT NULL DEFAULT 'PENDING',
      origin_station varchar(128) NOT NULL,
      destination_station varchar(128) NOT NULL,
      created_at timestamptz NOT NULL DEFAULT now(),
      updated_at timestamptz NOT NULL DEFAULT now(),
      deleted_at timestamptz,
      CONSTRAINT pk_consignments PRIMARY KEY (id),
      CONSTRAINT uq_consignments_tracking_number UNIQUE (tracking_number)
    );`);

    await queryRunner.query(`CREATE TABLE IF NOT EXISTS freight.tracking_events (
      id uuid NOT NULL DEFAULT gen_random_uuid(),
      consignment_id uuid NOT NULL,
      location varchar(256) NOT NULL,
      status freight.tracking_events_status_enum NOT NULL,
      occurred_at timestamptz NOT NULL,
      description text,
      created_at timestamptz NOT NULL DEFAULT now(),
      updated_at timestamptz NOT NULL DEFAULT now(),
      deleted_at timestamptz,
      CONSTRAINT pk_tracking_events PRIMARY KEY (id)
    );`);

    await queryRunner.query(`CREATE TABLE IF NOT EXISTS freight.fuel_purchases (
      id uuid NOT NULL DEFAULT gen_random_uuid(),
      vehicle_id uuid NOT NULL,
      purchase_date timestamptz NOT NULL,
      liters numeric(10, 2) NOT NULL,
      cost_per_liter numeric(10, 2) NOT NULL,
      total_cost numeric(14, 2) NOT NULL,
      fuel_station varchar(255) NULL,
      payment_method varchar(50) DEFAULT 'CASH',
      odometer_reading numeric(10, 2) NULL,
      driver_id uuid NULL,
      receipt_number varchar(255) NULL,
      notes text NULL,
      created_at timestamptz NOT NULL DEFAULT now(),
      updated_at timestamptz NOT NULL DEFAULT now(),
      deleted_at timestamptz NULL,
      CONSTRAINT pk_fuel_purchases PRIMARY KEY (id),
      CONSTRAINT fk_fuel_purchases_vehicle FOREIGN KEY (vehicle_id)
        REFERENCES freight.vehicles (id) ON DELETE CASCADE
    );`);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_fuel_purchases_vehicle ON freight.fuel_purchases (vehicle_id);`);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_fuel_purchases_date ON freight.fuel_purchases (purchase_date);`);

    await queryRunner.query(`CREATE TABLE IF NOT EXISTS freight.fuel_consumption (
      id uuid NOT NULL DEFAULT gen_random_uuid(),
      vehicle_id uuid NOT NULL,
      month date NOT NULL,
      total_liters numeric(10, 2) NOT NULL,
      total_cost numeric(14, 2) NOT NULL,
      total_distance_km numeric(10, 2) NOT NULL,
      fuel_efficiency_km_per_l numeric(10, 2) NULL,
      number_of_purchases integer DEFAULT 0,
      average_cost_per_liter numeric(10, 2) NULL,
      created_at timestamptz NOT NULL DEFAULT now(),
      updated_at timestamptz NOT NULL DEFAULT now(),
      deleted_at timestamptz NULL,
      CONSTRAINT pk_fuel_consumption PRIMARY KEY (id),
      CONSTRAINT fk_fuel_consumption_vehicle FOREIGN KEY (vehicle_id)
        REFERENCES freight.vehicles (id) ON DELETE CASCADE,
      CONSTRAINT uq_fuel_consumption_vehicle_month UNIQUE (vehicle_id, month)
    );`);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_fuel_consumption_vehicle_month ON freight.fuel_consumption (vehicle_id, month);`);

    await queryRunner.query(`CREATE TABLE IF NOT EXISTS freight.maintenance_schedules (
      id uuid NOT NULL DEFAULT gen_random_uuid(),
      vehicle_id uuid NOT NULL,
      maintenance_type varchar NOT NULL,
      description varchar NOT NULL,
      scheduled_date timestamptz NOT NULL,
      completed_date timestamptz,
      estimated_cost numeric(14,2),
      actual_cost numeric(14,2),
      status varchar NOT NULL DEFAULT 'SCHEDULED',
      odometer_reading numeric,
      service_provider varchar,
      notes text,
      next_due_km numeric,
      next_due_date timestamptz,
      created_at timestamptz NOT NULL DEFAULT now(),
      updated_at timestamptz NOT NULL DEFAULT now(),
      deleted_at timestamptz,
      PRIMARY KEY (id)
    );`);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_maintenance_schedules_vehicle_date ON freight.maintenance_schedules (vehicle_id, scheduled_date);`);

    await queryRunner.query(`CREATE TABLE IF NOT EXISTS freight.maintenance_costs (
      id uuid NOT NULL DEFAULT gen_random_uuid(),
      vehicle_id uuid NOT NULL,
      maintenance_schedule_id uuid,
      incurred_date timestamptz NOT NULL,
      cost_amount numeric(14,2) NOT NULL,
      cost_type varchar NOT NULL,
      description varchar NOT NULL,
      service_provider varchar,
      invoice_number varchar,
      notes text,
      created_at timestamptz NOT NULL DEFAULT now(),
      updated_at timestamptz NOT NULL DEFAULT now(),
      deleted_at timestamptz,
      PRIMARY KEY (id),
      CONSTRAINT fk_maintenance_schedule FOREIGN KEY (maintenance_schedule_id)
        REFERENCES freight.maintenance_schedules (id) ON DELETE SET NULL
    );`);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_maintenance_costs_vehicle_date ON freight.maintenance_costs (vehicle_id, incurred_date);`);

    await queryRunner.query(`CREATE TABLE IF NOT EXISTS freight.otp_verifications (
      id uuid NOT NULL DEFAULT gen_random_uuid(),
      phone varchar NOT NULL,
      otp varchar NOT NULL,
      verified boolean NOT NULL DEFAULT false,
      created_at timestamptz NOT NULL DEFAULT now(),
      updated_at timestamptz NOT NULL DEFAULT now(),
      deleted_at timestamptz,
      CONSTRAINT pk_otp_verifications PRIMARY KEY (id),
      CONSTRAINT uq_otp_verifications_phone UNIQUE (phone)
    );`);

    await queryRunner.query(`CREATE TABLE IF NOT EXISTS freight.booking_batch_offers (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      booking_id uuid NOT NULL REFERENCES freight.bookings(id) ON DELETE CASCADE,
      train_schedule_id uuid NOT NULL REFERENCES freight.train_schedules(id) ON DELETE CASCADE,
      offered_wagons integer NOT NULL,
      total_wagons integer NOT NULL,
      offered_lines jsonb NULL,
      offered_weight_tons numeric(12, 3) NOT NULL,
      offered_amount numeric(14, 2) NOT NULL,
      offered_pricing_breakdown jsonb NULL,
      invoice_id uuid NULL,
      payment_deadline timestamptz NOT NULL,
      status varchar(10) NOT NULL DEFAULT 'OFFERED',
      created_at timestamptz NOT NULL DEFAULT now(),
      updated_at timestamptz NOT NULL DEFAULT now(),
      deleted_at timestamptz NULL
    );`);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_booking_batch_offers_booking ON freight.booking_batch_offers (booking_id);`);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_booking_batch_offers_schedule ON freight.booking_batch_offers (train_schedule_id);`);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_booking_batch_offers_status ON freight.booking_batch_offers (status);`);

    // --- missing columns on existing tables ---
    await queryRunner.query(`ALTER TABLE freight.invoices
      ADD COLUMN IF NOT EXISTS subtotal_amount numeric(14, 2) NOT NULL DEFAULT 0,
      ADD COLUMN IF NOT EXISTS tax_amount numeric(14, 2) NOT NULL DEFAULT 0,
      ADD COLUMN IF NOT EXISTS paid_amount numeric(14, 2) NOT NULL DEFAULT 0,
      ADD COLUMN IF NOT EXISTS balance_amount numeric(14, 2) NOT NULL DEFAULT 0,
      ADD COLUMN IF NOT EXISTS paid_at timestamptz;`);

    await queryRunner.query(`ALTER TABLE freight.bookings
      ADD COLUMN IF NOT EXISTS booking_type varchar(20) NOT NULL DEFAULT 'ONE_TIME',
      ADD COLUMN IF NOT EXISTS customer_truck_plate_number varchar(32),
      ADD COLUMN IF NOT EXISTS customer_truck_driver_name varchar(120),
      ADD COLUMN IF NOT EXISTS customer_truck_type varchar(60),
      ADD COLUMN IF NOT EXISTS customer_truck_container_number varchar(16),
      ADD COLUMN IF NOT EXISTS customer_truck_assigned_at timestamptz,
      ADD COLUMN IF NOT EXISTS customer_truck_arrived_at timestamptz,
      ADD COLUMN IF NOT EXISTS bulk_hazardous_quantity numeric(12,3) NOT NULL DEFAULT 0,
      ADD COLUMN IF NOT EXISTS bulk_reefer_quantity numeric(12,3) NOT NULL DEFAULT 0,
      ADD COLUMN IF NOT EXISTS clearance_current_phase varchar(40),
      ADD COLUMN IF NOT EXISTS duty_required boolean,
      ADD COLUMN IF NOT EXISTS vessel_departure_date date,
      ADD COLUMN IF NOT EXISTS ro_amendment_requested_at timestamptz,
      ADD COLUMN IF NOT EXISTS ro_hold_reason text,
      ADD COLUMN IF NOT EXISTS pre_clearance_finalized_at timestamptz;`);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_bookings_booking_type ON freight.bookings (booking_type);`);

    await queryRunner.query(`ALTER TABLE freight.cargoes
      ADD COLUMN IF NOT EXISTS receiver_name varchar,
      ADD COLUMN IF NOT EXISTS delivered_at timestamp,
      ADD COLUMN IF NOT EXISTS delivery_remarks text;`);

    await queryRunner.query(`ALTER TABLE freight.contract_clearance_cycles
      ADD COLUMN IF NOT EXISTS duty_required boolean,
      ADD COLUMN IF NOT EXISTS vessel_departure_date date,
      ADD COLUMN IF NOT EXISTS ro_amendment_requested_at timestamptz,
      ADD COLUMN IF NOT EXISTS ro_hold_reason text,
      ADD COLUMN IF NOT EXISTS current_phase varchar(40),
      ADD COLUMN IF NOT EXISTS pre_clearance_finalized_at timestamptz;`);

    await queryRunner.query(`ALTER TABLE freight.first_mile
      ADD COLUMN IF NOT EXISTS paid boolean NOT NULL DEFAULT false;`);
    await queryRunner.query(`ALTER TABLE freight.last_mile
      ADD COLUMN IF NOT EXISTS paid boolean NOT NULL DEFAULT false;`);

    await queryRunner.query(`ALTER TABLE freight.route_milestones
      ADD COLUMN IF NOT EXISTS distance_km numeric(10,2);`);

    await queryRunner.query(`ALTER TABLE freight.routes
      ADD COLUMN IF NOT EXISTS status varchar(32) NOT NULL DEFAULT 'AVAILABLE';`);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS "IDX_routes_status" ON freight.routes (status);`);

    await queryRunner.query(`ALTER TABLE freight.train_schedules
      ADD COLUMN IF NOT EXISTS window_phase varchar(20) NULL,
      ADD COLUMN IF NOT EXISTS window_opens_at timestamptz NULL,
      ADD COLUMN IF NOT EXISTS window_closes_at timestamptz NULL,
      ADD COLUMN IF NOT EXISTS doc_review_ends_at timestamptz NULL,
      ADD COLUMN IF NOT EXISTS doc_review_completed_at timestamptz NULL,
      ADD COLUMN IF NOT EXISTS payment_phase_ends_at timestamptz NULL,
      ADD COLUMN IF NOT EXISTS booking_cycle_no integer NOT NULL DEFAULT 0;`);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_train_schedules_window_phase
      ON freight.train_schedules (window_phase) WHERE window_phase IS NOT NULL;`);

    await queryRunner.query(`ALTER TABLE freight.train_scheduling_global_rules
      ADD COLUMN IF NOT EXISTS import_window_lead_days integer NOT NULL DEFAULT 3,
      ADD COLUMN IF NOT EXISTS export_booking_lead_hours integer NOT NULL DEFAULT 24,
      ADD COLUMN IF NOT EXISTS window_open_hour integer NOT NULL DEFAULT 8,
      ADD COLUMN IF NOT EXISTS window_duration_hours numeric(4, 2) NOT NULL DEFAULT 3,
      ADD COLUMN IF NOT EXISTS doc_review_minutes integer NOT NULL DEFAULT 30,
      ADD COLUMN IF NOT EXISTS payment_window_minutes integer NOT NULL DEFAULT 60,
      ADD COLUMN IF NOT EXISTS reopen_delay_minutes integer NOT NULL DEFAULT 90;`);
  }

  public async down(): Promise<void> {
    // No-op: this migration only repairs drift by additively creating objects
    // that other migrations own. Rolling it back would drop objects those
    // migrations legitimately created. Revert individual feature migrations
    // instead if needed.
  }
}
