-- =============================================================================
-- AeroResolve Master Data Seed Script
-- PostgreSQL 14+
-- =============================================================================

CREATE SCHEMA IF NOT EXISTS masters_lookup;
CREATE SCHEMA IF NOT EXISTS masters_rule_engine;
CREATE SCHEMA IF NOT EXISTS masters_action_builder;

-- 1. Action Categories
INSERT INTO masters_action_builder.tbl_action_categories (code, name, "displayOrder", "isActive")
SELECT code, name, displayOrder, true FROM (VALUES
  ('REFUNDS', 'Refunds', 1),
  ('CASH_COMPENSATION', 'Cash Compensation', 2),
  ('TRAVEL_CREDITS', 'Travel Credits', 3),
  ('LOYALTY_BENEFITS', 'Loyalty Benefits', 4),
  ('ACCOMMODATION', 'Accommodation', 5),
  ('MEALS', 'Meals', 6),
  ('TRANSPORTATION', 'Transportation', 7),
  ('REBOOKING_TRAVEL', 'Rebooking & Travel', 8),
  ('UPGRADES', 'Upgrades', 9),
  ('ANCILLARY_RECOVERY', 'Ancillary Recovery', 10),
  ('COMMUNICATION', 'Communication', 11),
  ('WORKFLOW', 'Workflow', 12),
  ('FINANCE', 'Finance', 13),
  ('SYSTEM_INTEGRATION', 'System Integration', 14)
) AS t(code, name, displayOrder)
WHERE NOT EXISTS (SELECT 1 FROM masters_action_builder.tbl_action_categories WHERE masters_action_builder.tbl_action_categories.code = t.code);

-- 2. Action Types
INSERT INTO masters_action_builder.tbl_action_types (code, "categoryId", name, "displayOrder", "isActive")
SELECT v.code, c.id, v.name, v.display_order, true
FROM (VALUES
  -- Refunds
  ('FULL_TICKET_REFUND', 'REFUNDS', 'Full Ticket Refund', 1),
  ('PARTIAL_TICKET_REFUND', 'REFUNDS', 'Partial Ticket Refund', 2),
  ('TAX_REFUND', 'REFUNDS', 'Tax Refund', 3),
  ('ANCILLARY_REFUND', 'REFUNDS', 'Ancillary Refund', 4),
  ('BAGGAGE_FEE_REFUND', 'REFUNDS', 'Baggage Fee Refund', 5),
  ('CABIN_DOWNGRADE_REFUND', 'REFUNDS', 'Cabin Downgrade Refund', 6),

  -- Cash Compensation
  ('REGULATORY_COMPENSATION', 'CASH_COMPENSATION', 'Regulatory Compensation', 1),
  ('AIRLINE_GOODWILL_CASH', 'CASH_COMPENSATION', 'Airline Goodwill Cash', 2),
  ('EX_GRATIA_COMPENSATION', 'CASH_COMPENSATION', 'Ex-Gratia Compensation', 3),

  -- Travel Credits
  ('TRAVEL_VOUCHER', 'TRAVEL_CREDITS', 'Travel Voucher', 1),
  ('FUTURE_TRAVEL_CREDIT', 'TRAVEL_CREDITS', 'Future Travel Credit', 2),
  ('PROMO_CODE', 'TRAVEL_CREDITS', 'Promo Code', 3),
  ('DISCOUNT_COUPON', 'TRAVEL_CREDITS', 'Discount Coupon', 4),

  -- Loyalty Benefits
  ('AWARD_MILES', 'LOYALTY_BENEFITS', 'Award Miles', 1),
  ('BONUS_MILES', 'LOYALTY_BENEFITS', 'Bonus Miles', 2),
  ('TIER_POINTS', 'LOYALTY_BENEFITS', 'Tier Points', 3),
  ('TIER_UPGRADE', 'LOYALTY_BENEFITS', 'Tier Upgrade', 4),

  -- Accommodation
  ('HOTEL_ACCOMMODATION', 'ACCOMMODATION', 'Hotel Accommodation', 1),
  ('AIRPORT_HOTEL', 'ACCOMMODATION', 'Airport Hotel', 2),

  -- Meals
  ('MEAL_VOUCHER', 'MEALS', 'Meal Voucher', 1),
  ('RESTAURANT_VOUCHER', 'MEALS', 'Restaurant Voucher', 2),
  ('REFRESHMENT_COUPON', 'MEALS', 'Refreshment Coupon', 3),

  -- Transportation
  ('TAXI', 'TRANSPORTATION', 'Taxi', 1),
  ('AIRPORT_TRANSFER', 'TRANSPORTATION', 'Airport Transfer', 2),
  ('BUS_TRANSFER', 'TRANSPORTATION', 'Bus Transfer', 3),
  ('TRAIN_TICKET', 'TRANSPORTATION', 'Train Ticket', 4),
  ('CHAUFFEUR_SERVICE', 'TRANSPORTATION', 'Chauffeur Service', 5),

  -- Rebooking & Travel
  ('AUTO_REBOOK', 'REBOOKING_TRAVEL', 'Auto Rebook', 1),
  ('PRIORITY_REBOOKING', 'REBOOKING_TRAVEL', 'Priority Rebooking', 2),
  ('OPEN_TICKET', 'REBOOKING_TRAVEL', 'Open Ticket', 3),
  ('ALTERNATE_AIRLINE', 'REBOOKING_TRAVEL', 'Alternate Airline', 4),

  -- Upgrades
  ('CABIN_UPGRADE', 'UPGRADES', 'Cabin Upgrade', 1),
  ('SEAT_UPGRADE', 'UPGRADES', 'Seat Upgrade', 2),
  ('LOUNGE_ACCESS', 'UPGRADES', 'Lounge Access', 3),
  ('FAST_TRACK_SECURITY', 'UPGRADES', 'Fast Track Security', 4),
  ('PRIORITY_BOARDING', 'UPGRADES', 'Priority Boarding', 5),

  -- Ancillary Recovery
  ('COMPLIMENTARY_WIFI', 'ANCILLARY_RECOVERY', 'Complimentary Wi-Fi', 1),
  ('FREE_BAGGAGE', 'ANCILLARY_RECOVERY', 'Free Baggage', 2),
  ('COMPLIMENTARY_MEAL', 'ANCILLARY_RECOVERY', 'Complimentary Meal', 3),
  ('SEAT_SELECTION', 'ANCILLARY_RECOVERY', 'Seat Selection', 4),
  ('CARBON_OFFSET_CREDIT', 'ANCILLARY_RECOVERY', 'Carbon Offset Credit', 5),

  -- Communication
  ('PASSENGER_NOTIFICATION', 'COMMUNICATION', 'Passenger Notification', 1),
  ('AGENT_NOTIFICATION', 'COMMUNICATION', 'Agent Notification', 2),
  ('MANAGEMENT_ALERT', 'COMMUNICATION', 'Management Alert', 3),

  -- Workflow
  ('AUTO_APPROVE', 'WORKFLOW', 'Auto Approve', 1),
  ('MANUAL_REVIEW', 'WORKFLOW', 'Manual Review', 2),
  ('ESCALATE', 'WORKFLOW', 'Escalate', 3),
  ('CREATE_CASE', 'WORKFLOW', 'Create Case', 4),
  ('HOLD_FOR_INVESTIGATION', 'WORKFLOW', 'Hold for Investigation', 5),

  -- Finance
  ('TRIGGER_PAYMENT', 'FINANCE', 'Trigger Payment', 1),
  ('GENERATE_CREDIT_NOTE', 'FINANCE', 'Generate Credit Note', 2),
  ('GENERATE_INVOICE', 'FINANCE', 'Generate Invoice', 3),
  ('WRITE_OFF', 'FINANCE', 'Write-off', 4),

  -- System Integration
  ('UPDATE_CRM', 'SYSTEM_INTEGRATION', 'Update CRM', 1),
  ('UPDATE_LOYALTY', 'SYSTEM_INTEGRATION', 'Update Loyalty', 2),
  ('UPDATE_PSS', 'SYSTEM_INTEGRATION', 'Update PSS', 3),
  ('CREATE_AUDIT_RECORD', 'SYSTEM_INTEGRATION', 'Create Audit Record', 4),
  ('TRIGGER_WEBHOOK_API', 'SYSTEM_INTEGRATION', 'Trigger Webhook / API', 5)
) AS v(code, cat_code, name, display_order)
JOIN masters_action_builder.tbl_action_categories c ON c.code = v.cat_code
WHERE NOT EXISTS (SELECT 1 FROM masters_action_builder.tbl_action_types WHERE masters_action_builder.tbl_action_types.code = v.code);

