-- =============================================================================
-- SkyBook — COMPLETE DATABASE SCRIPT (phpPgAdmin copy & paste)
-- =============================================================================
--
-- HOW TO RUN (important — avoids "must be member of role" errors):
--   1. cPanel → PostgreSQL Databases → note your DB name (e.g. myczroxg_flight)
--   2. Open phpPgAdmin and log in as myczroxg_admin (your APP database user)
--      NOT the main cPanel account unless that is the only option.
--   3. Select database myczroxg_flight → SQL → paste ALL of this file → Execute
--
-- This script does NOT use ALTER OWNER (shared hosting blocks that).
-- Grants at the end are optional — skip section 9 if you run as myczroxg_admin.
--
-- Safe to re-run: uses IF NOT EXISTS, ON CONFLICT, and duplicate_column guards.
-- =============================================================================

-- ─── 1. CORE TABLES (create if missing) ─────────────────────────────────────

CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  role VARCHAR(20) NOT NULL DEFAULT 'user' CHECK (role IN ('admin', 'staff', 'user')),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS flights (
  id SERIAL PRIMARY KEY,
  flight_number VARCHAR(20) NOT NULL,
  airline VARCHAR(100) NOT NULL,
  origin VARCHAR(100) NOT NULL,
  destination VARCHAR(100) NOT NULL,
  departure_time TIMESTAMP NOT NULL,
  arrival_time TIMESTAMP NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  seats_total INTEGER NOT NULL DEFAULT 180,
  seats_available INTEGER NOT NULL DEFAULT 180,
  status VARCHAR(20) NOT NULL DEFAULT 'scheduled'
    CHECK (status IN ('scheduled', 'boarding', 'departed', 'cancelled', 'delayed')),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS bookings (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  flight_id INTEGER NOT NULL REFERENCES flights(id),
  booking_reference VARCHAR(20) UNIQUE NOT NULL,
  passenger_name VARCHAR(255) NOT NULL,
  passenger_email VARCHAR(255) NOT NULL,
  seat_count INTEGER NOT NULL DEFAULT 1,
  total_price DECIMAL(10, 2) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'confirmed', 'cancelled', 'completed', 'checked_in')),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ─── 2. NEW v2 TABLES ───────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS user_profiles (
  id SERIAL PRIMARY KEY,
  user_id INTEGER UNIQUE NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  phone VARCHAR(30),
  date_of_birth DATE,
  nationality VARCHAR(100),
  passport_number VARCHAR(50),
  address_line TEXT,
  city VARCHAR(100),
  country VARCHAR(100),
  preferred_cabin VARCHAR(30) DEFAULT 'economy',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS airlines (
  id SERIAL PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  iata_code VARCHAR(3) UNIQUE,
  country VARCHAR(100),
  logo_url TEXT,
  rating DECIMAL(2,1) DEFAULT 4.0,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS airports (
  id SERIAL PRIMARY KEY,
  iata_code VARCHAR(3) UNIQUE NOT NULL,
  name VARCHAR(200) NOT NULL,
  city VARCHAR(100) NOT NULL,
  country VARCHAR(100) NOT NULL,
  timezone VARCHAR(50) DEFAULT 'UTC',
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS cabin_classes (
  id SERIAL PRIMARY KEY,
  code VARCHAR(30) UNIQUE NOT NULL,
  name VARCHAR(80) NOT NULL,
  description TEXT,
  baggage_kg INTEGER DEFAULT 23,
  sort_order INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS aircraft_types (
  id SERIAL PRIMARY KEY,
  manufacturer VARCHAR(100),
  model VARCHAR(100) NOT NULL,
  total_seats INTEGER DEFAULT 180,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS flight_fares (
  id SERIAL PRIMARY KEY,
  flight_id INTEGER NOT NULL REFERENCES flights(id) ON DELETE CASCADE,
  cabin_class_id INTEGER NOT NULL REFERENCES cabin_classes(id),
  price DECIMAL(10, 2) NOT NULL,
  seats_total INTEGER NOT NULL DEFAULT 60,
  seats_available INTEGER NOT NULL DEFAULT 60,
  is_refundable BOOLEAN DEFAULT FALSE,
  baggage_kg INTEGER,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(flight_id, cabin_class_id)
);

CREATE TABLE IF NOT EXISTS promotions (
  id SERIAL PRIMARY KEY,
  code VARCHAR(30) UNIQUE NOT NULL,
  description TEXT,
  discount_percent DECIMAL(5,2),
  discount_amount DECIMAL(10,2),
  min_spend DECIMAL(10,2) DEFAULT 0,
  valid_from TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  valid_until TIMESTAMP,
  max_uses INTEGER,
  uses_count INTEGER DEFAULT 0,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS booking_passengers (
  id SERIAL PRIMARY KEY,
  booking_id INTEGER NOT NULL REFERENCES bookings(id) ON DELETE CASCADE,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  email VARCHAR(255),
  date_of_birth DATE,
  passport_number VARCHAR(50),
  nationality VARCHAR(100),
  seat_number VARCHAR(10),
  cabin_class VARCHAR(30) DEFAULT 'economy',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS booking_status_log (
  id SERIAL PRIMARY KEY,
  booking_id INTEGER NOT NULL REFERENCES bookings(id) ON DELETE CASCADE,
  old_status VARCHAR(20),
  new_status VARCHAR(20) NOT NULL,
  changed_by INTEGER REFERENCES users(id),
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS payments (
  id SERIAL PRIMARY KEY,
  booking_id INTEGER NOT NULL REFERENCES bookings(id) ON DELETE CASCADE,
  amount DECIMAL(10, 2) NOT NULL,
  currency VARCHAR(3) DEFAULT 'NGN',
  method VARCHAR(30) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'completed', 'failed', 'refunded')),
  transaction_ref VARCHAR(100) UNIQUE,
  paid_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS faq_categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100) UNIQUE NOT NULL,
  sort_order INTEGER DEFAULT 0,
  is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE IF NOT EXISTS faqs (
  id SERIAL PRIMARY KEY,
  category_id INTEGER REFERENCES faq_categories(id) ON DELETE SET NULL,
  question TEXT NOT NULL,
  answer TEXT NOT NULL,
  keywords TEXT,
  sort_order INTEGER DEFAULT 0,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS chat_sessions (
  id SERIAL PRIMARY KEY,
  session_token VARCHAR(64) UNIQUE NOT NULL,
  user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
  guest_name VARCHAR(150),
  guest_email VARCHAR(255),
  ip_hash VARCHAR(64),
  user_agent TEXT,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  last_active_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS conversations (
  id SERIAL PRIMARY KEY,
  chat_session_id INTEGER REFERENCES chat_sessions(id) ON DELETE SET NULL,
  user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
  guest_name VARCHAR(150),
  guest_email VARCHAR(255),
  subject VARCHAR(255) DEFAULT 'General enquiry',
  status VARCHAR(20) NOT NULL DEFAULT 'open'
    CHECK (status IN ('open', 'pending', 'resolved', 'closed')),
  priority VARCHAR(20) DEFAULT 'normal'
    CHECK (priority IN ('low', 'normal', 'high', 'urgent')),
  assigned_to INTEGER REFERENCES users(id) ON DELETE SET NULL,
  channel VARCHAR(20) DEFAULT 'chat' CHECK (channel IN ('chat', 'email', 'phone')),
  needs_human BOOLEAN DEFAULT FALSE,
  last_message_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS conversation_messages (
  id SERIAL PRIMARY KEY,
  conversation_id INTEGER NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  sender_type VARCHAR(20) NOT NULL
    CHECK (sender_type IN ('guest', 'user', 'bot', 'admin', 'system')),
  sender_user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
  content TEXT NOT NULL,
  metadata JSONB DEFAULT '{}',
  is_read BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ─── 3. UPGRADE EXISTING flights TABLE ──────────────────────────────────────

DO $$ BEGIN ALTER TABLE flights ADD COLUMN airline_id INTEGER REFERENCES airlines(id); EXCEPTION WHEN duplicate_column THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE flights ADD COLUMN origin_airport_id INTEGER REFERENCES airports(id); EXCEPTION WHEN duplicate_column THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE flights ADD COLUMN destination_airport_id INTEGER REFERENCES airports(id); EXCEPTION WHEN duplicate_column THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE flights ADD COLUMN aircraft_type_id INTEGER REFERENCES aircraft_types(id); EXCEPTION WHEN duplicate_column THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE flights ADD COLUMN duration_minutes INTEGER; EXCEPTION WHEN duplicate_column THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE flights ADD COLUMN terminal VARCHAR(20); EXCEPTION WHEN duplicate_column THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE flights ADD COLUMN gate VARCHAR(20); EXCEPTION WHEN duplicate_column THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE flights ADD COLUMN is_refundable BOOLEAN DEFAULT FALSE; EXCEPTION WHEN duplicate_column THEN NULL; END $$;

-- Unique constraint on flight_number + departure_time (skip if already exists)
DO $$ BEGIN
  ALTER TABLE flights ADD CONSTRAINT flights_flight_number_departure_time_key UNIQUE (flight_number, departure_time);
EXCEPTION WHEN duplicate_table OR duplicate_object THEN NULL;
END $$;

-- ─── 4. UPGRADE EXISTING bookings TABLE ─────────────────────────────────────

DO $$ BEGIN ALTER TABLE bookings ADD COLUMN flight_fare_id INTEGER REFERENCES flight_fares(id); EXCEPTION WHEN duplicate_column THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE bookings ADD COLUMN contact_name VARCHAR(255); EXCEPTION WHEN duplicate_column THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE bookings ADD COLUMN contact_email VARCHAR(255); EXCEPTION WHEN duplicate_column THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE bookings ADD COLUMN contact_phone VARCHAR(30); EXCEPTION WHEN duplicate_column THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE bookings ADD COLUMN cabin_class VARCHAR(30) DEFAULT 'economy'; EXCEPTION WHEN duplicate_column THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE bookings ADD COLUMN trip_type VARCHAR(20) DEFAULT 'one_way'; EXCEPTION WHEN duplicate_column THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE bookings ADD COLUMN subtotal DECIMAL(10,2); EXCEPTION WHEN duplicate_column THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE bookings ADD COLUMN discount_amount DECIMAL(10,2) DEFAULT 0; EXCEPTION WHEN duplicate_column THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE bookings ADD COLUMN promotion_id INTEGER REFERENCES promotions(id); EXCEPTION WHEN duplicate_column THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE bookings ADD COLUMN payment_status VARCHAR(20) DEFAULT 'pending'; EXCEPTION WHEN duplicate_column THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE bookings ADD COLUMN payment_method VARCHAR(30); EXCEPTION WHEN duplicate_column THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE bookings ADD COLUMN special_requests TEXT; EXCEPTION WHEN duplicate_column THEN NULL; END $$;
DO $$ BEGIN ALTER TABLE bookings ADD COLUMN check_in_status VARCHAR(20) DEFAULT 'not_started'; EXCEPTION WHEN duplicate_column THEN NULL; END $$;

-- Backfill new columns from legacy data
UPDATE bookings SET contact_name = passenger_name WHERE contact_name IS NULL;
UPDATE bookings SET contact_email = passenger_email WHERE contact_email IS NULL;
UPDATE bookings SET subtotal = total_price WHERE subtotal IS NULL;
UPDATE bookings SET payment_status = 'paid' WHERE payment_status IS NULL AND status IN ('confirmed', 'completed', 'checked_in');
UPDATE bookings SET payment_status = 'pending' WHERE payment_status IS NULL;

-- ─── 5. INDEXES ─────────────────────────────────────────────────────────────

CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_flights_departure ON flights(departure_time);
CREATE INDEX IF NOT EXISTS idx_flights_route ON flights(origin, destination);
CREATE INDEX IF NOT EXISTS idx_flight_fares_flight ON flight_fares(flight_id);
CREATE INDEX IF NOT EXISTS idx_bookings_user_id ON bookings(user_id);
CREATE INDEX IF NOT EXISTS idx_bookings_flight_id ON bookings(flight_id);
CREATE INDEX IF NOT EXISTS idx_bookings_reference ON bookings(booking_reference);
CREATE INDEX IF NOT EXISTS idx_booking_passengers_booking ON booking_passengers(booking_id);
CREATE INDEX IF NOT EXISTS idx_payments_booking ON payments(booking_id);
CREATE INDEX IF NOT EXISTS idx_faqs_category ON faqs(category_id);
CREATE INDEX IF NOT EXISTS idx_chat_sessions_token ON chat_sessions(session_token);
CREATE INDEX IF NOT EXISTS idx_conversations_user ON conversations(user_id);
CREATE INDEX IF NOT EXISTS idx_conversations_status ON conversations(status);
CREATE INDEX IF NOT EXISTS idx_messages_conversation ON conversation_messages(conversation_id);
CREATE INDEX IF NOT EXISTS idx_messages_created ON conversation_messages(created_at);

-- ─── 6. SEED REFERENCE DATA ─────────────────────────────────────────────────

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.', '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);

-- ─── 7. SEED DEMO USERS (only if users table is empty) ──────────────────────
-- Passwords: admin123 / staff123 / user123 — change after first login!

INSERT INTO users (email, password_hash, name, role)
SELECT v.email, v.password_hash, v.name, v.role
FROM (VALUES
  ('admin@flightbooking.com', '$2a$10$4961KDeQ0/rJD6L54FYUk.qVaaupOooZvKkXWVOFl01nwtBxaAWAu', 'System Admin', 'admin'),
  ('staff@flightbooking.com', '$2a$10$GLCo64GDnrZzwayAzS.DrORqaMvd3w4TLe7zKCdFEYzGOeh2oyqBS', 'Staff Member', 'staff'),
  ('user@flightbooking.com',  '$2a$10$njSvMer7pr8hkq348SFgiOKQ5zsSTfG2mksDQoXsNQEwqKU6jiiJG', 'Demo User', 'user')
) AS v(email, password_hash, name, role)
WHERE NOT EXISTS (SELECT 1 FROM users LIMIT 1);

-- ─── 8. SEED SAMPLE FLIGHTS (only if none exist) ────────────────────────────

INSERT INTO flights (flight_number, airline, origin, destination, departure_time, arrival_time, price, seats_total, seats_available, duration_minutes)
SELECT v.flight_number, v.airline, v.origin, v.destination, v.departure_time, v.arrival_time, v.price, 180, 180, v.duration_minutes
FROM (VALUES
  ('AP101', 'Air Peace',   'Lagos (LOS)',           'Abuja (ABV)',             NOW() + INTERVAL '3 days',  NOW() + INTERVAL '3 days 1 hour 15 minutes',  85000, 75),
  ('AP102', 'Air Peace',   'Abuja (ABV)',           'Lagos (LOS)',             NOW() + INTERVAL '5 days',  NOW() + INTERVAL '5 days 1 hour 15 minutes',  92000, 75),
  ('AR201', 'Arik Air',    'Lagos (LOS)',           'Port Harcourt (PHC)',     NOW() + INTERVAL '2 days',  NOW() + INTERVAL '2 days 1 hour',           75000, 60),
  ('AR202', 'Arik Air',    'Port Harcourt (PHC)',   'Lagos (LOS)',             NOW() + INTERVAL '4 days',  NOW() + INTERVAL '4 days 1 hour',           78000, 60),
  ('IB301', 'Ibom Air',    'Lagos (LOS)',           'Enugu (ENU)',             NOW() + INTERVAL '1 day',   NOW() + INTERVAL '1 day 1 hour 15 minutes',   68000, 75),
  ('IB302', 'Ibom Air',    'Enugu (ENU)',           'Lagos (LOS)',             NOW() + INTERVAL '3 days',  NOW() + INTERVAL '3 days 1 hour 15 minutes',  70000, 75),
  ('DN401', 'Dana Air',    'Lagos (LOS)',           'London (LHR)',            NOW() + INTERVAL '8 days',  NOW() + INTERVAL '8 days 6 hours 30 minutes', 950000, 390),
  ('DN402', 'Dana Air',    'London (LHR)',          'Lagos (LOS)',             NOW() + INTERVAL '10 days', NOW() + INTERVAL '10 days 6 hours 30 minutes', 980000, 390)
) AS v(flight_number, airline, origin, destination, departure_time, arrival_time, price, duration_minutes)
WHERE NOT EXISTS (SELECT 1 FROM flights LIMIT 1);

-- Link flights to airlines / airports
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;

-- Cabin fares for every flight
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';

-- ─── 9. GRANTS (optional — for app user myczroxg_admin) ─────────────────────
-- Skip this section if phpPgAdmin is already logged in as myczroxg_admin.
-- If a GRANT line fails, ignore it — your cPanel "ALL PRIVILEGES" on the DB is enough.

DO $$ BEGIN
  GRANT USAGE ON SCHEMA public TO myczroxg_admin;
EXCEPTION WHEN OTHERS THEN NULL;
END $$;

DO $$ BEGIN
  GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myczroxg_admin;
EXCEPTION WHEN OTHERS THEN NULL;
END $$;

DO $$ BEGIN
  GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO myczroxg_admin;
EXCEPTION WHEN OTHERS THEN NULL;
END $$;

-- Per-table grants (works when ALL TABLES grant fails on shared hosting)
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO myczroxg_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON user_profiles TO myczroxg_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON airlines TO myczroxg_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON airports TO myczroxg_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON cabin_classes TO myczroxg_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON aircraft_types TO myczroxg_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON flights TO myczroxg_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON flight_fares TO myczroxg_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON promotions TO myczroxg_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON bookings TO myczroxg_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON booking_passengers TO myczroxg_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON booking_status_log TO myczroxg_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON payments TO myczroxg_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON faq_categories TO myczroxg_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON faqs TO myczroxg_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON chat_sessions TO myczroxg_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON conversations TO myczroxg_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON conversation_messages TO myczroxg_admin;

GRANT USAGE, SELECT, UPDATE ON users_id_seq TO myczroxg_admin;
GRANT USAGE, SELECT, UPDATE ON user_profiles_id_seq TO myczroxg_admin;
GRANT USAGE, SELECT, UPDATE ON airlines_id_seq TO myczroxg_admin;
GRANT USAGE, SELECT, UPDATE ON airports_id_seq TO myczroxg_admin;
GRANT USAGE, SELECT, UPDATE ON cabin_classes_id_seq TO myczroxg_admin;
GRANT USAGE, SELECT, UPDATE ON aircraft_types_id_seq TO myczroxg_admin;
GRANT USAGE, SELECT, UPDATE ON flights_id_seq TO myczroxg_admin;
GRANT USAGE, SELECT, UPDATE ON flight_fares_id_seq TO myczroxg_admin;
GRANT USAGE, SELECT, UPDATE ON promotions_id_seq TO myczroxg_admin;
GRANT USAGE, SELECT, UPDATE ON bookings_id_seq TO myczroxg_admin;
GRANT USAGE, SELECT, UPDATE ON booking_passengers_id_seq TO myczroxg_admin;
GRANT USAGE, SELECT, UPDATE ON booking_status_log_id_seq TO myczroxg_admin;
GRANT USAGE, SELECT, UPDATE ON payments_id_seq TO myczroxg_admin;
GRANT USAGE, SELECT, UPDATE ON faq_categories_id_seq TO myczroxg_admin;
GRANT USAGE, SELECT, UPDATE ON faqs_id_seq TO myczroxg_admin;
GRANT USAGE, SELECT, UPDATE ON chat_sessions_id_seq TO myczroxg_admin;
GRANT USAGE, SELECT, UPDATE ON conversations_id_seq TO myczroxg_admin;
GRANT USAGE, SELECT, UPDATE ON conversation_messages_id_seq TO myczroxg_admin;

-- =============================================================================
-- DONE. Verify with:
--   SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY 1;
-- =============================================================================