-- Migration 005: WasSerba (Warung Serba Ada) Module

-- 1. ws_categories
CREATE TABLE ws_categories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 2. ws_products
CREATE TABLE ws_products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    category_id UUID REFERENCES ws_categories(id) ON DELETE SET NULL,
    sku VARCHAR(100) UNIQUE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    cost_price DECIMAL(15, 2) NOT NULL DEFAULT 0,
    selling_price DECIMAL(15, 2) NOT NULL DEFAULT 0,
    stock INT NOT NULL DEFAULT 0,
    min_stock INT NOT NULL DEFAULT 5,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 3. ws_transactions (Prepared for Phase 3 POS)
CREATE TABLE ws_transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    transaction_code VARCHAR(50) UNIQUE NOT NULL,
    member_id UUID REFERENCES members(id) ON DELETE SET NULL,
    cashier_id UUID REFERENCES users(id) ON DELETE SET NULL,
    total_amount DECIMAL(15, 2) NOT NULL DEFAULT 0,
    payment_method VARCHAR(50) NOT NULL, -- 'cash', 'simpanan', 'qris', etc.
    status VARCHAR(50) DEFAULT 'completed',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 4. ws_transaction_items
CREATE TABLE ws_transaction_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    transaction_id UUID REFERENCES ws_transactions(id) ON DELETE CASCADE,
    product_id UUID REFERENCES ws_products(id) ON DELETE SET NULL,
    quantity INT NOT NULL,
    cost_price DECIMAL(15, 2) NOT NULL, -- Snapshot of cost_price at the time
    selling_price DECIMAL(15, 2) NOT NULL, -- Snapshot of selling_price at the time
    subtotal DECIMAL(15, 2) NOT NULL
);

-- Default system settings for WasSerba
INSERT INTO system_settings (setting_key, setting_value, description) VALUES
('WS_ACC_RECEIVABLE', '1200', 'Akun Piutang untuk pembayaran potong gaji WasSerba'),
;