940 lines
42 KiB
SQL
940 lines
42 KiB
SQL
-- =============================================================================
|
|
-- 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);
|