-- 3. Membership Tiers
INSERT INTO masters_lookup.tbl_membership_tiers (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Platinum', 'platinum'),
  ('Gold', 'gold'),
  ('Silver', 'silver'),
  ('Bronze', 'bronze'),
  ('Basic', 'basic'),
  ('Non-Member', 'non-member')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_membership_tiers WHERE masters_lookup.tbl_membership_tiers.value = t.value);

-- 4. Customer Values
INSERT INTO masters_lookup.tbl_customer_values (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('High Value', 'high'),
  ('Medium Value', 'medium'),
  ('Low Value', 'low')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_customer_values WHERE masters_lookup.tbl_customer_values.value = t.value);

-- 5. Regions
INSERT INTO masters_lookup.tbl_regions (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Global', 'global'),
  ('Americas', 'americas'),
  ('Europe', 'europe'),
  ('Middle East', 'middle-east'),
  ('Asia Pacific', 'asia-pacific'),
  ('Africa', 'africa')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_regions WHERE masters_lookup.tbl_regions.value = t.value);

-- 6. Trip Purposes
INSERT INTO masters_lookup.tbl_trip_purposes (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Business', 'business'),
  ('Leisure', 'leisure'),
  ('Corporate', 'corporate'),
  ('Government', 'government')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_trip_purposes WHERE masters_lookup.tbl_trip_purposes.value = t.value);

-- 7. Cabin Classes
INSERT INTO masters_lookup.tbl_cabin_classes (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('First Class', 'first-class'),
  ('Business Class', 'business-class'),
  ('Premium Economy', 'premium-economy'),
  ('Economy', 'economy')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_cabin_classes WHERE masters_lookup.tbl_cabin_classes.value = t.value);

-- 8. Passenger Types
INSERT INTO masters_lookup.tbl_passenger_types (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Adult', 'adult'),
  ('Child', 'child'),
  ('Infant', 'infant'),
  ('Senior Citizen', 'senior')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_passenger_types WHERE masters_lookup.tbl_passenger_types.value = t.value);

-- 9. Ancillary Purchases
INSERT INTO masters_lookup.tbl_ancillary_purchases (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Preferred Seat', 'preferred-seat'),
  ('Extra Legroom', 'extra-legroom'),
  ('Wi-Fi', 'wi-fi'),
  ('Lounge Access', 'lounge-access'),
  ('Priority Boarding', 'priority-boarding'),
  ('Fast Track', 'fast-track'),
  ('Paid Meal', 'paid-meal'),
  ('Special Meal', 'special-meal'),
  ('Extra Baggage', 'extra-baggage'),
  ('Upgrade Purchase', 'upgrade-purchase'),
  ('Airport Transfer', 'airport-transfer'),
  ('Chauffeur Service', 'chauffeur-service'),
  ('Sports Equipment', 'sports-equipment'),
  ('Musical Instrument', 'musical-instrument'),
  ('In-flight Entertainment', 'in-flight-entertainment'),
  ('Power Outlet', 'power-outlet'),
  ('Carbon Offset', 'carbon-offset')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_ancillary_purchases WHERE masters_lookup.tbl_ancillary_purchases.value = t.value);

