-- =========================================================
-- LoungeOS — Product recipes (composite items), per location
-- =========================================================

CREATE TABLE product_recipes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    location_id INT NOT NULL,
    finished_product_id INT NOT NULL,      -- e.g. "Shawarma Double"
    component_product_id INT NOT NULL,     -- e.g. "Hotdog"
    qty_per_unit DECIMAL(10,2) NOT NULL DEFAULT 1,  -- how many of the component are used per 1 unit sold
    UNIQUE KEY unique_recipe_line (location_id, finished_product_id, component_product_id),
    FOREIGN KEY (location_id) REFERENCES locations(id),
    FOREIGN KEY (finished_product_id) REFERENCES products(id),
    FOREIGN KEY (component_product_id) REFERENCES products(id)
);

-- Widen stock columns to support fractional recipe quantities (e.g. 0.5 kg of an ingredient per unit sold)
ALTER TABLE location_stock MODIFY quantity DECIMAL(10,2) NOT NULL DEFAULT 0;
ALTER TABLE stock_transactions MODIFY change_qty DECIMAL(10,2) NOT NULL;