Sentra Laundry — Database Design¶

-- 1. Tabel Outlets (Daftar Cabang Laundry)
CREATE TABLE outlets (
    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 (Pegawai: Kasir, Operator Cuci, Setrika, QC, Kurir)
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', 'OUTLET_MANAGER', 'CASHIER_FRONTDESK', 'OPERATOR_WASHING', 'OPERATOR_IRONING', 'QC_PACKER', 'COURIER')),
    base_salary DECIMAL(15,2) DEFAULT 0.00,
    performance_score DECIMAL(3,2) DEFAULT 5.00, -- Nilai rating kepatuhan SLA (1-5)
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 3. Tabel Customers (Membership Program)
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 ('REGULAR', 'SILVER', 'GOLD', 'VIP')) DEFAULT 'REGULAR',
    loyalty_points INT DEFAULT 0,
    subscription_balance DECIMAL(15,2) DEFAULT 0.00, -- Saldo paket kuota kiloan prabayar
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 4. Tabel Laundry Services (Jasa Laundry Kiloan & Satuan Premium)
CREATE TABLE laundry_services (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(150) NOT NULL,
    category VARCHAR(50) CHECK (category IN ('KILOAN', 'SATUAN_PREMIUM', 'SHOES_BAG', 'AUTO_DETAILING', 'HOME_CLEANING')),
    price_per_unit DECIMAL(15,2) NOT NULL, -- Harga per kg atau per pcs
    unit_type VARCHAR(10) CHECK (unit_type IN ('KG', 'PCS', 'METER')), -- Satuan ukur
    duration_hours_regular INT NOT NULL DEFAULT 72, -- SLA Regular (3 hari)
    duration_hours_express INT NOT NULL DEFAULT 6,  -- SLA Express (6 jam)
    is_active BOOLEAN DEFAULT TRUE
);

-- 5. Tabel Laundry Orders
CREATE TABLE laundry_orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    outlet_id UUID REFERENCES outlets(id),
    customer_id UUID REFERENCES customers(id),
    order_number VARCHAR(100) UNIQUE NOT NULL,
    service_type VARCHAR(20) CHECK (service_type IN ('REGULAR', 'EXPRESS', 'SUPER_EXPRESS')) DEFAULT 'REGULAR',
    subtotal DECIMAL(15,2) NOT NULL,
    express_surcharge DECIMAL(15,2) DEFAULT 0.00,
    grand_total DECIMAL(15,2) NOT NULL,
    payment_status VARCHAR(20) CHECK (payment_status IN ('UNPAID', 'PAID', 'COD_RECONCILE')) DEFAULT 'UNPAID',
    order_status VARCHAR(30) CHECK (order_status IN ('RECEIVED', 'PROCESSING', 'QC_COMPLETED', 'SHIPPING', 'READY_FOR_COLLECTION', 'COMPLETED', 'REWASH_HOLD')) DEFAULT 'RECEIVED',
    sla_deadline TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 6. Tabel Laundry Order Items (Pakaian Konsumen per Order)
CREATE TABLE laundry_order_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    laundry_order_id UUID REFERENCES laundry_orders(id) ON DELETE CASCADE,
    service_id UUID REFERENCES laundry_services(id),
    weight_kg DECIMAL(6,2), -- Terisi jika kategori KILOAN
    qty_pcs INT NOT NULL DEFAULT 1, -- Jumlah fisik pakaian
    fabric_notes VARCHAR(255), -- Catatan jenis kain (e.g. Sutra, Katun)
    special_treatment TEXT
);

-- 7. Tabel Item Photos (Dokumentasi Kondisi Awal Pakaian Premium/Sepatu)
CREATE TABLE item_photos (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_item_id UUID REFERENCES laundry_order_items(id) ON DELETE CASCADE,
    photo_url TEXT NOT NULL, -- URL CDN penyimpanan foto noda/kerusakan bawaan
    description VARCHAR(255), -- Deskripsi noda/sobekan saat diterima
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 8. Tabel Machines (Manajemen Mesin Cuci / Pengering)
CREATE TABLE machines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    outlet_id UUID REFERENCES outlets(id),
    name VARCHAR(50) NOT NULL, -- Washer-01, Dryer-02
    type VARCHAR(20) CHECK (type IN ('WASHER', 'DRYER', 'DRY_CLEANING_MACHINE')),
    capacity_kg DECIMAL(5,2) NOT NULL, -- Kapasitas maksimum beban mesin
    status VARCHAR(20) CHECK (status IN ('IDLE', 'RUNNING', 'MAINTENANCE', 'BROKEN')) DEFAULT 'IDLE'
);

-- 9. Tabel Operational Queues (Antrean Kerja Staf BOH)
CREATE TABLE operational_queues (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_item_id UUID REFERENCES laundry_order_items(id) ON DELETE CASCADE,
    operator_id UUID REFERENCES users(id), -- Staf pelaksana stasiun ini
    machine_id UUID REFERENCES machines(id), -- Mesin yang digunakan
    station_type VARCHAR(20) CHECK (station_type IN ('WASHING', 'DRYING', 'IRONING', 'QC')),
    status VARCHAR(20) CHECK (status IN ('PENDING', 'PROCESSING', 'COMPLETED', 'REWASH')) DEFAULT 'PENDING',
    started_at TIMESTAMP WITH TIME ZONE,
    completed_at TIMESTAMP WITH TIME ZONE
);

-- 10. Tabel Courier Dispatches (Pengiriman Pickup & Delivery)
CREATE TABLE courier_dispatches (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    laundry_order_id UUID REFERENCES laundry_orders(id),
    courier_id UUID REFERENCES users(id),
    dispatch_type VARCHAR(20) CHECK (dispatch_type IN ('PICKUP', 'DELIVERY')),
    delivery_fee DECIMAL(15,2) DEFAULT 0.00,
    google_maps_distance_km DECIMAL(5,2),
    status VARCHAR(20) CHECK (status IN ('ASSIGNED', 'PICKING_UP', 'COLLECTED', 'DELIVERING', 'DELIVERED', 'FAILED')) DEFAULT 'ASSIGNED',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP WITH TIME ZONE
);