-- 10. Revenue Segments
INSERT INTO masters_lookup.tbl_revenue_segments (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('High Value', 'high'),
  ('Medium Value', 'medium'),
  ('Low Value', 'low')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_revenue_segments WHERE masters_lookup.tbl_revenue_segments.value = t.value);

-- 11. Jurisdictions
INSERT INTO masters_lookup.tbl_jurisdictions (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('United Arab Emirates', 'uae'),
  ('European Union', 'eu'),
  ('United States', 'us'),
  ('United Kingdom', 'uk'),
  ('India', 'india'),
  ('Asia Pacific', 'apac'),
  ('Global', 'global')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_jurisdictions WHERE masters_lookup.tbl_jurisdictions.value = t.value);


-- 12. Rule Categories (Business Rule Categories)
DELETE FROM masters_rule_engine.tbl_rules_categories 
WHERE code NOT IN (
  'FLIGHT_OPERATIONS', 'BAGGAGE_ISSUES', 'CABIN_DOWNGRADES', 
  'ANCILLARY_FAILURES', 'DENIED_BOARDING', 'MISSED_CONNECTIONS'
);

INSERT INTO masters_rule_engine.tbl_rules_categories (code, name, description, "displayOrder", "isActive")
SELECT code, name, description, displayOrder, true FROM (VALUES
  ('FLIGHT_OPERATIONS', 'Flight Operations', 'Rules for flight disruptions, delays, cancellations, and diversions', 1),
  ('BAGGAGE_ISSUES', 'Baggage Issues', 'Rules for delayed, damaged, or lost baggage recovery', 2),
  ('CABIN_DOWNGRADES', 'Cabin Downgrades', 'Rules for involuntary cabin downgrades and seat reassignments', 3),
  ('ANCILLARY_FAILURES', 'Ancillary Failures', 'Rules for unfulfilled ancillary services like Wi-Fi, meals, seats', 4),
  ('DENIED_BOARDING', 'Denied Boarding', 'Rules for overbooking and involuntary denied boarding', 5),
  ('MISSED_CONNECTIONS', 'Missed Connections', 'Rules for tight connection failures and rebooking compensation', 6)
) AS t(code, name, description, displayOrder)
ON CONFLICT (code) DO UPDATE SET name = EXCLUDED.name, description = EXCLUDED.description;

-- 13. Booking Channels
INSERT INTO masters_lookup.tbl_booking_channels (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Airline Website', 'airline-website'),
  ('Airline Mobile App', 'airline-mobile-app'),
  ('Airport Ticket Counter', 'airport-ticket-counter'),
  ('Call Center', 'call-center'),
  ('Corporate Booking Tool', 'corporate-booking-tool'),
  ('Global Distribution System', 'global-distribution-system'),
  ('Online Travel Agency', 'online-travel-agency'),
  ('Travel Agent', 'travel-agent')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_booking_channels WHERE masters_lookup.tbl_booking_channels.value = t.value);

-- 14. Flight Types
INSERT INTO masters_lookup.tbl_flight_types (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Domestic', 'domestic'),
  ('International', 'international')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_flight_types WHERE masters_lookup.tbl_flight_types.value = t.value);

-- 15. Journey Types
INSERT INTO masters_lookup.tbl_journey_types (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('One Way', 'one-way'),
  ('Round Trip', 'round-trip'),
  ('Multi City', 'multi-city')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_journey_types WHERE masters_lookup.tbl_journey_types.value = t.value);

-- 16. Fare Flexibilities
INSERT INTO masters_lookup.tbl_fare_flexibilities (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Non Refundable', 'non-refundable'),
  ('Partially Refundable', 'partially-refundable'),
  ('Refundable', 'refundable'),
  ('Exchangeable', 'exchangeable'),
  ('Non Changeable', 'non-changeable')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_fare_flexibilities WHERE masters_lookup.tbl_fare_flexibilities.value = t.value);

-- 17. Carrier Types
INSERT INTO masters_lookup.tbl_carrier_types (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Operating Carrier', 'operating-carrier'),
  ('Marketing Carrier', 'marketing-carrier'),
  ('Partner Carrier', 'partner-carrier'),
  ('Regional Carrier', 'regional-carrier'),
  ('Low Cost Carrier', 'low-cost-carrier'),
  ('Full Service Carrier', 'full-service-carrier'),
  ('Charter Carrier', 'charter-carrier')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_carrier_types WHERE masters_lookup.tbl_carrier_types.value = t.value);

-- 18. Special Assistance Types
INSERT INTO masters_lookup.tbl_special_assistance_types (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Wheelchair Assistance', 'wheelchair-assistance'),
  ('Wheelchair Ramp', 'wheelchair-ramp'),
  ('Wheelchair Steps', 'wheelchair-steps'),
  ('Wheelchair Cabin', 'wheelchair-cabin'),
  ('Blind Passenger', 'blind-passenger'),
  ('Deaf Passenger', 'deaf-passenger'),
  ('Medical Assistance', 'medical-assistance'),
  ('Oxygen Required', 'oxygen-required'),
  ('Stretcher', 'stretcher'),
  ('Unaccompanied Minor', 'unaccompanied-minor'),
  ('Service Animal', 'service-animal'),
  ('Pregnant Passenger', 'pregnant-passenger'),
  ('Elderly Passenger', 'elderly-passenger'),
  ('Other', 'other')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_special_assistance_types WHERE masters_lookup.tbl_special_assistance_types.value = t.value);

