BEGIN;

-- Max stock quantity per product
ALTER TABLE products ADD COLUMN max_stock_threshold INT DEFAULT 0;

-- Product returns tracking
CREATE TABLE product_returns (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    location_id UUID NOT NULL REFERENCES locations(id) ON DELETE CASCADE,
    quantity INT NOT NULL DEFAULT 1,
    reason VARCHAR(50) NOT NULL CHECK (reason IN ('defective', 'damaged', 'wrong_item', 'customer_return', 'other')),
    notes TEXT,
    returned_by UUID NOT NULL REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_product_returns_product ON product_returns(product_id);
CREATE INDEX idx_product_returns_date ON product_returns(created_at DESC);
CREATE INDEX idx_product_returns_tenant ON product_returns(tenant_id);

VALUES ('008', 'Inventory: max stock threshold, product returns table');

COMMIT;
