-- =============================================
-- Tok Tokkie Shuttles Namibia Database Schema
-- WITH FULL IMAGE SUPPORT
-- =============================================

-- Create database
CREATE DATABASE IF NOT EXISTS tokkie_shuttles;
USE tokkie_shuttles;

-- =============================================
-- 1. USERS TABLE (Admin & Staff)
-- =============================================
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    profile_image VARCHAR(255),
    role ENUM('admin', 'manager', 'staff') DEFAULT 'staff',
    is_active BOOLEAN DEFAULT TRUE,
    last_login DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =============================================
-- 2. ROUTES TABLE (with images)
-- =============================================
CREATE TABLE routes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    route_code VARCHAR(20) UNIQUE NOT NULL,
    origin VARCHAR(100) NOT NULL,
    destination VARCHAR(100) NOT NULL,
    country_origin VARCHAR(50) NOT NULL,
    country_destination VARCHAR(50) NOT NULL,
    distance_km INT,
    estimated_duration_hours DECIMAL(3,1),
    -- Image fields
    route_image VARCHAR(255),
    destination_image VARCHAR(255),
    map_image VARCHAR(255),
    gallery_images JSON, -- Array of image URLs
    is_active BOOLEAN DEFAULT TRUE,
    description TEXT,
    highlights TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =============================================
-- 3. VEHICLES TABLE (with images)
-- =============================================
CREATE TABLE vehicles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    vehicle_code VARCHAR(20) UNIQUE NOT NULL,
    vehicle_type ENUM('economy', 'vip', 'premium', 'standard') NOT NULL,
    vehicle_name VARCHAR(50) NOT NULL,
    capacity INT NOT NULL,
    has_trailer BOOLEAN DEFAULT FALSE,
    has_ac BOOLEAN DEFAULT TRUE,
    has_wifi BOOLEAN DEFAULT FALSE,
    has_bathroom BOOLEAN DEFAULT FALSE,
    has_usb_charging BOOLEAN DEFAULT FALSE,
    has_tv_entertainment BOOLEAN DEFAULT FALSE,
    extra_features TEXT,
    license_plate VARCHAR(20),
    -- Image fields
    vehicle_image VARCHAR(255),
    interior_image VARCHAR(255),
    exterior_image VARCHAR(255),
    gallery_images JSON, -- Array of image URLs
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =============================================
-- 4. SCHEDULES TABLE
-- =============================================
CREATE TABLE schedules (
    id INT PRIMARY KEY AUTO_INCREMENT,
    route_id INT NOT NULL,
    vehicle_id INT NOT NULL,
    departure_day ENUM('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday') NOT NULL,
    departure_time TIME NOT NULL,
    arrival_time TIME NOT NULL,
    price_per_seat DECIMAL(10,2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'NAD',
    max_seats INT,
    is_recurring BOOLEAN DEFAULT TRUE,
    is_active BOOLEAN DEFAULT TRUE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (route_id) REFERENCES routes(id) ON DELETE CASCADE,
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE,
    UNIQUE KEY unique_schedule (route_id, departure_day, departure_time)
);

-- =============================================
-- 5. CUSTOMERS TABLE (MUST come before bookings)
-- =============================================
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    address TEXT,
    passport_number VARCHAR(20),
    nationality VARCHAR(50),
    date_of_birth DATE,
    preferred_language VARCHAR(20) DEFAULT 'English',
    newsletter_subscribed BOOLEAN DEFAULT FALSE,
    total_bookings INT DEFAULT 0,
    total_spent DECIMAL(10,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email)
);