-- 19. Delay Reasons
INSERT INTO masters_lookup.tbl_delay_reasons (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Air Traffic Control Restriction', 'air-traffic-control-restriction'),
  ('Aircraft Rotation', 'aircraft-rotation'),
  ('Airport Congestion', 'airport-congestion'),
  ('Crew Availability', 'crew-availability'),
  ('Customs Delay', 'customs-delay'),
  ('Fueling Delay', 'fueling-delay'),
  ('Late Arrival of Aircraft', 'late-arrival-of-aircraft'),
  ('Operational Decision', 'operational-decision'),
  ('Passenger Handling', 'passenger-handling'),
  ('Runway Closure', 'runway-closure'),
  ('Security', 'security'),
  ('Severe Weather', 'severe-weather'),
  ('Technical Fault', 'technical-fault'),
  ('Other', 'other')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_delay_reasons WHERE masters_lookup.tbl_delay_reasons.value = t.value);

-- 20. Delay Durations
INSERT INTO masters_lookup.tbl_delay_durations (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Less than 1 Hour', 'less-than-1-hour'),
  ('1-2 Hours', '1-2-hours'),
  ('2-3 Hours', '2-3-hours'),
  ('3-4 Hours', '3-4-hours'),
  ('More than 4 Hours', 'more-than-4-hours')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_delay_durations WHERE masters_lookup.tbl_delay_durations.value = t.value);

-- 21. Extraordinary Circumstances
INSERT INTO masters_lookup.tbl_extraordinary_circumstances (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Air Traffic Management Decision', 'air-traffic-management-decision'),
  ('Airport Closure', 'airport-closure'),
  ('Bird Strike', 'bird-strike'),
  ('Civil Unrest', 'civil-unrest'),
  ('Medical Emergency', 'medical-emergency'),
  ('Political Instability', 'political-instability'),
  ('Security Threat', 'security-threat'),
  ('Severe Weather', 'severe-weather'),
  ('Strike (External)', 'strike-external-'),
  ('War', 'war'),
  ('Other', 'other')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_extraordinary_circumstances WHERE masters_lookup.tbl_extraordinary_circumstances.value = t.value);

-- 22. Cancellation Reasons
INSERT INTO masters_lookup.tbl_cancellation_reasons (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Air Traffic Control Restriction', 'air-traffic-control-restriction'),
  ('Airport Closure', 'airport-closure'),
  ('Commercial Decision', 'commercial-decision'),
  ('Crew Availability', 'crew-availability'),
  ('Operational Decision', 'operational-decision'),
  ('Overbooking', 'overbooking'),
  ('Security', 'security'),
  ('Severe Weather', 'severe-weather'),
  ('Strike', 'strike'),
  ('Technical Fault', 'technical-fault')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_cancellation_reasons WHERE masters_lookup.tbl_cancellation_reasons.value = t.value);

-- 23. Diversion Reasons
INSERT INTO masters_lookup.tbl_diversion_reasons (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Airport Closure', 'airport-closure'),
  ('Destination Weather', 'destination-weather'),
  ('Fuel Emergency', 'fuel-emergency'),
  ('Medical Emergency', 'medical-emergency'),
  ('Runway Obstruction', 'runway-obstruction'),
  ('Security Threat', 'security-threat'),
  ('Technical Fault', 'technical-fault')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_diversion_reasons WHERE masters_lookup.tbl_diversion_reasons.value = t.value);

-- 24. Missed Connection Reasons
INSERT INTO masters_lookup.tbl_missed_connection_reasons (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Customs Delay', 'customs-delay'),
  ('Flight Delay', 'flight-delay'),
  ('Immigration Delay', 'immigration-delay'),
  ('Passenger Delay', 'passenger-delay'),
  ('Security Screening Delay', 'security-screening-delay')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_missed_connection_reasons WHERE masters_lookup.tbl_missed_connection_reasons.value = t.value);

-- 25. Compensation Eligibilities
INSERT INTO masters_lookup.tbl_compensation_eligibilities (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Eligible', 'eligible'),
  ('Not Eligible', 'not-eligible'),
  ('Requires Manual Review', 'requires-manual-review')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_compensation_eligibilities WHERE masters_lookup.tbl_compensation_eligibilities.value = t.value);

-- 26. Compensation Types
INSERT INTO masters_lookup.tbl_compensation_types (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Cash', 'cash'),
  ('Cheque', 'cheque'),
  ('Flight Voucher', 'flight-voucher'),
  ('Loyalty Miles', 'loyalty-miles'),
  ('Meal Voucher', 'meal-voucher'),
  ('Hotel Accommodation', 'hotel-accommodation'),
  ('Ground Transport', 'ground-transport')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_compensation_types WHERE masters_lookup.tbl_compensation_types.value = t.value);

-- 27. Refund Types
INSERT INTO masters_lookup.tbl_refund_types (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Full Refund', 'full-refund'),
  ('Partial Refund', 'partial-refund'),
  ('Future Travel Credit', 'future-travel-credit'),
  ('Travel Voucher', 'travel-voucher'),
  ('Tax Refund Only', 'tax-refund-only'),
  ('Telephone Reimbursement', 'telephone-reimbursement')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_refund_types WHERE masters_lookup.tbl_refund_types.value = t.value);

-- 28. Flight Disruption Types
INSERT INTO masters_lookup.tbl_flight_disruption_types (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Cancellation', 'cancellation'),
  ('Delay', 'delay'),
  ('Denied Boarding', 'denied-boarding'),
  ('Diversion', 'diversion'),
  ('Missed Connection', 'missed-connection')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_flight_disruption_types WHERE masters_lookup.tbl_flight_disruption_types.value = t.value);

-- 29. Airline Responsibilities
INSERT INTO masters_lookup.tbl_airline_responsibilities (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Airline Responsible', 'airline-responsible'),
  ('Airport Responsible', 'airport-responsible'),
  ('ATC Responsible', 'atc-responsible'),
  ('Passenger Responsible', 'passenger-responsible'),
  ('Shared Responsibility', 'shared-responsibility'),
  ('Third Party Responsible', 'third-party-responsible'),
  ('Snow', 'snow')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_airline_responsibilities WHERE masters_lookup.tbl_airline_responsibilities.value = t.value);

