-- Seed reference data, FAQs, and sample fares (run after schema or migrate-v2)
-- All prices in Nigerian Naira (NGN)

INSERT INTO cabin_classes (code, name, description, baggage_kg, sort_order) VALUES
  ('economy', 'Economy', 'Standard seating with essential amenities', 23, 1),
  ('premium_economy', 'Premium Economy', 'Extra legroom and enhanced meal service', 25, 2),
  ('business', 'Business', 'Lie-flat seats and lounge access', 32, 3),
  ('first', 'First Class', 'Private suites and premium concierge', 40, 4)
ON CONFLICT (code) DO NOTHING;

INSERT INTO airlines (name, iata_code, country, rating) VALUES
  ('Air Peace', 'P4', 'Nigeria', 4.5),
  ('Arik Air', 'W3', 'Nigeria', 4.2),
  ('Ibom Air', 'QI', 'Nigeria', 4.4),
  ('Dana Air', 'D9', 'Nigeria', 4.0)
ON CONFLICT (iata_code) DO NOTHING;

INSERT INTO airports (iata_code, name, city, country, timezone) VALUES
  ('LOS', 'Murtala Muhammed International', 'Lagos', 'Nigeria', 'Africa/Lagos'),
  ('ABV', 'Nnamdi Azikiwe International', 'Abuja', 'Nigeria', 'Africa/Lagos'),
  ('PHC', 'Port Harcourt International', 'Port Harcourt', 'Nigeria', 'Africa/Lagos'),
  ('KAN', 'Mallam Aminu Kano International', 'Kano', 'Nigeria', 'Africa/Lagos'),
  ('ENU', 'Akanu Ibiam International', 'Enugu', 'Nigeria', 'Africa/Lagos'),
  ('LHR', 'London Heathrow', 'London', 'United Kingdom', 'Europe/London')
ON CONFLICT (iata_code) DO NOTHING;

INSERT INTO aircraft_types (manufacturer, model, total_seats)
SELECT v.manufacturer, v.model, v.total_seats
FROM (VALUES
  ('Boeing', '737-800', 180),
  ('Airbus', 'A320neo', 180),
  ('Boeing', '787 Dreamliner', 242)
) AS v(manufacturer, model, total_seats)
WHERE NOT EXISTS (
  SELECT 1 FROM aircraft_types a WHERE a.manufacturer = v.manufacturer AND a.model = v.model
);

INSERT INTO promotions (code, description, discount_percent, discount_amount, min_spend, valid_until, max_uses) VALUES
  ('SKYBOOK10', '10% off your first booking', 10, NULL, 50000, NOW() + INTERVAL '1 year', 1000),
  ('FLYSAVE5K', '₦5,000 off bookings over ₦300,000', NULL, 5000, 300000, NOW() + INTERVAL '6 months', 500)
ON CONFLICT (code) DO NOTHING;

UPDATE promotions SET discount_amount = 5000, min_spend = 300000, description = '₦5,000 off bookings over ₦300,000'
WHERE code = 'FLYSAVE5K' AND discount_amount IS NULL;

INSERT INTO faq_categories (name, slug, sort_order) VALUES
  ('Booking & Reservations', 'booking', 1),
  ('Payments & Refunds', 'payments', 2),
  ('Flights & Check-in', 'flights', 3),
  ('Account & Support', 'account', 4)
ON CONFLICT (slug) DO NOTHING;