-- =============================================
-- 6. BOOKINGS TABLE
-- =============================================
CREATE TABLE bookings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    booking_reference VARCHAR(20) UNIQUE NOT NULL,
    schedule_id INT NOT NULL,
    customer_id INT,
    -- Customer details
    customer_name VARCHAR(100) NOT NULL,
    customer_email VARCHAR(100) NOT NULL,
    customer_phone VARCHAR(20) NOT NULL,
    -- Pickup details
    pickup_address VARCHAR(255),
    pickup_landmark VARCHAR(100),
    dropoff_address VARCHAR(255),
    dropoff_landmark VARCHAR(100),
    -- Booking details
    number_of_passengers INT NOT NULL DEFAULT 1,
    total_amount DECIMAL(10,2) NOT NULL,
    deposit_amount DECIMAL(10,2) NOT NULL,
    balance_amount DECIMAL(10,2) NOT NULL,
    -- Status
    booking_status ENUM('pending', 'POP_uploaded', 'pending_approval', 'confirmed', 'completed', 'cancelled') DEFAULT 'pending',
    payment_status ENUM('unpaid', 'deposit_only', 'fully_paid') DEFAULT 'unpaid',
    -- Special requests
    special_requests TEXT,
    is_door_to_door BOOLEAN DEFAULT FALSE,
    image_url VARCHAR(500),
    proof_of_payment VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (schedule_id) REFERENCES schedules(id) ON DELETE RESTRICT,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    INDEX idx_booking_reference (booking_reference),
    INDEX idx_customer_email (customer_email),
    INDEX idx_status (booking_status)
);

-- =============================================
-- 7. PAYMENTS TABLE
-- =============================================
CREATE TABLE payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    booking_id INT NOT NULL,
    payment_reference VARCHAR(50) UNIQUE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_method ENUM('ewallet', 'paytoday', 'eft', 'cash', 'card') NOT NULL,
    payment_status ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
    transaction_id VARCHAR(100),
    payment_date DATETIME,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
    INDEX idx_booking_id (booking_id),
    INDEX idx_payment_reference (payment_reference)
);

-- =============================================
-- 8. NOTIFICATIONS TABLE
-- =============================================
CREATE TABLE notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    booking_id INT NOT NULL,
    notification_type ENUM('email', 'whatsapp', 'sms') NOT NULL,
    recipient VARCHAR(100) NOT NULL,
    subject VARCHAR(255),
    message TEXT,
    status ENUM('pending', 'sent', 'failed') DEFAULT 'pending',
    sent_at DATETIME,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
    INDEX idx_booking_id (booking_id),
    INDEX idx_status (status)
);

-- =============================================
-- 9. PROMOTIONS TABLE (NEW - with images)
-- =============================================
CREATE TABLE promotions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    description TEXT,
    promo_code VARCHAR(50) UNIQUE,
    discount_type ENUM('percentage', 'fixed') DEFAULT 'percentage',
    discount_value DECIMAL(10,2) NOT NULL,
    min_booking_amount DECIMAL(10,2),
    max_discount_amount DECIMAL(10,2),
    start_date DATETIME NOT NULL,
    end_date DATETIME NOT NULL,
    -- Image fields
    promo_image VARCHAR(255),
    banner_image VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    terms_conditions TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =============================================
-- 10. REVIEWS TABLE (NEW - with images)
-- =============================================
CREATE TABLE reviews (
    id INT PRIMARY KEY AUTO_INCREMENT,
    booking_id INT NOT NULL,
    customer_id INT NOT NULL,
    rating INT CHECK (rating >= 1 AND rating <= 5),
    review_text TEXT,
    -- Image fields
    review_images JSON, -- Array of image URLs
    is_verified BOOLEAN DEFAULT FALSE,
    is_featured BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    INDEX idx_booking_id (booking_id),
    INDEX idx_customer_id (customer_id)
);

-- =============================================
-- 11. AUDIT LOG TABLE
-- =============================================
CREATE TABLE audit_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(50) NOT NULL,
    table_name VARCHAR(50),
    record_id INT,
    old_values JSON,
    new_values JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at)
);

-- =============================================
-- 12. SETTINGS TABLE
-- =============================================
CREATE TABLE settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(50) UNIQUE NOT NULL,
    setting_value TEXT,
    setting_group VARCHAR(50),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =============================================
