-- Order ingredients transactions table
CREATE TABLE order_ingredients_transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    outlet_id UUID REFERENCES outlets(id) ON DELETE CASCADE,
    order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    order_item_id UUID REFERENCES order_items(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    product_variant_id UUID REFERENCES product_variants(id) ON DELETE CASCADE,
    ingredient_id UUID NOT NULL REFERENCES ingredients(id) ON DELETE CASCADE,
    gross_qty DECIMAL(12,3) NOT NULL CHECK (gross_qty > 0),
    net_qty DECIMAL(12,3) NOT NULL CHECK (net_qty > 0),
    waste_qty DECIMAL(12,3) NOT NULL CHECK (waste_qty >= 0),
    unit VARCHAR(50) NOT NULL,
    transaction_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    created_by UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_order_ingredients_transactions_organization_id ON order_ingredients_transactions(organization_id);
CREATE INDEX idx_order_ingredients_transactions_outlet_id ON order_ingredients_transactions(outlet_id);
CREATE INDEX idx_order_ingredients_transactions_order_id ON order_ingredients_transactions(order_id);
CREATE INDEX idx_order_ingredients_transactions_order_item_id ON order_ingredients_transactions(order_item_id);
CREATE INDEX idx_order_ingredients_transactions_product_id ON order_ingredients_transactions(product_id);
CREATE INDEX idx_order_ingredients_transactions_product_variant_id ON order_ingredients_transactions(product_variant_id);
CREATE INDEX idx_order_ingredients_transactions_ingredient_id ON order_ingredients_transactions(ingredient_id);
CREATE INDEX idx_order_ingredients_transactions_transaction_date ON order_ingredients_transactions(transaction_date);
CREATE INDEX idx_order_ingredients_transactions_created_by ON order_ingredients_transactions(created_by);
CREATE INDEX idx_order_ingredients_transactions_created_at ON order_ingredients_transactions(created_at);

-- Add comment to explain the table
COMMENT ON TABLE order_ingredients_transactions IS 'Tracks ingredient usage for orders including gross, net, and waste quantities';
COMMENT ON COLUMN order_ingredients_transactions.gross_qty IS 'Total quantity needed including waste';
COMMENT ON COLUMN order_ingredients_transactions.net_qty IS 'Actual quantity used in the product';
COMMENT ON COLUMN order_ingredients_transactions.waste_qty IS 'Waste quantity (gross_qty - net_qty)';