Sentra Retail — Database Design¶

-- 1. Tabel Stores (Cabang / Gudang)
CREATE TABLE stores (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    address TEXT,
    phone VARCHAR(20),
    type VARCHAR(20) CHECK (type IN ('STORE', 'WAREHOUSE', 'STORE_WITH_WAREHOUSE')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 2. Tabel Users (Karyawan)
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    store_id UUID REFERENCES stores(id),
    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', 'STORE_MANAGER', 'CASHIER', 'WAREHOUSE_STAFF', 'AUDITOR')),
    pin VARCHAR(6) NOT NULL, -- PIN untuk otorisasi cepat/Void di POS
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 3. Tabel Products
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sku VARCHAR(50) UNIQUE NOT NULL,
    barcode VARCHAR(50) UNIQUE,
    name VARCHAR(150) NOT NULL,
    category VARCHAR(50) NOT NULL,
    base_price DECIMAL(15,2) NOT NULL, -- Harga Pokok Pembelian (HPP)
    tax_rate DECIMAL(5,2) DEFAULT 11.00, -- Default PPN 11%
    has_variant BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 4. Tabel Product Variants (Mendukung Varian Ukuran/Warna)
CREATE TABLE product_variants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id UUID REFERENCES products(id) ON DELETE CASCADE,
    variant_sku VARCHAR(50) UNIQUE NOT NULL,
    variant_barcode VARCHAR(50) UNIQUE,
    size VARCHAR(20),
    color VARCHAR(20),
    additional_price DECIMAL(15,2) DEFAULT 0.00,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 5. Tabel Inventories (Stok di Gudang & Cabang)
CREATE TABLE inventories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    store_id UUID REFERENCES stores(id),
    product_variant_id UUID REFERENCES product_variants(id),
    qty INT NOT NULL DEFAULT 0,
    minimum_stock INT NOT NULL DEFAULT 5,
    batch_number VARCHAR(50), -- Khusus Apotek / Kosmetik
    expired_date DATE,        -- Khusus Apotek / Kosmetik
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(store_id, product_variant_id, batch_number)
);

-- 6. Tabel Members (Loyalty Program)
CREATE TABLE members (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) UNIQUE NOT NULL,
    tier VARCHAR(20) CHECK (tier IN ('BRONZE', 'SILVER', 'GOLD', 'PLATINUM')) DEFAULT 'BRONZE',
    points INT DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 7. Tabel Shifts
CREATE TABLE shifts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id),
    store_id UUID REFERENCES stores(id),
    start_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    end_time TIMESTAMP WITH TIME ZONE,
    start_cash_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00, -- Uang modal laci kasir
    end_cash_expected DECIMAL(15,2), -- Hitungan sistem
    end_cash_declared DECIMAL(15,2), -- Inputan fisik kasir saat tutup shift
    status VARCHAR(20) CHECK (status IN ('OPEN', 'CLOSED')) DEFAULT 'OPEN'
);

-- 8. Tabel Transactions
CREATE TABLE transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Menggunakan UUID agar aman di-generate offline
    store_id UUID REFERENCES stores(id),
    user_id UUID REFERENCES users(id),
    shift_id UUID REFERENCES shifts(id),
    member_id UUID REFERENCES members(id),
    invoice_number VARCHAR(100) UNIQUE NOT NULL,
    subtotal DECIMAL(15,2) NOT NULL,
    discount_amount DECIMAL(15,2) DEFAULT 0.00,
    tax_amount DECIMAL(15,2) DEFAULT 0.00,
    rounding_amount DECIMAL(15,2) DEFAULT 0.00,
    grand_total DECIMAL(15,2) NOT NULL,
    payment_method VARCHAR(30) CHECK (payment_method IN ('CASH', 'QRIS', 'DEBIT_CARD', 'CREDIT_CARD', 'E_WALLET', 'SPLIT')),
    payment_status VARCHAR(20) CHECK (payment_status IN ('PAID', 'REFUNDED')) DEFAULT 'PAID',
    is_synced BOOLEAN DEFAULT FALSE, -- Flag sinkronisasi offline ke online
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 9. Tabel Transaction Details
CREATE TABLE transaction_details (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    transaction_id UUID REFERENCES transactions(id) ON DELETE CASCADE,
    product_variant_id UUID REFERENCES product_variants(id),
    qty INT NOT NULL,
    unit_price DECIMAL(15,2) NOT NULL,
    discount_amount DECIMAL(15,2) DEFAULT 0.00,
    tax_amount DECIMAL(15,2) DEFAULT 0.00,
    net_price DECIMAL(15,2) NOT NULL, -- (unit_price * qty) - discount_amount
    commission_earned DECIMAL(15,2) DEFAULT 0.00 -- Komisi kasir dari item ini
);