-- 30. Weather Conditions
INSERT INTO masters_lookup.tbl_weather_conditions (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Fog', 'fog'),
  ('Heavy Rain', 'heavy-rain'),
  ('Hurricane', 'hurricane'),
  ('Ice', 'ice'),
  ('Lightning', 'lightning'),
  ('Sandstorm', 'sandstorm'),
  ('Thunderstorm', 'thunderstorm')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_weather_conditions WHERE masters_lookup.tbl_weather_conditions.value = t.value);

-- 31. ATC Restrictions
INSERT INTO masters_lookup.tbl_atc_restrictions (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Airspace Closure', 'airspace-closure'),
  ('Flow Control', 'flow-control'),
  ('Ground Stop', 'ground-stop'),
  ('Slot Restriction', 'slot-restriction'),
  ('Traffic Congestion', 'traffic-congestion'),
  ('Navigation System', 'navigation-system')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_atc_restrictions WHERE masters_lookup.tbl_atc_restrictions.value = t.value);

-- 32. Technical Fault Categories
INSERT INTO masters_lookup.tbl_technical_fault_categories (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Aircraft Damage', 'aircraft-damage'),
  ('Avionics', 'avionics'),
  ('Cabin Systems', 'cabin-systems'),
  ('Engine', 'engine'),
  ('Hydraulic System', 'hydraulic-system'),
  ('Landing Gear', 'landing-gear'),
  ('Volcanic Ash', 'volcanic-ash'),
  ('Wind Shear', 'wind-shear')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_technical_fault_categories WHERE masters_lookup.tbl_technical_fault_categories.value = t.value);

-- 33. Operators
INSERT INTO masters_rule_engine.tbl_operators (code, name, symbol, "displayOrder", "isActive")
SELECT code, name, symbol, displayOrder, true FROM (VALUES
  ('EQ', 'Equals', '=', 1),
  ('NE', 'Not Equals', '!=', 2),
  ('GT', 'Greater Than', '>', 3),
  ('GTE', 'Greater Than or Equal', '>=', 4),
  ('LT', 'Less Than', '<', 5),
  ('LTE', 'Less Than or Equal', '<=', 6),
  ('BETWEEN', 'Between', 'BETWEEN', 7),
  ('CONTAINS', 'Contains', 'CONTAINS', 8),
  ('IN', 'In', 'IN', 9),
  ('IS_EMPTY', 'Is Empty', 'IS EMPTY', 10)
) AS t(code, name, symbol, displayOrder)
WHERE NOT EXISTS (SELECT 1 FROM masters_rule_engine.tbl_operators WHERE masters_rule_engine.tbl_operators.code = t.code);

-- 34. Refund Bases
INSERT INTO masters_lookup.tbl_refund_bases (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Base Fare', 'base-fare'),
  ('Taxes', 'taxes'),
  ('Total Fare', 'total-fare')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_refund_bases WHERE masters_lookup.tbl_refund_bases.value = t.value);

-- 35. Currencies
INSERT INTO masters_lookup.tbl_currencies (label, value, symbol, "isActive")
SELECT label, value, symbol, true FROM (VALUES
  ('United States Dollar', 'USD', '$'),
  ('Euro', 'EUR', '€'),
  ('British Pound', 'GBP', '£'),
  ('United Arab Emirates Dirham', 'AED', 'AED'),
  ('Saudi Riyal', 'SAR', 'SAR'),
  ('Indian Rupee', 'INR', '₹'),
  ('Japanese Yen', 'JPY', '¥'),
  ('Canadian Dollar', 'CAD', '$'),
  ('Australian Dollar', 'AUD', '$'),
  ('Swiss Franc', 'CHF', 'CHF'),
  ('Singapore Dollar', 'SGD', '$'),
  ('Qatari Riyal', 'QAR', 'QAR'),
  ('Kuwaiti Dinar', 'KWD', 'KWD'),
  ('Bahraini Dinar', 'BHD', 'BHD'),
  ('Omani Rial', 'OMR', 'OMR')
) AS t(label, value, symbol)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_currencies WHERE masters_lookup.tbl_currencies.value = t.value);

-- 36. Refund Methods
INSERT INTO masters_lookup.tbl_refund_methods (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Original Payment Method', 'original-payment-method'),
  ('Wallet', 'wallet'),
  ('Bank Transfer', 'bank-transfer'),
  ('Voucher', 'voucher')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_refund_methods WHERE masters_lookup.tbl_refund_methods.value = t.value);

-- 37. Amount Types
INSERT INTO masters_lookup.tbl_amount_types (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Fixed', 'fixed'),
  ('Percentage', 'percentage'),
  ('Formula', 'formula')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_amount_types WHERE masters_lookup.tbl_amount_types.value = t.value);

-- =============================================================================
-- 38. Condition Groups (Level 2)
-- =============================================================================
INSERT INTO masters_rule_engine.tbl_condition_groups (code, name, "displayOrder", "isActive")
SELECT code, name, displayOrder, true FROM (VALUES
  ('FLIGHT', 'Flight Details', 1),
  ('DISRUPTION', 'Disruption Details', 2),
  ('PASSENGER', 'Passenger Profile', 3),
  ('JOURNEY', 'Journey & Itinerary', 4),
  ('BOOKING', 'Booking & Fare', 5),
  ('BAGGAGE', 'Baggage Details', 6),
  ('CABIN', 'Cabin & Seating', 7),
  ('ANCILLARY', 'Ancillary Service Details', 8)
) AS t(code, name, displayOrder)
ON CONFLICT (code) DO UPDATE SET name = EXCLUDED.name;