INSERT INTO faqs (category_id, question, answer, keywords, sort_order)
SELECT c.id, q, a, k, s FROM (VALUES
  ('booking', 'How do I book a flight?', 'Search for flights on the homepage, select your route and dates, choose a cabin class, enter passenger details, and confirm your booking. You will receive a confirmation email with your booking reference.', 'book,reserve,flight,how', 1),
  ('booking', 'Can I book for multiple passengers?', 'Yes. During checkout you can add up to 10 passengers per booking. Enter each traveler''s full name and details as they appear on their passport or NIN.', 'multiple,passengers,group,family', 2),
  ('booking', 'How do I cancel my booking?', 'Go to My Bookings, select your reservation, and click Cancel. Refund eligibility depends on your fare type — refundable fares receive a full refund within 24 hours.', 'cancel,refund,change', 3),
  ('payments', 'What payment methods do you accept?', 'We accept Visa, Mastercard, Verve, bank transfer, USSD, and Paystack. All prices are shown in Nigerian Naira (₦).', 'payment,card,verve,paystack,bank,ussd,naira', 4),
  ('payments', 'When will I be charged?', 'Your card or account is charged immediately upon booking confirmation in Naira. You will see the charge as SkyBook on your statement.', 'charge,bill,payment,when,naira', 5),
  ('payments', 'How do promo codes work?', 'Enter your promo code at checkout. Valid codes apply automatically if your booking meets the minimum spend requirement in Naira.', 'promo,discount,code,coupon', 6),
  ('flights', 'When does online check-in open?', 'Online check-in opens 24 hours before departure and closes 2 hours before for international flights.', 'check-in,online,boarding pass', 7),
  ('flights', 'What is my baggage allowance?', 'Economy: 23kg checked + 7kg carry-on. Premium Economy: 25kg. Business: 32kg. First Class: 40kg. See your booking confirmation for exact details.', 'baggage,luggage,kg,allowance', 8),
  ('flights', 'Can I change my flight date?', 'Date changes are permitted on flexible fares. Go to My Bookings and select Modify. Change fees may apply for non-refundable tickets.', 'change,date,modify,reschedule', 9),
  ('account', 'How do I create an account?', 'Click Register on the homepage, enter your name, email, and password. You can also book as a guest and create an account later.', 'register,sign up,account,create', 10),
  ('account', 'I forgot my password', 'Click Forgot Password on the login page. If you need help, chat with our support bot or contact an agent.', 'password,reset,forgot,login', 11),
  ('account', 'How do I contact support?', 'Use the chat widget on any page to ask questions instantly. For complex issues, our team will follow up within 24 hours.', 'support,help,contact,chat,agent', 12)
) AS v(slug, q, a, k, s)
JOIN faq_categories c ON c.slug = v.slug
WHERE NOT EXISTS (SELECT 1 FROM faqs WHERE question = v.q);

UPDATE flights f SET airline_id = a.id
FROM airlines a WHERE f.airline ILIKE '%' || a.name || '%' AND f.airline_id IS NULL;

UPDATE flights SET airline_id = (SELECT id FROM airlines WHERE iata_code = 'P4' LIMIT 1)
WHERE airline_id IS NULL;

UPDATE flights f SET origin_airport_id = ap.id
FROM airports ap WHERE f.origin ILIKE '%' || ap.iata_code || '%' AND f.origin_airport_id IS NULL;

UPDATE flights f SET destination_airport_id = ap.id
FROM airports ap WHERE f.destination ILIKE '%' || ap.iata_code || '%' AND f.destination_airport_id IS NULL;

UPDATE flights SET duration_minutes = EXTRACT(EPOCH FROM (arrival_time - departure_time)) / 60
WHERE duration_minutes IS NULL;

INSERT INTO flight_fares (flight_id, cabin_class_id, price, seats_total, seats_available, is_refundable, baggage_kg)
SELECT f.id, cc.id,
  CASE cc.code
    WHEN 'economy' THEN f.price
    WHEN 'premium_economy' THEN ROUND(f.price * 1.4, 0)
    WHEN 'business' THEN ROUND(f.price * 2.5, 0)
    WHEN 'first' THEN ROUND(f.price * 4.0, 0)
  END,
  CASE cc.code WHEN 'economy' THEN 120 WHEN 'premium_economy' THEN 30 WHEN 'business' THEN 20 WHEN 'first' THEN 10 END,
  CASE cc.code WHEN 'economy' THEN 120 WHEN 'premium_economy' THEN 30 WHEN 'business' THEN 20 WHEN 'first' THEN 10 END,
  cc.code IN ('business', 'first'),
  cc.baggage_kg
FROM flights f CROSS JOIN cabin_classes cc
ON CONFLICT (flight_id, cabin_class_id) DO NOTHING;

UPDATE payments SET currency = 'NGN' WHERE currency IS NULL OR currency = 'USD';