BEGIN;

-- Minimum sell price
ALTER TABLE products ADD COLUMN min_sell_price DECIMAL(10,2) DEFAULT 0.00;

-- Spiff fields
ALTER TABLE products ADD COLUMN spiff_enabled BOOLEAN DEFAULT FALSE;
ALTER TABLE products ADD COLUMN spiff_amount DECIMAL(10,2) DEFAULT 0.00;
ALTER TABLE products ADD COLUMN spiff_type VARCHAR(20) DEFAULT 'always'
    CHECK (spiff_type IN ('always', 'deadline', 'quantity'));
ALTER TABLE products ADD COLUMN spiff_deadline DATE;
ALTER TABLE products ADD COLUMN spiff_quantity INT DEFAULT 0;
ALTER TABLE products ADD COLUMN spiff_quantity_sold INT DEFAULT 0;

-- Custom fields (configurable per tenant)
CREATE TABLE product_custom_fields (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    field_number INT NOT NULL CHECK (field_number IN (1, 2, 3)),
    label VARCHAR(100) NOT NULL,
    field_type VARCHAR(20) DEFAULT 'text' CHECK (field_type IN ('text', 'number', 'date', 'boolean')),
    is_active BOOLEAN DEFAULT TRUE,
    UNIQUE(tenant_id, field_number)
);

-- Custom field values per product
ALTER TABLE products ADD COLUMN custom_field_1 TEXT;
ALTER TABLE products ADD COLUMN custom_field_2 TEXT;
ALTER TABLE products ADD COLUMN custom_field_3 TEXT;

VALUES ('009', 'Products: min sell price, spiffs, custom fields');

COMMIT;