-- =============================================================================
-- 40. Rule Category Groups Mapping (Level 1 -> Level 2)
-- =============================================================================
INSERT INTO masters_rule_engine.tbl_rule_category_groups ("ruleCategoryId", "conditionGroupId")
SELECT c.id, g.id
FROM (VALUES
  ('FLIGHT_OPERATIONS', 'FLIGHT'),
  ('FLIGHT_OPERATIONS', 'DISRUPTION'),
  ('FLIGHT_OPERATIONS', 'PASSENGER'),
  ('FLIGHT_OPERATIONS', 'JOURNEY'),
  ('FLIGHT_OPERATIONS', 'BOOKING'),
  ('BAGGAGE_ISSUES', 'PASSENGER'),
  ('BAGGAGE_ISSUES', 'JOURNEY'),
  ('BAGGAGE_ISSUES', 'BAGGAGE'),
  ('CABIN_DOWNGRADES', 'PASSENGER'),
  ('CABIN_DOWNGRADES', 'JOURNEY'),
  ('CABIN_DOWNGRADES', 'CABIN'),
  ('CABIN_DOWNGRADES', 'BOOKING'),
  ('ANCILLARY_FAILURES', 'PASSENGER'),
  ('ANCILLARY_FAILURES', 'ANCILLARY'),
  ('ANCILLARY_FAILURES', 'BOOKING'),
  ('DENIED_BOARDING', 'FLIGHT'),
  ('DENIED_BOARDING', 'PASSENGER'),
  ('DENIED_BOARDING', 'JOURNEY'),
  ('DENIED_BOARDING', 'BOOKING'),
  ('MISSED_CONNECTIONS', 'FLIGHT'),
  ('MISSED_CONNECTIONS', 'PASSENGER'),
  ('MISSED_CONNECTIONS', 'JOURNEY'),
  ('MISSED_CONNECTIONS', 'BOOKING')
) AS v(cat_code, group_code)
JOIN masters_rule_engine.tbl_rules_categories c ON c.code = v.cat_code
JOIN masters_rule_engine.tbl_condition_groups g ON g.code = v.group_code
WHERE NOT EXISTS (
  SELECT 1 FROM masters_rule_engine.tbl_rule_category_groups rcg 
  WHERE rcg."ruleCategoryId" = c.id AND rcg."conditionGroupId" = g.id
);

-- =============================================================================
-- 41. Condition Fields (Level 3)
-- =============================================================================
INSERT INTO masters_rule_engine.tbl_condition_fields ("groupId", code, name, "lookupTable", "dataType", "operatorType", "displayOrder", "isActive")
SELECT g.id, v.code, v.name, v.lookup_table, v.data_type, v.operator_type, v.display_order, true
FROM (VALUES
  -- FLIGHT
  ('FLIGHT', 'flight_type', 'Flight Type', 'tbl_flight_types', 'ENUM', 'COMPARISON', 1),
  ('FLIGHT', 'origin_airport', 'Origin Airport', NULL, 'STRING', 'TEXT', 2),
  ('FLIGHT', 'destination_airport', 'Destination Airport', NULL, 'STRING', 'TEXT', 3),
  ('FLIGHT', 'operating_carrier', 'Operating Carrier', 'tbl_carrier_types', 'ENUM', 'COMPARISON', 4),
  ('FLIGHT', 'marketing_carrier', 'Marketing Carrier', 'tbl_carrier_types', 'ENUM', 'COMPARISON', 5),
  ('FLIGHT', 'flight_distance', 'Flight Distance (km)', NULL, 'NUMBER', 'COMPARISON', 6),

  -- DISRUPTION
  ('DISRUPTION', 'delay_duration', 'Delay Duration (mins)', 'tbl_delay_durations', 'NUMBER', 'COMPARISON', 1),
  ('DISRUPTION', 'delay_reason', 'Delay Reason', 'tbl_delay_reasons', 'ENUM', 'SET', 2),
  ('DISRUPTION', 'cancellation_reason', 'Cancellation Reason', 'tbl_cancellation_reasons', 'ENUM', 'SET', 3),
  ('DISRUPTION', 'diversion_reason', 'Diversion Reason', 'tbl_diversion_reasons', 'ENUM', 'SET', 4),
  ('DISRUPTION', 'weather_condition', 'Weather Condition', 'tbl_weather_conditions', 'ENUM', 'SET', 5),
  ('DISRUPTION', 'atc_restriction', 'ATC Restriction', 'tbl_atc_restrictions', 'ENUM', 'SET', 6),
  ('DISRUPTION', 'technical_fault', 'Technical Fault', 'tbl_technical_fault_categories', 'ENUM', 'SET', 7),
  ('DISRUPTION', 'extraordinary_circumstance', 'Extraordinary Circumstance', 'tbl_extraordinary_circumstances', 'ENUM', 'SET', 8),
  ('DISRUPTION', 'airline_responsibility', 'Airline Responsibility', 'tbl_airline_responsibilities', 'ENUM', 'COMPARISON', 9),

  -- PASSENGER
  ('PASSENGER', 'passenger_type', 'Passenger Type', 'tbl_passenger_types', 'ENUM', 'SET', 1),
  ('PASSENGER', 'cabin_class', 'Cabin Class', 'tbl_cabin_classes', 'ENUM', 'SET', 2),
  ('PASSENGER', 'membership_tier', 'Membership Tier', 'tbl_membership_tiers', 'ENUM', 'SET', 3),
  ('PASSENGER', 'loyalty_tier', 'Loyalty Tier', 'tbl_membership_tiers', 'ENUM', 'SET', 4),
  ('PASSENGER', 'customer_value', 'Customer Value', 'tbl_customer_values', 'ENUM', 'COMPARISON', 5),
  ('PASSENGER', 'corporate_customer', 'Corporate Customer', NULL, 'BOOLEAN', 'BOOLEAN', 6),
  ('PASSENGER', 'group_booking', 'Group Booking', NULL, 'BOOLEAN', 'BOOLEAN', 7),
  ('PASSENGER', 'special_assistance', 'Special Assistance Type', 'tbl_special_assistance_types', 'ENUM', 'SET', 8),

  -- JOURNEY
  ('JOURNEY', 'journey_type', 'Journey Type', 'tbl_journey_types', 'ENUM', 'COMPARISON', 1),
  ('JOURNEY', 'protected_connection', 'Protected Connection', NULL, 'BOOLEAN', 'BOOLEAN', 2),
  ('JOURNEY', 'self_transfer', 'Self Transfer', NULL, 'BOOLEAN', 'BOOLEAN', 3),
  ('JOURNEY', 'number_of_segments', 'Number Of Segments', NULL, 'NUMBER', 'COMPARISON', 4),
  ('JOURNEY', 'final_destination_delay', 'Final Destination Delay (mins)', NULL, 'NUMBER', 'COMPARISON', 5),

  -- BOOKING
  ('BOOKING', 'booking_channel', 'Booking Channel', 'tbl_booking_channels', 'ENUM', 'SET', 1),
  ('BOOKING', 'refundable_ticket', 'Refundable Ticket', NULL, 'BOOLEAN', 'BOOLEAN', 2),
  ('BOOKING', 'fare_flexibility', 'Fare Flexibility', 'tbl_fare_flexibilities', 'ENUM', 'COMPARISON', 3),
  ('BOOKING', 'ticket_value', 'Ticket Value', NULL, 'NUMBER', 'COMPARISON', 4),
  ('BOOKING', 'ancillary_purchased', 'Ancillary Purchased', 'tbl_ancillary_purchases', 'ENUM', 'SET', 5),

  -- BAGGAGE
  ('BAGGAGE', 'bag_status', 'Bag Status', NULL, 'STRING', 'TEXT', 1),
  ('BAGGAGE', 'bag_type', 'Bag Type', NULL, 'STRING', 'TEXT', 2),
  ('BAGGAGE', 'bag_delay', 'Bag Delay Duration (hrs)', NULL, 'NUMBER', 'COMPARISON', 3),
  ('BAGGAGE', 'bag_value', 'Bag Value', NULL, 'NUMBER', 'COMPARISON', 4),
  ('BAGGAGE', 'pir_created', 'PIR Created', NULL, 'BOOLEAN', 'BOOLEAN', 5),

  -- CABIN
  ('CABIN', 'original_cabin', 'Original Cabin', 'tbl_cabin_classes', 'ENUM', 'COMPARISON', 1),
  ('CABIN', 'assigned_cabin', 'Assigned Cabin', 'tbl_cabin_classes', 'ENUM', 'COMPARISON', 2),
  ('CABIN', 'downgrade_level', 'Downgrade Level', NULL, 'NUMBER', 'COMPARISON', 3),
  ('CABIN', 'seat_type', 'Seat Type', NULL, 'STRING', 'TEXT', 4),

  -- ANCILLARY
  ('ANCILLARY', 'ancillary_type', 'Ancillary Type', 'tbl_ancillary_purchases', 'ENUM', 'SET', 1),
  ('ANCILLARY', 'ancillary_delivered', 'Ancillary Delivered', NULL, 'BOOLEAN', 'BOOLEAN', 2),
  ('ANCILLARY', 'service_value', 'Service Value', NULL, 'NUMBER', 'COMPARISON', 3)
) AS v(group_code, code, name, lookup_table, data_type, operator_type, display_order)
JOIN masters_rule_engine.tbl_condition_groups g ON g.code = v.group_code
ON CONFLICT (code) DO UPDATE SET
  name = EXCLUDED.name,
  "lookupTable" = EXCLUDED."lookupTable",
  "dataType" = EXCLUDED."dataType",
  "operatorType" = EXCLUDED."operatorType",
  "displayOrder" = EXCLUDED."displayOrder";

