booking.sql

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(120) UNIQUE NOT NULL,
    phone VARCHAR(30),
    password_hash TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Category for resources (ex: Meeting Room, VIP Room, Massage Service, etc)
CREATE TABLE resource_types (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(120) NOT NULL
);

-- Bookable resources (ex: Room A, Room B, Haircut Service)
CREATE TABLE resources (
    id BIGSERIAL PRIMARY KEY,
    type_id BIGINT REFERENCES resource_types(id),
    name VARCHAR(160) NOT NULL,
    description TEXT,
    price_per_unit DECIMAL(10,2) NOT NULL, -- price per hour/day/service
    unit VARCHAR(30) DEFAULT 'hour', -- hour/day/session
    is_active BOOLEAN DEFAULT TRUE
);

-- User creates a booking
CREATE TABLE bookings (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id),
    status VARCHAR(50) DEFAULT 'pending', -- pending, confirmed, cancelled, completed
    total_amount DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Resources inside the booking with date/time range
CREATE TABLE booking_items (
    id BIGSERIAL PRIMARY KEY,
    booking_id BIGINT REFERENCES bookings(id),
    resource_id BIGINT REFERENCES resources(id),
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    quantity INT DEFAULT 1
);

-- Payment record
CREATE TABLE payments (
    id BIGSERIAL PRIMARY KEY,
    booking_id BIGINT REFERENCES bookings(id),
    amount DECIMAL(10,2) NOT NULL,
    provider VARCHAR(50), -- ABA, Wing, Stripe, PayPal
    status VARCHAR(50) DEFAULT 'pending',
    paid_at TIMESTAMP
);


-- 🎯 Core Requirements of a Booking System

-- Users can register + login

-- There are resources that can be booked (rooms / services / items)

-- Bookings have date/time and status

-- One booking may include payments

-- Support for availability checking

-- | Feature                     | Description                    |
-- | --------------------------- | ------------------------------ |
-- | Staff Accounts              | Admin & employee roles         |
-- | Cancellation & Refund rules | Flexible booking policy        |
-- | Calendar View API           | For showing availability in UI |
-- | Notifications               | Telegram/Email/WhatsApp alerts |
-- | Price rules                 | Weekend / seasonal pricing     |