-- CMS and Enhanced Booking System Database Schema

-- ============================================
-- CMS TABLES
-- ============================================

-- Website content management
CREATE TABLE IF NOT EXISTS cms_content (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  section VARCHAR(100) NOT NULL UNIQUE, -- hero, features, about, etc.
  title TEXT,
  subtitle TEXT,
  content TEXT,
  button_text VARCHAR(100),
  button_link VARCHAR(255),
  image_url VARCHAR(255),
  is_active BOOLEAN DEFAULT 1,
  display_order INTEGER DEFAULT 0,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_by INTEGER,
  FOREIGN KEY (updated_by) REFERENCES users(id)
);

-- Vehicle types management
CREATE TABLE IF NOT EXISTS cms_vehicles (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  image_url VARCHAR(255),
  max_passengers INTEGER NOT NULL,
  max_luggage INTEGER NOT NULL,
  base_price DECIMAL(10,2) NOT NULL,
  price_per_mile DECIMAL(10,2) NOT NULL,
  vehicle_type VARCHAR(50), -- saloon, executive, estate, people-carrier, minibus
  features TEXT, -- JSON array of features
  is_active BOOLEAN DEFAULT 1,
  display_order INTEGER DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Pricing rules management
CREATE TABLE IF NOT EXISTS cms_pricing (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  rule_name VARCHAR(100) NOT NULL UNIQUE,
  rule_type VARCHAR(50) NOT NULL, -- airport-fee, extra-service, discount
  value DECIMAL(10,2) NOT NULL,
  value_type VARCHAR(20) NOT NULL, -- fixed, percentage
  description TEXT,
  applicable_to TEXT, -- JSON array: which vehicles/services this applies to
  is_active BOOLEAN DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Airport information
CREATE TABLE IF NOT EXISTS cms_airports (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(150) NOT NULL,
  code VARCHAR(10) UNIQUE,
  postcode VARCHAR(20),
  address TEXT,
  meet_greet_fee DECIMAL(10,2) DEFAULT 15.00,
  parking_fee DECIMAL(10,2) DEFAULT 0.00,
  is_active BOOLEAN DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Website settings
CREATE TABLE IF NOT EXISTS cms_settings (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  setting_key VARCHAR(100) NOT NULL UNIQUE,
  setting_value TEXT,
  setting_type VARCHAR(50), -- text, number, boolean, json
  category VARCHAR(50), -- company, contact, social, booking
  description TEXT,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Image gallery for CMS
CREATE TABLE IF NOT EXISTS cms_images (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  filename VARCHAR(255) NOT NULL,
  original_name VARCHAR(255),
  file_path VARCHAR(500) NOT NULL,
  file_size INTEGER,
  mime_type VARCHAR(100),
  category VARCHAR(50), -- vehicle, hero, gallery, service
  alt_text VARCHAR(255),
  uploaded_by INTEGER,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (uploaded_by) REFERENCES users(id)
);

-- ============================================
-- ENHANCED BOOKING TABLES
-- ============================================

-- Enhanced bookings table
CREATE TABLE IF NOT EXISTS bookings_enhanced (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  booking_reference VARCHAR(20) UNIQUE NOT NULL,
  
  -- Journey details
  journey_type VARCHAR(50) NOT NULL, -- airport-to-address, address-to-airport, address-to-address
  pickup_type VARCHAR(50), -- airport, postcode, address
  pickup_location TEXT NOT NULL,
  pickup_postcode VARCHAR(20),
  pickup_airport_id INTEGER,
  dropoff_type VARCHAR(50),
  dropoff_location TEXT NOT NULL,
  dropoff_postcode VARCHAR(20),
  dropoff_airport_id INTEGER,
  
  -- Date and time
  pickup_date DATE NOT NULL,
  pickup_time TIME NOT NULL,
  flight_number VARCHAR(50),
  flight_arrival_time TIME,
  
  -- Passenger details
  passengers INTEGER NOT NULL,
  luggage INTEGER NOT NULL,
  
  -- Vehicle selection
  vehicle_id INTEGER NOT NULL,
  vehicle_name VARCHAR(100),
  
  -- Customer details
  user_id INTEGER,
  guest_email VARCHAR(255),
  guest_name VARCHAR(255),
  guest_phone VARCHAR(50),
  customer_notes TEXT,
  
  -- Pricing
  distance_miles REAL,
  base_fare DECIMAL(10,2) NOT NULL,
  distance_charge DECIMAL(10,2) DEFAULT 0,
  airport_fee DECIMAL(10,2) DEFAULT 0,
  meet_greet_fee DECIMAL(10,2) DEFAULT 0,
  extras_total DECIMAL(10,2) DEFAULT 0,
  discount_amount DECIMAL(10,2) DEFAULT 0,
  discount_type VARCHAR(50),
  total_price DECIMAL(10,2) NOT NULL,
  
  -- Extras/Services
  meet_greet BOOLEAN DEFAULT 0,
  child_seat BOOLEAN DEFAULT 0,
  
  -- Return journey
  is_return_journey BOOLEAN DEFAULT 0,
  return_pickup_date DATE,
  return_pickup_time TIME,
  return_flight_number VARCHAR(50),
  
  -- Status
  status VARCHAR(50) DEFAULT 'pending', -- pending, confirmed, assigned, completed, cancelled
  payment_status VARCHAR(50) DEFAULT 'unpaid', -- unpaid, paid, refunded
  assigned_driver_id INTEGER,
  
  -- Timestamps
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (vehicle_id) REFERENCES cms_vehicles(id),
  FOREIGN KEY (pickup_airport_id) REFERENCES cms_airports(id),
  FOREIGN KEY (dropoff_airport_id) REFERENCES cms_airports(id),
  FOREIGN KEY (assigned_driver_id) REFERENCES users(id)
);

-- Quote requests (for manual quotes)
CREATE TABLE IF NOT EXISTS quote_requests (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  journey_type VARCHAR(50) NOT NULL,
  pickup_location TEXT NOT NULL,
  dropoff_location TEXT NOT NULL,
  pickup_date DATE,
  pickup_time TIME,
  passengers INTEGER,
  luggage INTEGER,
  customer_name VARCHAR(255),
  customer_email VARCHAR(255) NOT NULL,
  customer_phone VARCHAR(50),
  additional_info TEXT,
  status VARCHAR(50) DEFAULT 'pending', -- pending, quoted, converted, expired
  quoted_price DECIMAL(10,2),
  quoted_vehicle_id INTEGER,
  quoted_at DATETIME,
  quoted_by INTEGER,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (quoted_vehicle_id) REFERENCES cms_vehicles(id),
  FOREIGN KEY (quoted_by) REFERENCES users(id)
);

-- Driver management
CREATE TABLE IF NOT EXISTS drivers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER UNIQUE,
  driver_name VARCHAR(255) NOT NULL,
  phone VARCHAR(50) NOT NULL,
  email VARCHAR(255),
  license_number VARCHAR(100) UNIQUE NOT NULL,
  license_expiry DATE,
  vehicle_registration VARCHAR(20),
  vehicle_type VARCHAR(100),
  status VARCHAR(50) DEFAULT 'active', -- active, inactive, suspended
  rating DECIMAL(3,2) DEFAULT 5.00,
  total_trips INTEGER DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- ============================================
-- INDEXES FOR PERFORMANCE
-- ============================================

CREATE INDEX IF NOT EXISTS idx_cms_content_section ON cms_content(section);
CREATE INDEX IF NOT EXISTS idx_cms_vehicles_active ON cms_vehicles(is_active);
CREATE INDEX IF NOT EXISTS idx_cms_pricing_active ON cms_pricing(is_active);
CREATE INDEX IF NOT EXISTS idx_bookings_enhanced_reference ON bookings_enhanced(booking_reference);
CREATE INDEX IF NOT EXISTS idx_bookings_enhanced_status ON bookings_enhanced(status);
CREATE INDEX IF NOT EXISTS idx_bookings_enhanced_date ON bookings_enhanced(pickup_date);
CREATE INDEX IF NOT EXISTS idx_quote_requests_status ON quote_requests(status);
CREATE INDEX IF NOT EXISTS idx_drivers_status ON drivers(status);

-- ============================================
-- DEFAULT DATA
-- ============================================

-- Insert default CMS content
INSERT OR IGNORE INTO cms_content (section, title, subtitle, content, is_active) VALUES
('hero', 'Premium Airport Transfers', '24 hours a day. 7 days a week.', 'Professional taxi transfers to and from all UK airports for both individuals and groups, with an emphasis on a courteous and thoroughly professional service at affordable prices.', 1),
('features', 'Why Choose Us', 'Professional Service You Can Trust', NULL, 1),
('about', 'About Ash Transfer', 'Your Trusted Airport Transfer Partner', 'From the moment you contact us, we''ll do everything we can to ensure that your booking and transfer go as smoothly as possible.', 1);

-- Insert default vehicles
INSERT OR IGNORE INTO cms_vehicles (name, description, max_passengers, max_luggage, base_price, price_per_mile, vehicle_type, features, is_active) VALUES
('Saloon Car', 'Ford Mondeo, VW Passat or similar. Perfect for up to 3 passengers with 3 standard suitcases or 4 passengers with hand luggage only.', 4, 3, 35.00, 1.50, 'saloon', '["Air conditioning", "Professional driver", "Clean vehicle"]', 1),
('Executive Car', 'E-Class Mercedes or similar. Luxury travel for up to 3 passengers with 3 standard suitcases or 4 passengers with hand luggage only.', 4, 3, 50.00, 2.00, 'executive', '["Luxury interior", "Professional driver", "Complimentary water"]', 1),
('Estate Car', 'Volvo Estate, VW Passat or similar. Extra luggage space for up to 4 passengers with 4 standard suitcases.', 4, 4, 40.00, 1.75, 'estate', ["Extra luggage space", "Air conditioning", "Professional driver"]', 1),
('People Carrier', 'VW Sharan, Ford Galaxy or similar. Ideal for families or groups of up to 5 passengers with 5 suitcases or 6 passengers with hand luggage.', 6, 5, 55.00, 1.90, 'people-carrier', '["Spacious interior", "Family friendly", "Professional driver"]', 1),
('Executive People Carrier', 'Mercedes V-Class or similar. Luxury travel for up to 5 passengers with 5 suitcases or 6 passengers with hand luggage.', 6, 5, 70.00, 2.50, 'executive-people-carrier', '["Luxury interior", "Spacious", "Complimentary refreshments"]', 1),
('8 Seater Minibus', 'VW Transporter or similar. Perfect for larger groups of up to 8 passengers with 8 standard suitcases.', 8, 8, 80.00, 2.20, 'minibus', '["Group travel", "Extra luggage", "Professional driver"]', 1);

-- Insert default pricing rules
INSERT OR IGNORE INTO cms_pricing (rule_name, rule_type, value, value_type, description, is_active) VALUES
('Meet & Greet Service', 'extra-service', 15.00, 'fixed', 'Driver waits at arrivals with name board', 1),
('Child Car Seat', 'extra-service', 0.00, 'fixed', 'Free child car seat provided', 1),
('Return Journey Discount', 'discount', 10.00, 'percentage', 'Save 10% when booking return journey', 1),
('Airport Parking Fee', 'airport-fee', 5.00, 'fixed', 'Standard airport parking fee', 1);

-- Insert major UK airports
INSERT OR IGNORE INTO cms_airports (name, code, postcode, meet_greet_fee) VALUES
('Manchester Airport', 'MAN', 'M90 1QX', 15.00),
('Liverpool John Lennon Airport', 'LPL', 'L24 1YD', 15.00),
('London Heathrow Airport', 'LHR', 'TW6 1AP', 20.00),
('London Gatwick Airport', 'LGW', 'RH6 0NP', 20.00),
('Birmingham Airport', 'BHX', 'B26 3QJ', 15.00),
('Leeds Bradford Airport', 'LBA', 'LS19 7TU', 15.00),
('Edinburgh Airport', 'EDI', 'EH12 9DN', 15.00),
('Glasgow Airport', 'GLA', 'PA3 2SW', 15.00),
('Bristol Airport', 'BRS', 'BS48 3DY', 15.00),
('Newcastle Airport', 'NCL', 'NE13 8BZ', 15.00);

-- Insert default settings
INSERT OR IGNORE INTO cms_settings (setting_key, setting_value, setting_type, category, description) VALUES
('company_name', 'Ash Transfer', 'text', 'company', 'Company name'),
('company_phone', '+44 7860 261578', 'text', 'contact', 'Main phone number'),
('company_email', 'info@ashtransfer.com', 'text', 'contact', 'Main email address'),
('booking_email', 'booking@ashtransfer.com', 'text', 'contact', 'Booking email'),
('min_advance_hours', '24', 'number', 'booking', 'Minimum hours in advance for booking'),
('allow_same_day_booking', '0', 'boolean', 'booking', 'Allow same-day bookings'),
('default_currency', 'GBP', 'text', 'booking', 'Default currency'),
('currency_symbol', '£', 'text', 'booking', 'Currency symbol');