-- 38. Baggage Types
INSERT INTO masters_lookup.tbl_baggage_types (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Checked Bag', 'checked-bag'),
  ('Cabin Bag', 'cabin-bag'),
  ('Oversized Baggage', 'oversized-baggage'),
  ('Sports Equipment', 'sports-equipment'),
  ('Special Baggage', 'special-baggage')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_baggage_types WHERE masters_lookup.tbl_baggage_types.value = t.value);

-- 39. Compensation Rules
INSERT INTO masters_lookup.tbl_compensation_rules (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('EU261', 'eu261'),
  ('UK261', 'uk261'),
  ('APPR', 'appr'),
  ('DGCA', 'dgca'),
  ('DOT', 'dot'),
  ('Airline Policy', 'airline-policy')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_compensation_rules WHERE masters_lookup.tbl_compensation_rules.value = t.value);

-- 40. Approval Levels
INSERT INTO masters_lookup.tbl_approval_levels (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Supervisor', 'supervisor'),
  ('Manager', 'manager'),
  ('Director', 'director')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_approval_levels WHERE masters_lookup.tbl_approval_levels.value = t.value);

-- 41. Eligible Fare Types
INSERT INTO masters_lookup.tbl_eligible_fare_types (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Published Fares', 'published-fares'),
  ('Promo Fares', 'promo-fares')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_eligible_fare_types WHERE masters_lookup.tbl_eligible_fare_types.value = t.value);

-- 42. Applicable Products
INSERT INTO masters_lookup.tbl_applicable_products (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Flights', 'flights'),
  ('Ancillary', 'ancillary'),
  ('Lounge', 'lounge')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_applicable_products WHERE masters_lookup.tbl_applicable_products.value = t.value);

-- 43. Meal Types
INSERT INTO masters_lookup.tbl_meal_types (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Breakfast', 'breakfast'),
  ('Lunch', 'lunch'),
  ('Dinner', 'dinner'),
  ('Snack', 'snack'),
  ('Any', 'any')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_meal_types WHERE masters_lookup.tbl_meal_types.value = t.value);

-- 44. Airport Restrictions
INSERT INTO masters_lookup.tbl_airport_restrictions (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Departure', 'departure'),
  ('Transit', 'transit'),
  ('Arrival', 'arrival'),
  ('Any Airport', 'any-airport')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_airport_restrictions WHERE masters_lookup.tbl_airport_restrictions.value = t.value);