-- 13. STOPS TABLE (Trans-Kalahari corridor)
-- =============================================
CREATE TABLE stops (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    country VARCHAR(50) NOT NULL,
    is_border BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =============================================
-- 14. ROUTE STOPS JUNCTION TABLE
-- =============================================
CREATE TABLE route_stops (
    id INT PRIMARY KEY AUTO_INCREMENT,
    route_id INT NOT NULL,
    stop_id INT NOT NULL,
    order INT NOT NULL,
    distance_from_previous_km INT,
    FOREIGN KEY (route_id) REFERENCES routes(id) ON DELETE CASCADE,
    FOREIGN KEY (stop_id) REFERENCES stops(id) ON DELETE CASCADE,
    UNIQUE KEY unique_route_stop_order (route_id, stop_id)
);

-- =============================================
-- 15. SEGMENT PRICES TABLE (A-to-B pricing)
-- =============================================
CREATE TABLE segment_prices (
    id INT PRIMARY KEY AUTO_INCREMENT,
    route_id INT NOT NULL,
    from_stop_id INT NOT NULL,
    to_stop_id INT NOT NULL,
    vehicle_type ENUM('economy', 'vip', 'premium', 'standard') NOT NULL,
    price_nad DECIMAL(10,2) NOT NULL,
    price_bwp DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (route_id) REFERENCES routes(id) ON DELETE CASCADE,
    FOREIGN KEY (from_stop_id) REFERENCES stops(id) ON DELETE CASCADE,
    FOREIGN KEY (to_stop_id) REFERENCES stops(id) ON DELETE CASCADE,
    UNIQUE KEY unique_segment_pricing (route_id, from_stop_id, to_stop_id, vehicle_type)
);

-- =============================================
-- 16. BOOKING SEGMENTS TABLE
-- =============================================
CREATE TABLE booking_segments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    booking_id INT NOT NULL,
    from_stop_id INT NOT NULL,
    to_stop_id INT NOT NULL,
    FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
    FOREIGN KEY (from_stop_id) REFERENCES stops(id) ON DELETE CASCADE,
    FOREIGN KEY (to_stop_id) REFERENCES stops(id) ON DELETE CASCADE,
    INDEX idx_booking_id (booking_id)
);

-- =============================================
-- 17. PUSH TOKENS TABLE
-- =============================================
CREATE TABLE push_tokens (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) NOT NULL,
    token VARCHAR(255) NOT NULL,
    platform ENUM('ios', 'android') DEFAULT 'android',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_token (token),
    INDEX idx_email (email)
);

-- =============================================
-- INSERT INITIAL DATA WITH IMAGES
-- =============================================

-- 1a. Insert Stops (Trans-Kalahari corridor)
INSERT INTO stops (name, country, is_border) VALUES
('Windhoek', 'Namibia', FALSE),
('Gobabis', 'Namibia', FALSE),
('Buitepos', 'Namibia', TRUE),
('Charles Hill', 'Botswana', TRUE),
('Tsootsha', 'Botswana', FALSE),
('Karakubis', 'Botswana', FALSE),
('Ghanzi', 'Botswana', FALSE),
('Gaborone', 'Botswana', FALSE);

