import { type EntityManager } from 'typeorm';

/**
 * Activity Floor-snapshot resolver — used by booking-create and the
 * activity supplier-payable hook. Reads Mellow's Floor Price for the
 * row's (destination × activity × ticket × age × date) and returns
 * it as the per-unit cost basis.
 *
 * Per-line snapshot is `floor_per_unit × quantity` — same per-TRIP
 * TOTAL semantic as the transport helper.
 *
 * Resolution:
 *   1. activity_pricing row keyed on (activity, ticket_id, age_group)
 *      — ticket_id NULL on the row matches lines without a ticket
 *   2. Season for `date` in the destination — read from `seasons` with
 *      sort_order to derive a 1..5 index
 *   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 / activity_id provided
 *   - no matching activity_pricing row
 *   - no floor configured at all
 *
 * Null is the "no snapshot" sentinel — callers leave actual_supplier_cost
 * NULL on the line; ops fills it in when they assign a supplier.
 */
export interface ActivityFloorSnapshotInput {
  tenant_id: string;
  destination_id: string;
  activity_id: string;
  ticket_id: string | null;
  age_group: string;
  date: string | null;
}

export async function resolveActivityFloorSnapshot(
  manager: EntityManager,
  input: ActivityFloorSnapshotInput,
): Promise<number | null> {
  if (!input.date) return null;
  if (!input.activity_id || !input.age_group) return null;

  const params: Array<string | null> = [
    input.tenant_id,
    input.destination_id,
    input.activity_id,
    input.age_group,
    input.ticket_id,
    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'
           )
         )
       ORDER BY s.season_index ASC
       LIMIT 1
    )
    SELECT
      ap.floor_default_price,
      ap.floor_season_1_price,
      ap.floor_season_2_price,
      ap.floor_season_3_price,
      ap.floor_season_4_price,
      ap.floor_season_5_price,
      (SELECT season_index FROM season_for_date) AS season_index
      FROM activity_pricing ap
     WHERE ap.tenant_id = $1
       AND ap.destination_id = $2
       AND ap.activity_id = $3
       AND ap.age_group = $4
       AND ap.is_deleted = false
       AND (
         ($5::uuid IS NULL AND ap.ticket_id IS NULL)
         OR ap.ticket_id = $5::uuid
       )
     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;
}