-- 45. Destination Types
INSERT INTO masters_lookup.tbl_destination_types (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Home', 'home'),
  ('Hotel', 'hotel'),
  ('Airport', 'airport'),
  ('City Center', 'city-center')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_destination_types WHERE masters_lookup.tbl_destination_types.value = t.value);

-- 46. Vehicle Categories
INSERT INTO masters_lookup.tbl_vehicle_categories (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Executive Sedan', 'executive-sedan'),
  ('Luxury SUV', 'luxury-suv'),
  ('Premium Van', 'premium-van')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_vehicle_categories WHERE masters_lookup.tbl_vehicle_categories.value = t.value);

-- 47. Cabin Preferences
INSERT INTO masters_lookup.tbl_cabin_preferences (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Same Cabin', 'same-cabin'),
  ('Upgrade if Available', 'upgrade-if-available'),
  ('Lowest Available', 'lowest-available')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_cabin_preferences WHERE masters_lookup.tbl_cabin_preferences.value = t.value);

-- 48. Airline Preferences
INSERT INTO masters_lookup.tbl_airline_preferences (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Same Airline', 'same-airline'),
  ('Alliance Airline', 'alliance-airline'),
  ('Any Partner', 'any-partner')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_airline_preferences WHERE masters_lookup.tbl_airline_preferences.value = t.value);

-- 49. Meal Categories
INSERT INTO masters_lookup.tbl_meal_categories (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Vegetarian', 'vegetarian'),
  ('Non-Vegetarian', 'non-vegetarian'),
  ('Vegan', 'vegan'),
  ('Child Meal', 'child-meal')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_meal_categories WHERE masters_lookup.tbl_meal_categories.value = t.value);

-- 50. Seat Categories
INSERT INTO masters_lookup.tbl_seat_categories (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Standard', 'standard'),
  ('Preferred', 'preferred'),
  ('Exit Row', 'exit-row'),
  ('Extra Legroom', 'extra-legroom')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_seat_categories WHERE masters_lookup.tbl_seat_categories.value = t.value);

-- 51. Compensation Bases
INSERT INTO masters_lookup.tbl_compensation_bases (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Regulatory Rule', 'regulatory-rule'),
  ('Airline Policy', 'airline-policy'),
  ('Percentage Difference', 'percentage-difference')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_compensation_bases WHERE masters_lookup.tbl_compensation_bases.value = t.value);

-- 52. Tax Types
INSERT INTO masters_lookup.tbl_tax_types (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Airport Tax', 'airport-tax'),
  ('Fuel Surcharge', 'fuel-surcharge'),
  ('Government Tax', 'government-tax'),
  ('Service Tax', 'service-tax'),
  ('VAT', 'vat'),
  ('GST', 'gst'),
  ('Airport Development Fee', 'airport-development-fee')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_tax_types WHERE masters_lookup.tbl_tax_types.value = t.value);

-- 53. Room Types
INSERT INTO masters_lookup.tbl_room_types (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Single', 'single'),
  ('Double', 'double'),
  ('Twin', 'twin'),
  ('Family', 'family')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_room_types WHERE masters_lookup.tbl_room_types.value = t.value);

-- 54. Hotel Tiers

INSERT INTO masters_lookup.tbl_hotel_tiers (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('3 Star', '3-star'),
  ('4 Star', '4-star'),
  ('5 Star', '5-star')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_hotel_tiers WHERE masters_lookup.tbl_hotel_tiers.value = t.value);

-- 55. Hotel Categories

INSERT INTO masters_lookup.tbl_hotel_categories (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Airport Hotel', 'airport-hotel'),
  ('Transit Hotel', 'transit-hotel')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_hotel_categories WHERE masters_lookup.tbl_hotel_categories.value = t.value);

-- 56. Hotel Chains

INSERT INTO masters_lookup.tbl_hotel_chains (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Hilton', 'hilton'),
  ('Marriott', 'marriott'),
  ('IHG', 'ihg'),
  ('Hyatt', 'hyatt'),
  ('Radisson', 'radisson')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_hotel_chains WHERE masters_lookup.tbl_hotel_chains.value = t.value);

-- 57. Loyalty Programs
INSERT INTO masters_lookup.tbl_loyalty_programs (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('AeroMiles', 'aeromiles'),
  ('SkyRewards', 'skyrewards'),
  ('StarAlliance', 'staralliance')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_loyalty_programs WHERE masters_lookup.tbl_loyalty_programs.value = t.value);

-- 58. Promotion Codes
INSERT INTO masters_lookup.tbl_promotion_codes (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('SUMMER2026', 'summer2026'),
  ('APOLOGY10', 'apology10'),
  ('FESTIVE25', 'festive25')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_promotion_codes WHERE masters_lookup.tbl_promotion_codes.value = t.value);

-- 59. Goodwill Reasons
INSERT INTO masters_lookup.tbl_goodwill_reasons (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Service Failure', 'service-failure'),
  ('Customer Retention', 'customer-retention'),
  ('Operational Delay', 'operational-delay')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_goodwill_reasons WHERE masters_lookup.tbl_goodwill_reasons.value = t.value);

-- 60. Routes
INSERT INTO masters_lookup.tbl_routes (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Airport to City Center', 'airport-city'),
  ('City Center to Airport', 'city-airport'),
  ('Airport to Terminal 2', 'airport-t2')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_routes WHERE masters_lookup.tbl_routes.value = t.value);

-- 61. Refund Components
INSERT INTO masters_lookup.tbl_refund_components (label, value, "isActive")
SELECT label, value, true FROM (VALUES
  ('Base Fare', 'base-fare'),
  ('Taxes', 'taxes'),
  ('Ancillary', 'ancillary'),
  ('Seat Fee', 'seat-fee'),
  ('Baggage Fee', 'baggage-fee'),
  ('Service Fee', 'service-fee')
) AS t(label, value)
WHERE NOT EXISTS (SELECT 1 FROM masters_lookup.tbl_refund_components WHERE masters_lookup.tbl_refund_components.value = t.value);