-- Upgrade existing SkyBook v1 database to v2
-- Safe to run multiple times (uses IF NOT EXISTS / DO blocks)

-- New reference 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',
  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',
  priority VARCHAR(20) DEFAULT 'normal',
  assigned_to INTEGER REFERENCES users(id) ON DELETE SET NULL,
  channel VARCHAR(20) DEFAULT 'chat', 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, 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
);

-- Add columns to 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 $$;

-- Add columns to 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 contact fields from legacy passenger columns
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;

-- Permissions (adjust username if needed) — GRANT only, no ALTER OWNER
GRANT USAGE ON SCHEMA public TO myczroxg_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myczroxg_admin;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO myczroxg_admin;