-- =====================================================
-- YATRA BOOKING SYSTEM - CORRECTED SCHEMA v2
-- Requires MySQL 8.0.16+ (CHECK constraints are enforced
-- from that version; on older versions they are ignored)
--
-- CHANGELOG vs v1:
--  [FIX-1]  booking_seats: added schedule_id + UNIQUE(schedule_id, seat_id)
--           -> a seat can never be sold twice for the same trip
--  [FIX-2]  draft_seat_reservations: added schedule_id + UNIQUE(schedule_id, seat_id)
--           -> two drafts can never hold the same seat at once
--           (expired rows MUST be deleted by a cleanup job to release the lock)
--  [FIX-3]  boarding_points + dropping_points merged into single `points` table
--  [FIX-4]  routes: UNIQUE(source, destination) + CHECK source <> destination
--  [FIX-5]  reviews: CHECK rating 1-5 + UNIQUE(user_id, bus_id)
--  [FIX-6]  schedules: CHECK arrival > departure + search index
--  [FIX-7]  waitlists: UNIQUE(user_id, schedule_id) + created_at
--  [FIX-8]  coupons now usable: coupon_id/discount on bookings + coupon_usages table
--  [FIX-9]  tickets: UNIQUE(booking_id) -> one ticket per booking
--  [FIX-10] missing created_at added (operators, buses, schedules, sessions,
--           refunds, cancellations, payments, seats)
--  [FIX-11] tightened NULLs: payment_status, amounts, loyalty user_id, etc.
--  [FIX-12] explicit ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 on every table
--  [FIX-13] performance indexes for the hot query paths
--  [FIX-14] gender is now an ENUM; CHECK on passenger age; CHECK amount >= 0
--  [FIX-15] user_sessions: revoked flag for proper logout/invalidation
-- =====================================================

CREATE DATABASE IF NOT EXISTS yatra_booking
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;
USE yatra_booking;

-- =====================================================
-- ROLES
-- =====================================================

CREATE TABLE roles (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) UNIQUE NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO roles(name)
VALUES
('ADMIN'),
('OPERATOR'),
('PASSENGER');

-- =====================================================
-- USERS
-- Password stored as Argon2id/Bcrypt hash
-- =====================================================

CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    role_id BIGINT NOT NULL,

    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    phone VARCHAR(30) UNIQUE,

    password_hash VARCHAR(255) NOT NULL,

    email_verified BOOLEAN NOT NULL DEFAULT FALSE,
    phone_verified BOOLEAN NOT NULL DEFAULT FALSE,

    status ENUM(
        'ACTIVE',
        'INACTIVE',
        'SUSPENDED'
    ) NOT NULL DEFAULT 'ACTIVE',

    failed_login_attempts INT NOT NULL DEFAULT 0,
    account_locked_until DATETIME NULL,
    password_changed_at DATETIME NULL,
    last_login_at DATETIME NULL,

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY(role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- PASSWORD RESETS
-- =====================================================

CREATE TABLE password_resets (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    expires_at DATETIME NOT NULL,
    used BOOLEAN NOT NULL DEFAULT FALSE,
    used_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- [FIX-13] cleanup jobs scan by expiry
    INDEX idx_password_resets_expires (expires_at),

    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- SESSIONS
-- =====================================================

CREATE TABLE user_sessions (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    refresh_token_encrypted TEXT NOT NULL,
    ip_address VARCHAR(100),
    device_info VARCHAR(255),
    revoked BOOLEAN NOT NULL DEFAULT FALSE,          -- [FIX-15]
    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- [FIX-10]

    INDEX idx_sessions_expires (expires_at),         -- [FIX-13]

    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- OPERATORS
-- =====================================================

CREATE TABLE operators (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    company_name VARCHAR(200) NOT NULL,
    registration_number VARCHAR(100),
    document_encrypted TEXT,
    verified BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- [FIX-10]

    FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- CITIES
-- =====================================================

CREATE TABLE cities (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    city_name VARCHAR(100) UNIQUE NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- ROUTES
-- =====================================================

CREATE TABLE routes (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    source_city_id BIGINT NOT NULL,
    destination_city_id BIGINT NOT NULL,

    distance_km DECIMAL(10,2),
    estimated_duration_minutes INT,

    -- [FIX-4] no duplicate routes, no city-to-itself routes
    UNIQUE KEY uq_route (source_city_id, destination_city_id),
    CONSTRAINT chk_route_cities CHECK (source_city_id <> destination_city_id),

    FOREIGN KEY(source_city_id) REFERENCES cities(id),
    FOREIGN KEY(destination_city_id) REFERENCES cities(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- POINTS  [FIX-3]
-- Replaces the duplicated boarding_points / dropping_points
-- tables. The same physical location (e.g. a bus park) can
-- serve as boarding on one schedule and dropping on another.
-- =====================================================

CREATE TABLE points (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    city_id BIGINT NOT NULL,
    point_name VARCHAR(255) NOT NULL,
    address TEXT,
    latitude DECIMAL(10,8),
    longitude DECIMAL(11,8),

    UNIQUE KEY uq_point_per_city (city_id, point_name),

    FOREIGN KEY(city_id) REFERENCES cities(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- AMENITIES
-- =====================================================

CREATE TABLE amenities (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) UNIQUE NOT NULL,
    icon VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- BUSES
-- NOTE: total_seats is intentionally kept as the declared
-- capacity; the seats table is the layout. Application must
-- keep them consistent (or derive count from seats).
-- =====================================================

CREATE TABLE buses (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    operator_id BIGINT NOT NULL,

    bus_name VARCHAR(150),
    registration_no VARCHAR(100) UNIQUE,

    bus_type ENUM(
        'NORMAL',
        'AC',
        'DELUXE',
        'VIP'
    ),

    total_seats INT NOT NULL,

    status ENUM(
        'ACTIVE',
        'INACTIVE'
    ) NOT NULL DEFAULT 'ACTIVE',

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- [FIX-10]

    CONSTRAINT chk_bus_seats CHECK (total_seats > 0),

    FOREIGN KEY(operator_id) REFERENCES operators(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- BUS AMENITIES
-- =====================================================

CREATE TABLE bus_amenities (
    bus_id BIGINT NOT NULL,
    amenity_id BIGINT NOT NULL,

    PRIMARY KEY(bus_id, amenity_id),

    FOREIGN KEY(bus_id) REFERENCES buses(id) ON DELETE CASCADE,
    FOREIGN KEY(amenity_id) REFERENCES amenities(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- SEATS
-- =====================================================

CREATE TABLE seats (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    bus_id BIGINT NOT NULL,

    seat_number VARCHAR(10) NOT NULL,

    deck ENUM('LOWER','UPPER') NOT NULL DEFAULT 'LOWER',

    seat_type ENUM(
        'WINDOW',
        'AISLE',
        'SLEEPER'
    ),

    UNIQUE KEY uq_seat (bus_id, seat_number),

    FOREIGN KEY(bus_id) REFERENCES buses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- SCHEDULES
-- =====================================================

CREATE TABLE schedules (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    bus_id BIGINT NOT NULL,
    route_id BIGINT NOT NULL,

    departure_time DATETIME NOT NULL,
    arrival_time DATETIME NOT NULL,

    boarding_point_id BIGINT,
    dropping_point_id BIGINT,

    fare DECIMAL(10,2) NOT NULL,

    status ENUM(
        'ACTIVE',
        'CANCELLED',
        'COMPLETED'
    ) NOT NULL DEFAULT 'ACTIVE',

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- [FIX-10]

    -- [FIX-6] a bus cannot arrive before it departs; fare cannot be negative
    CONSTRAINT chk_schedule_times CHECK (arrival_time > departure_time),
    CONSTRAINT chk_schedule_fare CHECK (fare >= 0),

    -- [FIX-13] THE hot query: "buses from A to B on date X"
    INDEX idx_schedule_search (route_id, departure_time, status),
    -- operator dashboard: "my bus's upcoming trips"
    INDEX idx_schedule_bus (bus_id, departure_time),

    FOREIGN KEY(bus_id) REFERENCES buses(id),
    FOREIGN KEY(route_id) REFERENCES routes(id),
    FOREIGN KEY(boarding_point_id) REFERENCES points(id),   -- [FIX-3]
    FOREIGN KEY(dropping_point_id) REFERENCES points(id)    -- [FIX-3]
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- TEMP BOOKING DRAFTS
-- =====================================================

CREATE TABLE booking_drafts (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    draft_code VARCHAR(50) UNIQUE NOT NULL,

    user_id BIGINT,
    schedule_id BIGINT NOT NULL,

    total_amount DECIMAL(10,2),

    status ENUM(
        'PENDING_PAYMENT',
        'PAYMENT_PROCESSING',
        'COMPLETED',
        'FAILED',
        'EXPIRED'
    ) NOT NULL DEFAULT 'PENDING_PAYMENT',

    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_draft_expires (status, expires_at),   -- [FIX-13] cleanup job

    FOREIGN KEY(user_id) REFERENCES users(id),
    FOREIGN KEY(schedule_id) REFERENCES schedules(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- TEMP PASSENGERS
-- =====================================================

CREATE TABLE booking_draft_passengers (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    draft_id BIGINT NOT NULL,

    full_name VARCHAR(150),
    age INT,
    gender ENUM('MALE','FEMALE','OTHER'),           -- [FIX-14]
    phone VARCHAR(30),

    CONSTRAINT chk_draft_passenger_age CHECK (age IS NULL OR age BETWEEN 1 AND 120),

    FOREIGN KEY(draft_id)
        REFERENCES booking_drafts(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- TEMP SEAT LOCKING  [FIX-2]
-- schedule_id added so the uniqueness is per-trip, not
-- per-physical-seat. UNIQUE(schedule_id, seat_id) means the
-- database itself refuses a second draft holding the same
-- seat for the same trip.
-- IMPORTANT: a cleanup job must DELETE rows where
-- reserved_until < NOW() (and mark the draft EXPIRED),
-- otherwise expired locks keep blocking the seat.
-- =====================================================

CREATE TABLE draft_seat_reservations (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    draft_id BIGINT NOT NULL,
    schedule_id BIGINT NOT NULL,
    seat_id BIGINT NOT NULL,

    reserved_until DATETIME NOT NULL,

    UNIQUE KEY uq_draft_seat_lock (schedule_id, seat_id),

    INDEX idx_draft_lock_expiry (reserved_until),   -- [FIX-13] cleanup job

    FOREIGN KEY(draft_id)
        REFERENCES booking_drafts(id)
        ON DELETE CASCADE,
    FOREIGN KEY(schedule_id) REFERENCES schedules(id),
    FOREIGN KEY(seat_id) REFERENCES seats(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- PAYMENT ATTEMPTS
-- =====================================================

CREATE TABLE payment_attempts (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    draft_id BIGINT NOT NULL,

    payment_provider ENUM(
        'ESEWA',
        'KHALTI',
        'FONEPAY',
        'CARD'
    ),

    gateway_reference VARCHAR(255),

    amount DECIMAL(10,2) NOT NULL,                  -- [FIX-11]

    status ENUM(
        'INITIATED',
        'PENDING',
        'SUCCESS',
        'FAILED'
    ) NOT NULL DEFAULT 'INITIATED',                 -- [FIX-11]

    request_payload JSON,
    response_payload JSON,

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_attempt_amount CHECK (amount >= 0),

    FOREIGN KEY(draft_id) REFERENCES booking_drafts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- COUPONS  [FIX-8]
-- used_count lets you enforce usage_limit atomically:
--   UPDATE coupons SET used_count = used_count + 1
--   WHERE id = ? AND (usage_limit IS NULL OR used_count < usage_limit)
-- =====================================================

CREATE TABLE coupons (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    code VARCHAR(50) UNIQUE NOT NULL,               -- [FIX-11]

    discount_type ENUM(
        'FIXED',
        'PERCENTAGE'
    ) NOT NULL,

    discount_value DECIMAL(10,2) NOT NULL,

    valid_from DATETIME,
    valid_until DATETIME,

    usage_limit INT,
    used_count INT NOT NULL DEFAULT 0,

    CONSTRAINT chk_coupon_value CHECK (discount_value >= 0),
    CONSTRAINT chk_coupon_pct CHECK (
        discount_type <> 'PERCENTAGE' OR discount_value <= 100
    )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- BOOKINGS
-- [FIX-8] coupon_id + discount_amount so an applied coupon
-- is actually recorded on the booking.
-- =====================================================

CREATE TABLE bookings (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    booking_code VARCHAR(50) UNIQUE NOT NULL,

    user_id BIGINT NOT NULL,
    schedule_id BIGINT NOT NULL,

    coupon_id BIGINT NULL,                          -- [FIX-8]
    discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    total_amount DECIMAL(10,2) NOT NULL,            -- [FIX-11]

    booking_status ENUM(
        'PENDING',
        'CONFIRMED',
        'CANCELLED',
        'REFUNDED',
        'CHECKED_IN',
        'COMPLETED'
    ) NOT NULL DEFAULT 'PENDING',

    booked_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_booking_amounts CHECK (total_amount >= 0 AND discount_amount >= 0),

    -- [FIX-13] "my bookings" page + reporting
    INDEX idx_booking_user (user_id, booked_at),
    INDEX idx_booking_schedule (schedule_id),

    FOREIGN KEY(user_id) REFERENCES users(id),
    FOREIGN KEY(schedule_id) REFERENCES schedules(id),
    FOREIGN KEY(coupon_id) REFERENCES coupons(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- COUPON USAGES  [FIX-8]
-- One row per redemption; UNIQUE(coupon_id, user_id) enforces
-- one-use-per-user. Drop that constraint if a user may reuse
-- the same coupon.
-- =====================================================

CREATE TABLE coupon_usages (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    coupon_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    booking_id BIGINT NOT NULL,

    used_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uq_coupon_per_user (coupon_id, user_id),
    UNIQUE KEY uq_coupon_per_booking (booking_id),

    FOREIGN KEY(coupon_id) REFERENCES coupons(id),
    FOREIGN KEY(user_id) REFERENCES users(id),
    FOREIGN KEY(booking_id) REFERENCES bookings(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- PASSENGERS
-- =====================================================

CREATE TABLE passengers (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    booking_id BIGINT NOT NULL,

    full_name VARCHAR(150) NOT NULL,                -- [FIX-11]
    age INT,
    gender ENUM('MALE','FEMALE','OTHER'),           -- [FIX-14]
    phone VARCHAR(30),

    CONSTRAINT chk_passenger_age CHECK (age IS NULL OR age BETWEEN 1 AND 120),

    FOREIGN KEY(booking_id) REFERENCES bookings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- BOOKED SEATS  [FIX-1]  ** THE critical fix **
-- schedule_id added; UNIQUE(schedule_id, seat_id) makes it
-- physically impossible to sell the same seat twice for the
-- same trip, no matter what bugs exist in application code.
-- App must still verify seat.bus_id == schedule.bus_id and
-- use SELECT ... FOR UPDATE inside the booking transaction.
-- =====================================================

CREATE TABLE booking_seats (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    booking_id BIGINT NOT NULL,
    schedule_id BIGINT NOT NULL,
    seat_id BIGINT NOT NULL,

    UNIQUE KEY uq_seat_per_trip (schedule_id, seat_id),

    FOREIGN KEY(booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
    FOREIGN KEY(schedule_id) REFERENCES schedules(id),
    FOREIGN KEY(seat_id) REFERENCES seats(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- PAYMENTS
-- =====================================================

CREATE TABLE payments (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    booking_id BIGINT NOT NULL,

    provider ENUM(
        'ESEWA',
        'KHALTI',
        'FONEPAY',
        'CARD'
    ),

    transaction_ref_encrypted TEXT,

    amount DECIMAL(10,2) NOT NULL,                  -- [FIX-11]

    payment_status ENUM(
        'PENDING',
        'SUCCESS',
        'FAILED',
        'REFUNDED'
    ) NOT NULL DEFAULT 'PENDING',                   -- [FIX-11]

    paid_at DATETIME,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- [FIX-10]

    CONSTRAINT chk_payment_amount CHECK (amount >= 0),

    FOREIGN KEY(booking_id) REFERENCES bookings(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- REFUNDS
-- =====================================================

CREATE TABLE refunds (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    payment_id BIGINT NOT NULL,

    amount DECIMAL(10,2) NOT NULL,                  -- [FIX-11]

    status ENUM(
        'PENDING',
        'SUCCESS',
        'FAILED'
    ) NOT NULL DEFAULT 'PENDING',                   -- [FIX-11]

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- [FIX-10]

    CONSTRAINT chk_refund_amount CHECK (amount >= 0),

    FOREIGN KEY(payment_id) REFERENCES payments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- TICKETS
-- =====================================================

CREATE TABLE tickets (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    booking_id BIGINT NOT NULL,

    qr_code_path VARCHAR(255),
    pdf_ticket_path VARCHAR(255),

    checked_in BOOLEAN NOT NULL DEFAULT FALSE,

    issued_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uq_ticket_booking (booking_id),      -- [FIX-9]

    FOREIGN KEY(booking_id) REFERENCES bookings(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- CHECKINS
-- =====================================================

CREATE TABLE checkins (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    ticket_id BIGINT NOT NULL,
    checked_in_by BIGINT NULL,
    checked_in_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uq_checkin_ticket (ticket_id),       -- one check-in per ticket

    FOREIGN KEY(ticket_id) REFERENCES tickets(id),
    FOREIGN KEY(checked_in_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- REVIEWS  [FIX-5]
-- =====================================================

CREATE TABLE reviews (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    user_id BIGINT NOT NULL,
    bus_id BIGINT NOT NULL,

    rating INT NOT NULL,
    review_text TEXT,

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_review_rating CHECK (rating BETWEEN 1 AND 5),
    UNIQUE KEY uq_review_per_user_bus (user_id, bus_id),

    FOREIGN KEY(user_id) REFERENCES users(id),
    FOREIGN KEY(bus_id) REFERENCES buses(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- CANCELLATIONS
-- =====================================================

CREATE TABLE cancellations (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    booking_id BIGINT NOT NULL,

    reason TEXT,
    refund_amount DECIMAL(10,2),

    status ENUM(
        'REQUESTED',
        'APPROVED',
        'REJECTED',
        'REFUNDED'
    ) NOT NULL DEFAULT 'REQUESTED',                 -- [FIX-11]

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- [FIX-10]

    UNIQUE KEY uq_cancellation_booking (booking_id),  -- one request per booking

    CONSTRAINT chk_cancel_refund CHECK (refund_amount IS NULL OR refund_amount >= 0),

    FOREIGN KEY(booking_id) REFERENCES bookings(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- NOTIFICATIONS
-- =====================================================

CREATE TABLE notifications (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    user_id BIGINT NOT NULL,

    type ENUM(
        'EMAIL',
        'SMS',
        'PUSH'
    ) NOT NULL,

    title VARCHAR(255),
    message TEXT,

    is_read BOOLEAN NOT NULL DEFAULT FALSE,

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- [FIX-13] "unread notifications" badge query
    INDEX idx_notification_user (user_id, is_read, created_at),

    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- WAITLIST  [FIX-7]
-- =====================================================

CREATE TABLE waitlists (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    user_id BIGINT NOT NULL,
    schedule_id BIGINT NOT NULL,

    position_no INT,

    status ENUM(
        'WAITING',
        'NOTIFIED',
        'CONFIRMED',
        'EXPIRED'
    ) NOT NULL DEFAULT 'WAITING',

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uq_waitlist_entry (user_id, schedule_id),

    FOREIGN KEY(user_id) REFERENCES users(id),
    FOREIGN KEY(schedule_id) REFERENCES schedules(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- LOYALTY
-- =====================================================

CREATE TABLE loyalty_accounts (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    user_id BIGINT UNIQUE NOT NULL,                 -- [FIX-11]

    points INT NOT NULL DEFAULT 0,

    tier ENUM(
        'SILVER',
        'GOLD',
        'PLATINUM'
    ) NOT NULL DEFAULT 'SILVER',

    CONSTRAINT chk_loyalty_points CHECK (points >= 0),

    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- AUDIT LOGS
-- =====================================================

CREATE TABLE audit_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    user_id BIGINT,

    action VARCHAR(255),
    entity_name VARCHAR(100),
    entity_id BIGINT,

    ip_address VARCHAR(100),

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- [FIX-13] "show me everything that happened to booking #123"
    INDEX idx_audit_entity (entity_name, entity_id, created_at),

    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- SYSTEM SETTINGS
-- =====================================================

CREATE TABLE system_settings (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO system_settings(setting_key,setting_value)
VALUES
('DRAFT_TIMEOUT_MINUTES','10'),
('MAX_LOGIN_ATTEMPTS','5'),
('LOYALTY_POINTS_PER_BOOKING','10');