-- 1. Insert Routes with Unsplash Images
INSERT INTO routes (
    route_code, origin, destination, country_origin, country_destination, 
    distance_km, estimated_duration_hours,
    route_image, destination_image, map_image,
    gallery_images, description, highlights
) VALUES
(
    'WHK-GAB', 'Windhoek', 'Gaborone', 'Namibia', 'Botswana',
    1450, 18.0,
    'https://images.unsplash.com/photo-1544620347-c4fd4a3d5957?w=800', -- Windhoek city
    'https://images.unsplash.com/photo-1580060839136-3f2f2b6aaec3?w=800', -- Gaborone
    'https://images.unsplash.com/photo-1524661135-423995f22d0b?w=800', -- Map
    '["https://images.unsplash.com/photo-1544620347-c4fd4a3d5957?w=400", "https://images.unsplash.com/photo-1580060839136-3f2f2b6aaec3?w=400"]',
    'Premium cross-border shuttle service connecting Windhoek and Gaborone',
    'Comfortable journey, border assistance, refreshments included'
),
(
    'WHK-JNB', 'Windhoek', 'Johannesburg', 'Namibia', 'South Africa',
    1800, 22.0,
    'https://images.unsplash.com/photo-1544620347-c4fd4a3d5957?w=800',
    'https://images.unsplash.com/photo-1578662996444-abc54a1a6e9c?w=800', -- Johannesburg
    'https://images.unsplash.com/photo-1524661135-423995f22d0b?w=800',
    '["https://images.unsplash.com/photo-1544620347-c4fd4a3d5957?w=400", "https://images.unsplash.com/photo-1578662996444-abc54a1a6e9c?w=400"]',
    'Cross-border shuttle between Windhoek and Johannesburg',
    'Direct route, comfortable seating, overnight stops available'
),
(
    'WHK-CPT', 'Windhoek', 'Cape Town', 'Namibia', 'South Africa',
    1600, 20.0,
    'https://images.unsplash.com/photo-1544620347-c4fd4a3d5957?w=800',
    'https://images.unsplash.com/photo-1580060839136-3f2f2b6aaec3?w=800', -- Cape Town
    'https://images.unsplash.com/photo-1524661135-423995f22d0b?w=800',
    '["https://images.unsplash.com/photo-1544620347-c4fd4a3d5957?w=400", "https://images.unsplash.com/photo-1580060839136-3f2f2b6aaec3?w=400"]',
    'Scenic route from Windhoek to Cape Town',
    'Scenic views, comfort stops, flexible schedule'
);

-- 2. Insert Vehicles with Unsplash Images
INSERT INTO vehicles (
    vehicle_code, vehicle_type, vehicle_name, capacity, 
    has_trailer, has_ac, has_wifi, has_usb_charging, has_tv_entertainment,
    vehicle_image, interior_image, exterior_image,
    gallery_images, extra_features
) VALUES
(
    'QUANTUM-1', 'economy', 'Toyota Quantum', 14,
    TRUE, TRUE, TRUE, TRUE, FALSE,
    'https://images.unsplash.com/photo-1549317661-bd32c8ce0db2?w=800', -- Van exterior
    'https://images.unsplash.com/photo-1549317661-bd32c8ce0db2?w=800', -- Interior
    'https://images.unsplash.com/photo-1549317661-bd32c8ce0db2?w=800', -- Exterior
    '["https://images.unsplash.com/photo-1549317661-bd32c8ce0db2?w=400", "https://images.unsplash.com/photo-1549317661-bd32c8ce0db2?w=400"]',
    'Air conditioning, USB charging ports, comfortable seating'
),
(
    'PREMACY-1', 'vip', 'Premium Premacy', 4,
    FALSE, TRUE, TRUE, TRUE, TRUE,
    'https://images.unsplash.com/photo-1549317661-bd32c8ce0db2?w=800',
    'https://images.unsplash.com/photo-1549317661-bd32c8ce0db2?w=800',
    'https://images.unsplash.com/photo-1549317661-bd32c8ce0db2?w=800',
    '["https://images.unsplash.com/photo-1549317661-bd32c8ce0db2?w=400"]',
    'Extra legroom, entertainment system, refreshments'
),
(
    'SPRINTER-1', 'premium', 'Mercedes Sprinter', 20,
    TRUE, TRUE, TRUE, TRUE, TRUE,
    'https://images.unsplash.com/photo-1549317661-bd32c8ce0db2?w=800',
    'https://images.unsplash.com/photo-1549317661-bd32c8ce0db2?w=800',
    'https://images.unsplash.com/photo-1549317661-bd32c8ce0db2?w=800',
    '["https://images.unsplash.com/photo-1549317661-bd32c8ce0db2?w=400", "https://images.unsplash.com/photo-1549317661-bd32c8ce0db2?w=400"]',
    'Premium seating, onboard entertainment, Wi-Fi'
);

