# Package → FD Flight Price: Read Contract v2 **Status:** contract agreed, implementation pending. **Consumer:** package project, querying the shared MySQL directly (read replica). **Producer:** fdking-admin. **v2 supersedes v1.** Changes: pricing is now exact rather than indicative; `A-list` is removed because "starting from" cannot be computed on the flight component alone; Query B rejects insufficient seats; return-flight detail added to the leg view; `v_package_option_leg_avail` and `v_package_user_pool` dropped as no longer needed. The package project reads through the views below. It never queries `flights`, `flight_ticket_prices`, `planwise_seat_availabilities` or the `package_*` tables directly. --- ## 1. Scope of what these queries return **Flight component only.** The package's own date-wise price lives in the package project and is added there. This matters more than it sounds — see §4. **Connection:** read replica. Never the primary. Search results can be a few seconds stale; that is expected and is handled by the atomic claim at booking (§7), not by trying to make search authoritative. **Contract stability:** the views' output columns are frozen at v2. What sits behind them is free to change. MySQL views cannot take parameters, so `package_id`, `user_id` and `seats` are filters in the caller's `SELECT`; the queries below are exact text and should not need editing. **Schema assumptions, both confirmed:** one `flights` row is exactly one departure date, and `planwise_seat_availabilities.plan_no` is always 1. **One package, several departure cities.** A Bali package sold ex-BOM, ex-DEL and ex-BLR is a single `package_id` with one land price and three sets of flights. Query A returns one row per **(package, origin, date)**, and `origin_code` is on every row. Because the land price is identical across origins, join it on **date alone** — the origin only changes the flight component. To show "from ₹X" without asking the customer for a city, take `MIN` across origins; to show a chosen city, filter on `origin_code`. Same output serves both. `origin_code` must also be in any cache key alongside `user_id`. --- ## 2. Objects ### `v_package_option` One row per bookable combination of flights for a package. | column | type | meaning | |---|---|---| | `option_id` | BIGINT | stable id for this combination | | `package_id` | INT | | | `origin_code` | VARCHAR | departure city, e.g. `BOM`. One package sells from several | | `trip_type` | ENUM | `oneway` \| `return` \| `multicity` | | `depart_date` | DATE | departure date of leg 1 | | `legs_count` | TINYINT | **1** for `return` and `multicity` — one flights row carries the whole itinerary. **2** for `oneway` — two separate flights | | `status` | TINYINT | 1 = live. Always filter on this | ### `v_package_option_leg` One row per leg. Join to `v_package_option` on `option_id`. | column | type | meaning | |---|---|---| | `option_id` | BIGINT | | | `leg_no` | TINYINT | 1-based, itinerary order | | `flight_id` | INT | | | `travel_date` | DATE | departure date of this leg | | `trip_type` | ENUM | `return` here means this one row carries both journeys | | `flight_number` | VARCHAR | outbound | | `dep_city_code` / `arr_city_code` | VARCHAR | **actual** airport, may differ from what was asked for | | `requested_dep_code` / `requested_arr_code` | VARCHAR | e.g. `DEL` where the flight is `HDO` | | `dep_time` / `arr_time` | TIME | outbound | | `arr_date` | DATE | may be +1 from `travel_date` | | `stops` | TINYINT | outbound | | `re_flight_number` | VARCHAR | **return journey**, NULL unless `trip_type = 'return'` | | `re_dep_city_code` / `re_arr_city_code` | VARCHAR | return journey, NULL otherwise | | `return_dep_date` / `return_arr_date` | DATE | return journey, NULL otherwise | | `return_dep_time` / `return_arr_time` | TIME | return journey, NULL otherwise | | `return_stops` | TINYINT | return journey, NULL otherwise | | `infant_price` | INT | per infant, this leg | | `close_before_at` | DATETIME | sale cutoff. Rows past cutoff are already excluded | **A leg row is not a journey.** FD inventory packs journeys into rows three different ways: | `trip_type` | `flights.return_flight` | rows | journeys per row | |---|---|---|---| | `oneway` | 0 | 2 | 1 each | | `return` | 1 | 1 | 2 (outbound + `re_*`) | | `multicity` | 2 | 1 | N (in a `segments` JSON array) | So `legs_count` is 1 for both `return` and `multicity`. The `re_*` columns above cover the return case only. **Do not build itineraries from this view — use `v_package_option_segment`.** It normalises all three shapes into one row per actual journey, so the consumer never has to know which encoding a flight uses. The `re_*` columns are kept here for convenience and for anything already written against them. ### `v_package_option_segment` One row per **journey**, for every trip type — the view to render an itinerary from. Join on `option_id`, order by `leg_no`, `seg_no`. | column | type | meaning | |---|---|---| | `option_id` | BIGINT | | | `leg_no` | TINYINT | which option leg this journey belongs to | | `seg_no` | INT | 1-based order within that leg | | `flight_id` | INT | | | `flight_number` | VARCHAR | | | `airline_code` | VARCHAR | | | `dep_city_code` / `dep_city_name` | | resolved from the segment's city id | | `arr_city_code` / `arr_city_name` | | | | `departure_date` / `departure_time` | | absolute, not an offset | | `arrival_date` / `arrival_time` | | `arrival_date` may be +1 | | `stops` | INT | | | `duration` | VARCHAR | as stored, e.g. `"6:59"` | A 3-leg multicity returns three rows, all with `leg_no = 1` and `seg_no` 1–3. A `return` returns two rows, `leg_no = 1`, `seg_no` 1–2. A `oneway` returns two rows with `leg_no` 1 and 2, each `seg_no = 1`. Ordering by `leg_no, seg_no` gives the itinerary in travel order in every case. Segments in the underlying JSON identify cities by **id**; this view resolves them to codes and names, so the consumer never touches `cities`. ### `v_package_seat_price` One row per sellable seat. Query A and Query B both rank from this, which is what guarantees a calendar price and a quote can't disagree. | column | type | meaning | |---|---|---| | `seat_id` | INT | `flight_ticket_prices.id` | | `flight_id` / `travel_date` | | | | `pool` | ENUM | `gen` \| `pkg` \| `user` | | `tier_rank` | INT | 0 user, 1 package, 2 general — the draw order | | `user_id` | INT | 0 for `gen` and `pkg`; the agent's id for `user` | | `price` | INT | package price where set, else the seat's normal price | --- ## 3. The rules this encodes ``` regular sale : gen seats package sale : gen + pkg + this user's pool draw order : user pool → package pool → general ``` A package draws on unreserved seats; regular sale never touches package seats; a user's own reserved seats are invisible to every other user. `p.user_id IN (0, :user_id)` is that whole rule in one sargable predicate: general and shared-package seats carry `package_user_id = 0`, a user's own seats carry their id, another agent's seats are excluded automatically. **Price** — per seat, from the pool it came from. Where no package price was set, the seat's normal price applies; that fallback is resolved inside the view. **Seats are per leg.** Four passengers need four seats on DEL→BKK *and* four on BKK→DEL. Never shared between legs. **Pax** — `seats = adult + child`. Infants take no seat and are priced from `infant_price`. --- ## 4. Query A — cheapest flight option per package per date **This does not compute "starting from".** It cannot: the package's own price varies by date, so ``` starting_from = MIN over dates of ( package_price[date] + flight_price[date] ) ``` Picking the cheapest *flight* date first and adding that date's package price returns the wrong minimum whenever the cheapest flight day is not the cheapest combined day. Query A therefore returns **one row per (package, departure date)** — roughly 775 rows for a 25-package month — and the package project joins its date-wise prices and takes the minimum. Same query serves both screens, with a different `:package_ids`: | | results page | package calendar | |---|---|---| | `:package_ids` | all 25 | one | | rows returned | ~775 | ~31 | | then | join package prices, `MIN` per package | join package prices, show per date | ```sql WITH opt AS ( SELECT o.option_id, o.package_id, o.origin_code, o.trip_type, o.depart_date, o.legs_count FROM v_package_option o WHERE o.package_id IN (:package_ids) AND o.status = 1 AND o.depart_date BETWEEN :from_date AND :to_date AND (:trip_type IS NULL OR o.trip_type = :trip_type) ), legs AS ( SELECT l.option_id, l.leg_no, l.flight_id, l.travel_date, l.infant_price FROM v_package_option_leg l JOIN opt ON opt.option_id = l.option_id ), days AS ( SELECT DISTINCT flight_id, travel_date FROM legs ), ranked AS ( SELECT p.flight_id, p.travel_date, p.price, ROW_NUMBER() OVER (PARTITION BY p.flight_id, p.travel_date ORDER BY p.tier_rank, p.price, p.seat_id) AS rn FROM v_package_seat_price p JOIN days d ON d.flight_id = p.flight_id AND d.travel_date = p.travel_date WHERE p.user_id IN (0, :user_id) ), flight_day AS ( SELECT flight_id, travel_date, COUNT(*) AS seats_available, SUM(CASE WHEN rn <= :seats THEN price END) AS cost_for_seats, SUM(rn <= :seats) AS seats_priced FROM ranked GROUP BY flight_id, travel_date ), priced AS ( SELECT o.package_id, o.origin_code, o.option_id, o.trip_type, o.depart_date, o.legs_count, MIN(COALESCE(fd.seats_available, 0)) AS seats_sellable, SUM(fd.cost_for_seats) AS total_seat_price, SUM(l.infant_price) * :infants AS total_infant_price, COUNT(DISTINCT l.leg_no) AS legs_priced, MIN(COALESCE(fd.seats_priced, 0)) AS min_seats_priced FROM opt o JOIN legs l ON l.option_id = o.option_id LEFT JOIN flight_day fd ON fd.flight_id = l.flight_id AND fd.travel_date = l.travel_date GROUP BY o.package_id, o.origin_code, o.option_id, o.trip_type, o.depart_date, o.legs_count HAVING legs_priced = o.legs_count AND min_seats_priced = :seats ) SELECT z.package_id, z.option_id, z.trip_type, z.depart_date, z.legs_count, z.seats_sellable, z.total_seat_price, z.total_infant_price, ROUND(z.total_seat_price / :seats) AS avg_seat_price FROM ( SELECT p.*, -- Per ORIGIN as well as per date: a Bali package from BLR is a -- genuinely different price from BOM, so each departure city gets -- its own cheapest option for each date. ROW_NUMBER() OVER (PARTITION BY p.package_id, p.origin_code, p.depart_date ORDER BY p.total_seat_price, p.trip_type) AS rn FROM priced p ) z WHERE z.rn = 1 ORDER BY z.package_id, z.origin_code, z.depart_date; ``` Drop the `rn = 1` filter to return every option on a date rather than the cheapest — useful if the calendar should offer a choice of flight combinations. ### 4a. This price is exact `ranked` numbers every drawable seat in draw order; `cost_for_seats` sums the `:seats` cheapest **in that order**. Where a booking spills across pools at different prices, the total reflects what will actually be charged — there is no pool-minimum approximation anywhere. That is a change from v1, which priced the general portion at the pool minimum and could understate. Two seats at ₹5,000 and ₹6,000 now total ₹11,000, not ₹10,000. Cost of exactness: ranking ~30k seat rows for a 25-package month, ~6k for one package. Both are milliseconds on `idx_pkg_pool`. It also removed the gen/pkg/user pool arithmetic entirely, so this query is simpler than v1's, not more complex. ### 4b. Two guards, and what each catches `HAVING legs_priced = o.legs_count` — `v_package_option_leg` filters out flights that are switched off or past their sale cutoff, so those legs never reach the join. Without this the option would be priced on its surviving legs: a Delhi–Bangkok package with a dead return leg would list, priced on the outbound alone. `HAVING min_seats_priced = :seats` — a leg with fewer than `:seats` drawable seats yields a partial `cost_for_seats`. Without this, three requested seats on a leg holding two returns the price of two. `LEFT JOIN flight_day` — a flight-day with no sellable seats produces no row in the aggregate at all, so an inner join would drop that leg silently and both guards above would pass on the remainder. ### 4c. Parameters | name | rule | |---|---| | `:package_ids` | non-empty list of positive integers | | `:user_id` | non-negative integer. `0` = no agent; only shared pools count | | `:seats` | **integer >= 1.** `avg_seat_price` divides by it | | `:infants` | non-negative integer. Priced, never counted against seats | | `:from_date` / `:to_date` | `YYYY-MM-DD`, `to >= from` | | `:trip_type` | `NULL`, or one of `oneway` / `return` / `multicity` | Validate `adult`, `child` and `infant` as non-negative integers on the caller's side and derive `:seats = adult + child` before binding. A `:seats` of 0 divides by zero; a negative one produces nonsense that the `HAVING` will not catch. ### 4d. Caching Cache **this** result keyed on `(package_ids, month, seats, user_id)` with a short TTL. `user_id` must be in the key: two agents legitimately see different prices when one holds a reserved pool. **Do not cache the combined "starting from" against a flight-only key.** The combined figure depends on the package project's date-wise prices, so its cache key must include whatever version or effective-date identifies those prices. A flight-only key will serve a stale total after a package reprices. That cache belongs on the package side, where the price version is known. --- ## 5. Query B — final quote for one option Run immediately before showing a final price or taking payment. Same ranking as Query A, scoped to one option, and it **refuses to price an option it cannot fill**. ```sql SELECT x.leg_no, x.flight_id, x.travel_date, SUM(x.price) AS leg_seat_price, MAX(x.infant_price) * :infants AS leg_infant_price FROM ( SELECT l.leg_no, l.flight_id, l.travel_date, l.infant_price, p.price, ROW_NUMBER() OVER (PARTITION BY l.leg_no ORDER BY p.tier_rank, p.price, p.seat_id) AS rn FROM v_package_option_leg l JOIN v_package_seat_price p ON p.flight_id = l.flight_id AND p.travel_date = l.travel_date AND p.user_id IN (0, :user_id) WHERE l.option_id = :option_id ) x WHERE x.rn <= :seats GROUP BY x.leg_no, x.flight_id, x.travel_date HAVING COUNT(*) = :seats ORDER BY x.leg_no; ``` `HAVING COUNT(*) = :seats` drops any leg that cannot supply the full party. A leg that cannot be filled simply does not appear — which is why the row count alone is not a safe check, and why the wrapper below exists. ### 5a. Query B-verify — one row, two numbers to compare Counting rows in application code is a way to get this wrong. This returns a single row; reject the quote unless `legs_priced = legs_expected`. ```sql SELECT :option_id AS option_id, (SELECT legs_count FROM v_package_option WHERE option_id = :option_id) AS legs_expected, COUNT(*) AS legs_priced, SUM(t.leg_seat_price) AS total_seat_price, SUM(t.leg_infant_price) AS total_infant_price FROM ( ) t; ``` `legs_priced < legs_expected` means at least one leg cannot seat the party — for a paired `oneway` that is typically the return. **Do not quote, and do not take payment.** Re-run Query A for that package to show what is still available. ### 5b. Ordering parity `ORDER BY tier_rank, price, seat_id` must match the booking statement exactly, or the quote and the charge diverge. `seat_id` is the deterministic tie-break: without it two seats at the same tier and price can order differently between quote and booking. The booking side is an `UPDATE ... ORDER BY`, which cannot use a window function, so it expresses the same ordering with plain expressions. Both are generated from one shared fragment on the fdking-admin side. --- ## 6. Screen flow ``` Results page one Query A across all packages for the month (~775 rows) → cheapest flight option per package per date join the package project's date-wise prices → MIN(package_price + flight_price) per package → "Starting from" Package calendar one Query A for that package/month (~31 rows) join package price for every date → total per available date Date selected Query B + B-verify for the chosen option → reject unless legs_priced = legs_expected → final quote Booking transaction on the PRIMARY → atomically claim seats on every leg → roll back entirely if any leg falls short ``` Query A and Query B both read the replica, so both can be stale. Neither is a guarantee. The guarantee is the claim in the booking transaction, which re-checks under an `affectedRows` guard on the primary and rolls back the whole itinerary if any leg falls short. A half-booked multi-leg package is the failure mode worth engineering against. --- ## 7. What must exist before this runs On the fdking-admin side, in order: 1. `flight_ticket_prices.pkg_seat_price` — package price per seat, leaving `plan_1` intact. 2. **`reserveForPackage` rewritten to write it.** Today that function does `SET booked_status = 6, plan_1 = :price`, overwriting the seat's normal price. Until the reserve, release and booking paths are changed, `v_package_seat_price` reports package prices as if they were normal prices, and released seats carry the package price back into general sale. **The views will not behave as documented until this lands.** 3. `package_criteria`, `package_criteria_leg`, `package_option`, `package_option_leg`, fed by the package-sync API. 4. The four views: `v_package_option`, `v_package_option_leg`, `v_package_option_segment`, `v_package_seat_price`. ## 8. Open items - **Fields not in v2.** Baggage, fare rules, cancellation policy, terminals. Adding output columns later is safe; renaming or removing is not. - **Sort order.** Query A sorts by package then date. Say now if the results page wants something else by default.