Discover the Untouched Beauty of Eastern Himalayas

CREATE TABLE destinations (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            VARCHAR(100) NOT NULL,
    slug            VARCHAR(100) UNIQUE NOT NULL,
    tagline         VARCHAR(200),
    description     TEXT,
    altitude_ft     INTEGER,
    best_season     VARCHAR(50),         -- 'Oct-Mar', 'Mar-Jun', 'Year-round'
    image_url       TEXT,
    gallery         JSONB,               -- ['url1', 'url2', ...]
    highlights      JSONB,               -- ['Tiger Hill', 'Tea Estate', ...]
    tags            TEXT[],               -- '{hill-station, cultural, nature}'
    is_active       BOOLEAN DEFAULT true,
    sort_order      INTEGER DEFAULT 0,
    created_at      TIMESTAMPTZ DEFAULT now(),
    updated_at      TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE packages (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            VARCHAR(150) NOT NULL,
    slug            VARCHAR(150) UNIQUE NOT NULL,
    destination_id  UUID REFERENCES destinations(id),
    duration_days   INTEGER NOT NULL,
    duration_nights INTEGER NOT NULL,
    description     TEXT,
    itinerary       JSONB,               -- [{day: 1, title: "...", activities: [...]}]
    inclusions      JSONB,
    exclusions      JSONB,
    price_per_person DECIMAL(10,2) NOT NULL,
    currency        VARCHAR(3) DEFAULT 'INR',
    discount_pct    DECIMAL(5,2) DEFAULT 0,
    max_group_size  INTEGER,
    difficulty      VARCHAR(20),         -- 'easy', 'moderate', 'challenging'
    is_featured     BOOLEAN DEFAULT false,
    is_active       BOOLEAN DEFAULT true,
    rating          DECIMAL(2,1),
    review_count    INTEGER DEFAULT 0,
    created_at      TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE accommodations (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            VARCHAR(150) NOT NULL,
    slug            VARCHAR(150) UNIQUE NOT NULL,
    destination_id  UUID REFERENCES destinations(id),
    type            VARCHAR(30),         -- 'homestay', 'hotel', 'resort', 'glamping'
    description     TEXT,
    amenities       JSONB,               -- ['wifi', 'parking', 'meals', ...]
    rooms           JSONB,               -- [{type: "deluxe", price: 3500, capacity: 2}]
    price_from      DECIMAL(10,2) NOT NULL,
    eco_certified   BOOLEAN DEFAULT false,
    image_url       TEXT,
    gallery         JSONB,
    rating          DECIMAL(2,1),
    is_active       BOOLEAN DEFAULT true,
    created_at      TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE vehicles (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    type            VARCHAR(30) NOT NULL,   -- 'sedan', 'suv', 'tempo', 'bus'
    model           VARCHAR(100),
    capacity        INTEGER NOT NULL,
    price_per_km    DECIMAL(8,2) NOT NULL,
    min_daily_rate  DECIMAL(8,2) NOT NULL,
    features        JSONB,               -- ['AC', 'GPS', 'first-aid', ...]
    is_available    BOOLEAN DEFAULT true,
    image_url       TEXT,
    created_at      TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE bookings (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    booking_ref     VARCHAR(20) UNIQUE NOT NULL,  -- 'EHT-2024-XXXXX'
    user_id         UUID REFERENCES users(id),
    booking_type    VARCHAR(20) NOT NULL,  -- 'package', 'accommodation', 'transport', 'permit'
    package_id      UUID REFERENCES packages(id),
    accommodation_id UUID REFERENCES accommodations(id),
    vehicle_id      UUID REFERENCES vehicles(id),
    guest_name      VARCHAR(150) NOT NULL,
    guest_email     VARCHAR(200) NOT NULL,
    guest_phone     VARCHAR(20) NOT NULL,
    guests_count    INTEGER NOT NULL,
    check_in        DATE NOT NULL,
    check_out       DATE,
    total_amount    DECIMAL(12,2) NOT NULL,
    discount_amount DECIMAL(12,2) DEFAULT 0,
    final_amount    DECIMAL(12,2) NOT NULL,
    currency        VARCHAR(3) DEFAULT 'INR',
    status          VARCHAR(20) DEFAULT 'pending',  -- pending, confirmed, paid, completed, cancelled
    payment_status  VARCHAR(20) DEFAULT 'unpaid',
    payment_id      VARCHAR(100),       -- Razorpay/Stripe payment reference
    special_requests TEXT,
    permit_details  JSONB,               -- {type: "nathula", status: "approved"}
    notes           TEXT,                -- Internal admin notes
    created_at      TIMESTAMPTZ DEFAULT now(),
    updated_at      TIMESTAMPTZ DEFAULT now()
);

-- Indexes for performance
CREATE INDEX idx_bookings_user ON bookings(user_id);
CREATE INDEX idx_bookings_status ON bookings(status);
CREATE INDEX idx_bookings_date ON bookings(check_in);
CREATE INDEX idx_bookings_type ON bookings(booking_type);