-- 3. Insert Schedules
INSERT INTO schedules (
    route_id, vehicle_id, departure_day, departure_time, 
    arrival_time, price_per_seat, currency, notes
) VALUES
(1, 1, 'Friday', '05:00:00', '23:00:00', 1200.00, 'NAD', 'Includes trailer - 1 large suitcase + 1 carry-on'),
(1, 2, 'Friday', '06:30:00', '00:30:00', 1850.00, 'NAD', 'Door-to-door service, max 4 passengers'),
(1, 1, 'Sunday', '05:00:00', '23:00:00', 1200.00, 'NAD', 'Includes trailer - 1 large suitcase + 1 carry-on'),
(1, 2, 'Sunday', '06:30:00', '00:30:00', 1850.00, 'NAD', 'Door-to-door service, max 4 passengers');

-- 4. Insert Promotions
INSERT INTO promotions (
    title, description, promo_code, discount_type, discount_value,
    min_booking_amount, max_discount_amount,
    start_date, end_date,
    promo_image, banner_image,
    terms_conditions
) VALUES
(
    'Early Bird Discount', 'Book 2 weeks in advance and save 10%',
    'EARLY10', 'percentage', 10.00,
    1000.00, 200.00,
    NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH),
    'https://images.unsplash.com/photo-1544620347-c4fd4a3d5957?w=800',
    'https://images.unsplash.com/photo-1544620347-c4fd4a3d5957?w=1200',
    'Valid for bookings made 14+ days before departure'
),
(
    'Group Discount', 'Save 15% on bookings of 4 or more passengers',
    'GROUP15', 'percentage', 15.00,
    4000.00, 500.00,
    NOW(), DATE_ADD(NOW(), INTERVAL 6 MONTH),
    'https://images.unsplash.com/photo-1544620347-c4fd4a3d5957?w=800',
    'https://images.unsplash.com/photo-1544620347-c4fd4a3d5957?w=1200',
    'Minimum 4 passengers per booking'
);

-- 5. Insert Settings
INSERT INTO settings (setting_key, setting_value, setting_group, description) VALUES
('booking_deadline_hours', '52', 'booking', 'Booking deadline in hours before departure'),
('deposit_percentage', '50', 'payment', 'Percentage of total amount required as deposit'),
('currency', 'NAD', 'general', 'Default currency'),
('whatsapp_number', '+264811275285', 'contact', 'WhatsApp contact number'),
('phone_number', '+264811275285', 'contact', 'Primary phone number'),
('support_email', 'tokkiebookings@shuttlesnamibia.com', 'contact', 'Support email address'),
('cancellation_policy', 'Cancellations must be made 24 hours before departure', 'general', 'Cancellation policy text'),
('app_name', 'Tok Tokkie Shuttles', 'branding', 'App display name'),
('app_logo', 'https://images.unsplash.com/photo-1544620347-c4fd4a3d5957?w=200', 'branding', 'App logo URL'),
('app_banner', 'https://images.unsplash.com/photo-1544620347-c4fd4a3d5957?w=1200', 'branding', 'App banner image');

-- 6. Insert Default Admin User
INSERT INTO users (username, email, password_hash, full_name, role, is_active, profile_image) VALUES
('admin', 'admin@tokkie.com', '$2b$10$YOUR_HASH_HERE', 'System Admin', 'admin', TRUE, 'https://images.unsplash.com/photo-1544620347-c4fd4a3d5957?w=200');

-- =============================================
-- STORED PROCEDURES
-- =============================================
DELIMITER $$

CREATE PROCEDURE generate_booking_reference(OUT ref VARCHAR(20))
BEGIN
    SET ref = CONCAT('TK', DATE_FORMAT(NOW(), '%Y%m'), LPAD(FLOOR(RAND() * 10000), 4, '0'));
