import { type EntityManager } from 'typeorm';

/**
 * Floor-snapshot resolver — used by booking-create, amended-transport
 * add, and the supplier-payable fallback. Reads Mellow's Floor Price
 * for the row's (destination × route × cab × mode × date) and returns
 * it as the "expected cost basis" for that line.
 *
 * Resolution rules:
 *   1. Season for `date` in the destination (1-based index, ordered
 *      by `sort_order ASC NULLS LAST, name ASC` — matches the FE
 *      pricing matrix ordering). Both legacy `start_date`/`end_date`
 *      varchars AND the `durations` jsonb format are honoured.
 *   2. transport_pricing row matching (service, vehicle_type, mode,
 *      destination currency). PVT → vehicle_type_id required; SIC →
 *      vehicle_type_id IS NULL.
 *   3. Pick `floor_season_N_price` for the resolved season. Fall back
 *      to `floor_default_price`. Return null on any miss.
 *
 * Returns null when:
 *   - no date provided
 *   - destination has no currency configured
 *   - no matching transport_pricing row
 *   - no floor configured at all (neither seasonal nor default)
 *
 * Null is the "no snapshot" sentinel — Movement Chart's Cost cell
 * already treats it as "ops will fill in" via the `—` italic display.
 */
export interface FloorSnapshotInput {
  tenant_id: string;
  destination_id: string;
  transport_service_id: string;
  vehicle_type_id: string | null | undefined;
  is_shared: boolean;
  date: string | null;
}

export async function resolveFloorSnapshot(
  manager: EntityManager,
  input: FloorSnapshotInput,
): Promise<number | null> {
  if (!input.date) return null;
  const mode: 'PVT' | 'SIC' = input.is_shared ? 'SIC' : 'PVT';

  const params: Array<string | null> = [
    input.tenant_id,
    input.destination_id,
    input.transport_service_id,
    mode,
    input.vehicle_type_id || null,
    input.date.slice(5), // MM-DD
  ];

  const rows = (await manager.query(
    `
    WITH active_seasons AS (
      SELECT id, name,
             ROW_NUMBER() OVER (ORDER BY sort_order ASC NULLS LAST, name ASC) AS season_index
        FROM seasons
       WHERE tenant_id = $1
         AND destination_id = $2
         AND is_deleted = false
         AND is_active = true
    ),
    season_for_date AS (
      SELECT s.id, s.name, s.season_index
        FROM seasons s2
        JOIN active_seasons s ON s.id = s2.id
       WHERE s2.tenant_id = $1
         AND s2.destination_id = $2
         AND s2.is_deleted = false
         AND s2.is_active = true
         AND (
           (s2.start_date IS NOT NULL AND s2.end_date IS NOT NULL
            AND $6 BETWEEN s2.start_date AND s2.end_date)
           OR EXISTS (
             SELECT 1
               FROM jsonb_array_elements(COALESCE(s2.durations, '[]'::jsonb)) d
              WHERE $6 BETWEEN d->>'start' AND d->>'end'
           )
         )
       -- Deterministic when seasons overlap (which the contract says
       -- they shouldn't, but no DB constraint enforces it).
       ORDER BY s.season_index ASC
       LIMIT 1
    ),
    destination_currency AS (
      SELECT currency_id FROM destinations WHERE id = $2
    )
    SELECT
      tp.floor_default_price,
      tp.floor_season_1_price,
      tp.floor_season_2_price,
      tp.floor_season_3_price,
      tp.floor_season_4_price,
      tp.floor_season_5_price,
      (SELECT season_index FROM season_for_date) AS season_index
      FROM transport_pricing tp, destination_currency dc
     WHERE tp.tenant_id = $1
       AND tp.is_deleted = false
       AND tp.transport_service_id = $3
       AND tp.mode = $4
       AND tp.currency_id = dc.currency_id
       AND (
         ($4 = 'PVT' AND tp.vehicle_type_id = $5::uuid)
         OR ($4 = 'SIC' AND tp.vehicle_type_id IS NULL)
       )
     LIMIT 1
    `,
    params,
  )) as Array<{
    floor_default_price: string | null;
    floor_season_1_price: string | null;
    floor_season_2_price: string | null;
    floor_season_3_price: string | null;
    floor_season_4_price: string | null;
    floor_season_5_price: string | null;
    season_index: string | null;
  }>;

  const row = rows[0];
  if (!row) return null;

  const idx = row.season_index ? Number(row.season_index) : null;
  const seasonal = idx
    ? (row[`floor_season_${idx}_price` as keyof typeof row] as string | null)
    : null;
  const value = seasonal ?? row.floor_default_price;
  return value != null ? Number(value) : null;
}