Sentra Service — Database Design¶

-- 1. Tabel Branches (Cabang Outlet)
CREATE TABLE branches (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    address TEXT,
    phone VARCHAR(20),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 2. Tabel Users (Mencakup Staf Jasa seperti Kapster/Mekanik/Terapis)
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role VARCHAR(30) CHECK (role IN ('OWNER', 'SYSTEM_ADMIN', 'BRANCH_MANAGER', 'RECEPTIONIST_CASHIER', 'SERVICE_STAFF')),
    specialty_skills VARCHAR(255)[], -- Kumpulan keahlian, e.g. {'Haircut', 'Coloring'}
    base_salary DECIMAL(15,2) DEFAULT 0.00,
    commission_percentage DECIMAL(5,2) DEFAULT 0.00, -- Persentase komisi umum staf
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 3. Tabel Customers
CREATE TABLE customers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    phone VARCHAR(20) UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    membership_tier VARCHAR(20) CHECK (membership_tier IN ('BRONZE', 'SILVER', 'GOLD', 'PLATINUM')) DEFAULT 'BRONZE',
    loyalty_points INT DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 4. Tabel Services (Daftar Layanan Jasa)
CREATE TABLE services (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(150) NOT NULL,
    category VARCHAR(50) NOT NULL, -- Hair, Massage, Engine, dll.
    duration_minutes INT NOT NULL DEFAULT 30, -- Estimasi durasi dasar
    price DECIMAL(15,2) NOT NULL,
    commission_type VARCHAR(10) CHECK (commission_type IN ('FIXED', 'PERCENTAGE')) DEFAULT 'PERCENTAGE',
    commission_value DECIMAL(15,2) NOT NULL, -- Nilai komisi staf per jasa ini
    is_active BOOLEAN DEFAULT TRUE
);

-- 5. Tabel Staff Schedules (Jadwal Roster Shift Kerja Staf)
CREATE TABLE staff_schedules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    branch_id UUID REFERENCES branches(id) ON DELETE CASCADE,
    work_date DATE NOT NULL,
    shift_start TIME NOT NULL,
    shift_end TIME NOT NULL,
    break_start TIME,
    break_end TIME,
    is_off_day BOOLEAN DEFAULT FALSE,
    UNIQUE(user_id, work_date)
);

-- 6. Tabel Appointments (Pemesanan Slot Waktu Jasa)
CREATE TABLE appointments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    branch_id UUID REFERENCES branches(id),
    customer_id UUID REFERENCES customers(id),
    booking_number VARCHAR(100) UNIQUE NOT NULL,
    appointment_date DATE NOT NULL,
    scheduled_start TIMESTAMP WITH TIME ZONE NOT NULL,
    scheduled_end TIMESTAMP WITH TIME ZONE NOT NULL, -- Start + Total Duration
    deposit_amount DECIMAL(15,2) DEFAULT 0.00,
    source VARCHAR(20) CHECK (source IN ('ONLINE_WIDGET', 'WALK_IN', 'WHATSAPP_BOT')),
    status VARCHAR(30) CHECK (status IN ('PENDING_PAYMENT', 'CONFIRMED', 'CHECKED_IN', 'IN_SERVICE', 'COMPLETED', 'NO_SHOW', 'CANCELLED')) DEFAULT 'CONFIRMED',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 7. Tabel Appointment Items (Detail Jasa Terpesan & Staff Assignment)
CREATE TABLE appointment_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    appointment_id UUID REFERENCES appointments(id) ON DELETE CASCADE,
    service_id UUID REFERENCES services(id),
    assigned_staff_id UUID REFERENCES users(id), -- Staf pelaksana jasa
    price_charged DECIMAL(15,2) NOT NULL,
    sequence_order INT NOT NULL DEFAULT 1 -- Urutan pengerjaan jika multi-jasa
);

-- 8. Tabel Transactions
CREATE TABLE transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    appointment_id UUID REFERENCES appointments(id) ON DELETE SET NULL,
    branch_id UUID REFERENCES branches(id),
    cashier_id UUID REFERENCES users(id),
    invoice_number VARCHAR(100) UNIQUE NOT NULL,
    subtotal_services DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    subtotal_retail DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(15,2) DEFAULT 0.00,
    tax_amount DECIMAL(15,2) DEFAULT 0.00, -- PPN 11%
    grand_total DECIMAL(15,2) NOT NULL,
    payment_method VARCHAR(30) CHECK (payment_method IN ('CASH', 'QRIS', 'DEBIT_CARD', 'CREDIT_CARD', 'SPLIT')),
    rounding_amount DECIMAL(15,2) DEFAULT 0.00,
    tips_amount DECIMAL(15,2) DEFAULT 0.00, -- Tip sukarela pelanggan
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 9. Tabel Staff Commissions (Audit Trail Hak Insentif Staf)
CREATE TABLE staff_commissions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    transaction_id UUID REFERENCES transactions(id) ON DELETE CASCADE,
    staff_id UUID REFERENCES users(id),
    source_type VARCHAR(20) CHECK (source_type IN ('SERVICE', 'RETAIL_PRODUCT')),
    commission_amount DECIMAL(15,2) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);