END$$

CREATE PROCEDURE check_seat_availability(
    IN p_schedule_id INT,
    IN p_requested_seats INT,
    OUT p_available BOOLEAN,
    OUT p_available_seats INT
)
BEGIN
    DECLARE total_capacity INT;
    DECLARE booked_seats INT;
    
    SELECT v.capacity INTO total_capacity
    FROM schedules s
    JOIN vehicles v ON s.vehicle_id = v.id
    WHERE s.id = p_schedule_id;
    
    SELECT COALESCE(SUM(number_of_passengers), 0) INTO booked_seats
    FROM bookings
    WHERE schedule_id = p_schedule_id
    AND booking_status NOT IN ('cancelled', 'completed');
    
    SET p_available_seats = total_capacity - booked_seats;
    SET p_available = (p_available_seats >= p_requested_seats);
END$$

DELIMITER ;

-- =============================================
-- VIEWS
-- =============================================

CREATE VIEW upcoming_schedules AS
SELECT 
    s.id AS schedule_id,
    s.route_id,
    r.origin,
    r.destination,
    r.country_origin,
    r.country_destination,
    r.route_image,
    r.destination_image,
    s.departure_day,
    s.departure_time,
    s.arrival_time,
    v.id AS vehicle_id,
    v.vehicle_name,
    v.vehicle_type,
    v.vehicle_image,
    v.capacity,
    s.price_per_seat,
    s.currency,
    (v.capacity - COALESCE((
        SELECT SUM(number_of_passengers)
        FROM bookings b
        WHERE b.schedule_id = s.id
        AND b.booking_status NOT IN ('cancelled', 'completed')
    ), 0)) AS available_seats
FROM schedules s
JOIN routes r ON s.route_id = r.id
JOIN vehicles v ON s.vehicle_id = v.id
WHERE s.is_active = TRUE;

CREATE VIEW booking_details AS
SELECT 
    b.id,
    b.booking_reference,
    c.full_name AS customer_name,
    c.email AS customer_email,
    c.phone AS customer_phone,
    r.origin,
    r.destination,
    r.route_image,
    s.departure_day,
    s.departure_time,
    s.arrival_time,
    v.vehicle_name,
    v.vehicle_type,
    v.vehicle_image,
    b.number_of_passengers,
    b.total_amount,
    b.deposit_amount,
    b.balance_amount,
    b.booking_status,
    b.payment_status,
    b.created_at
FROM bookings b
JOIN schedules s ON b.schedule_id = s.id
JOIN routes r ON s.route_id = r.id
JOIN vehicles v ON s.vehicle_id = v.id
LEFT JOIN customers c ON b.customer_id = c.id;

-- =============================================
-- SAMPLE TEST DATA
-- =============================================

INSERT INTO customers (email, full_name, phone, address, passport_number, nationality) VALUES
('test@customer.com', 'John Doe', '+264811234567', '123 Independence Ave, Windhoek', 'P1234567', 'Namibian');

INSERT INTO bookings (
    booking_reference, schedule_id, customer_id,
    customer_name, customer_email, customer_phone,
    number_of_passengers, total_amount, deposit_amount, balance_amount,
    booking_status, payment_status
) VALUES (
    'TK2026010001', 1, 1,
    'John Doe', 'test@customer.com', '+264811234567',
    2, 2400.00, 1200.00, 1200.00,
    'pending', 'unpaid'
);

INSERT INTO reviews (
    booking_id, customer_id, rating, review_text, 
    review_images, is_verified, is_featured
) VALUES (
    1, 1, 5, 
    'Amazing service! The driver was professional and the vehicle was comfortable. Highly recommend!',
    '["https://images.unsplash.com/photo-1544620347-c4fd4a3d5957?w=400", "https://images.unsplash.com/photo-1544620347-c4fd4a3d5957?w=400"]',
    TRUE, TRUE
);