Sentra Rental — Database Design¶

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 1. Tabel Cabang
CREATE TABLE branches (
    branch_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    branch_name VARCHAR(100) NOT NULL,
    address TEXT NOT NULL,
    city VARCHAR(50) NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 2. Tabel Pengguna (Pelanggan, Staff, Owner)
CREATE TABLE users (
    user_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    branch_id UUID REFERENCES branches(branch_id),
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone_number VARCHAR(20) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL CHECK (role IN (
        'OWNER', 'BRANCH_MANAGER', 'FRONTDESK_ADMIN',
        'INSPECTOR', 'TECHNICIAN', 'CUSTOMER'
    )),
    ktp_number VARCHAR(16) UNIQUE,
    sim_number VARCHAR(16),
    is_verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 3. Tabel Aset Utama
CREATE TABLE assets (
    asset_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    branch_id UUID REFERENCES branches(branch_id),
    asset_name VARCHAR(150) NOT NULL,
    sku VARCHAR(50) NOT NULL,
    serial_number VARCHAR(50) UNIQUE NOT NULL,
    category VARCHAR(50) NOT NULL CHECK (category IN (
        'VEHICLE', 'CAMERA_EQUIPMENT', 'PARTY_ITEMS',
        'OUTDOOR_GEAR', 'PROJECT_TOOLS'
    )),
    purchase_date DATE NOT NULL,
    purchase_price DECIMAL(15, 2) NOT NULL,
    salvage_value DECIMAL(15, 2) NOT NULL,
    useful_life_months INT NOT NULL,         -- untuk depresiasi garis lurus
    daily_rental_rate DECIMAL(12, 2) NOT NULL,
    hourly_rental_rate DECIMAL(12, 2) NOT NULL,
    current_status VARCHAR(20) NOT NULL CHECK (current_status IN (
        'AVAILABLE', 'BOOKED', 'MAINTENANCE', 'DAMAGED', 'LOST', 'RETIRED'
    )),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_assets_sku ON assets(sku);
CREATE INDEX idx_assets_status ON assets(current_status);

-- 4. Tabel Histori Kondisi Aset (Lifecycle)
CREATE TABLE asset_conditions (
    condition_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    asset_id UUID REFERENCES assets(asset_id) ON DELETE CASCADE,
    checked_by UUID REFERENCES users(user_id),
    overall_condition VARCHAR(15) NOT NULL CHECK (overall_condition IN (
        'EXCELLENT', 'GOOD', 'FAIR', 'POOR', 'BROKEN'
    )),
    damage_notes TEXT,
    photo_urls TEXT[] NOT NULL,             -- Array URL ke S3
    recorded_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 5. Tabel Booking Utama
CREATE TABLE bookings (
    booking_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    customer_id UUID REFERENCES users(user_id) NOT NULL,
    branch_id UUID REFERENCES branches(branch_id) NOT NULL,
    booking_code VARCHAR(15) UNIQUE NOT NULL, -- Format: SR/YYYYMMDD/XXXXX
    total_amount DECIMAL(15, 2) NOT NULL,
    ppn_amount DECIMAL(15, 2) NOT NULL DEFAULT 0.00,  -- PPN 11%
    payment_status VARCHAR(20) NOT NULL CHECK (payment_status IN (
        'UNPAID', 'PAID', 'PARTIALLY_REFUNDED', 'REFUNDED'
    )),
    booking_status VARCHAR(20) NOT NULL CHECK (booking_status IN (
        'PENDING', 'APPROVED', 'DISPATCHED', 'RETURNED', 'COMPLETED', 'CANCELLED'
    )),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 6. Tabel Detail Item Booking
CREATE TABLE booking_details (
    detail_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    booking_id UUID REFERENCES bookings(booking_id) ON DELETE CASCADE,
    asset_id UUID REFERENCES assets(asset_id) NOT NULL,
    start_time TIMESTAMP WITH TIME ZONE NOT NULL,
    end_time TIMESTAMP WITH TIME ZONE NOT NULL,
    price_applied DECIMAL(12, 2) NOT NULL,  -- Harga pada saat booking
    actual_return_time TIMESTAMP WITH TIME ZONE,
    late_fee_applied DECIMAL(12, 2) DEFAULT 0.00,
    damage_fee_applied DECIMAL(12, 2) DEFAULT 0.00
);

-- 7. Tabel Uang Jaminan (Deposit)
CREATE TABLE deposits (
    deposit_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    booking_id UUID REFERENCES bookings(booking_id) ON DELETE CASCADE,
    amount_held DECIMAL(12, 2) NOT NULL,
    payment_gateway_ref VARCHAR(100) UNIQUE,    -- Xendit/Midtrans pre-auth charge ID
    deposit_status VARCHAR(20) NOT NULL CHECK (deposit_status IN (
        'HELD', 'RELEASED', 'FORFEITED', 'CLAIMED_FOR_DAMAGES'
    )),
    held_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    released_at TIMESTAMP WITH TIME ZONE
);

-- 8. Tabel Inspeksi Handover (Check-Out / Check-In)
CREATE TABLE handover_inspections (
    inspection_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    booking_id UUID REFERENCES bookings(booking_id) ON DELETE CASCADE,
    asset_id UUID REFERENCES assets(asset_id) NOT NULL,
    inspection_type VARCHAR(10) NOT NULL CHECK (inspection_type IN ('CHECK_OUT', 'CHECK_IN')),
    inspector_id UUID REFERENCES users(user_id) NOT NULL,
    odometer_reading INT,                       -- opsional untuk kendaraan
    fuel_or_battery_level VARCHAR(20),          -- opsional
    checklist_json JSONB NOT NULL,              -- fleksibel untuk berbagai jenis barang
    photo_urls TEXT[] NOT NULL,
    digital_signature_url VARCHAR(255) NOT NULL,
    tamper_proof_hash CHAR(64) NOT NULL,        -- SHA-256 dari seluruh payload
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 9. Tabel Perawatan Unit (Maintenance)
CREATE TABLE maintenance_logs (
    maintenance_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    asset_id UUID REFERENCES assets(asset_id) ON DELETE CASCADE,
    technician_id UUID REFERENCES users(user_id),
    maintenance_type VARCHAR(20) NOT NULL CHECK (maintenance_type IN (
        'ROUTINE_SERVICE', 'REPAIR', 'CALIBRATION', 'CLEANING'
    )),
    description TEXT NOT NULL,
    parts_replaced JSONB,                       -- daftar part yang diganti
    cost_incurred DECIMAL(12, 2) NOT NULL,
    downtime_started TIMESTAMP WITH TIME ZONE NOT NULL,
    downtime_ended TIMESTAMP WITH TIME ZONE,
    next_service_date DATE
);

-- 10. Tabel Komisi & Payroll Staff Operasional
CREATE TABLE staff_commissions (
    commission_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    staff_id UUID REFERENCES users(user_id) NOT NULL,
    booking_detail_id UUID REFERENCES booking_details(detail_id) ON DELETE SET NULL,
    amount_earned DECIMAL(12, 2) NOT NULL,
    incentive_type VARCHAR(20) NOT NULL CHECK (incentive_type IN (
        'CHECKOUT_DISPATCH', 'CHECKIN_VERIFICATION', 'COMPLIANCE_BONUS'
    )),
    calculated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);