-- ============================================
-- BAYAZID FARM MANAGEMENT SYSTEM
-- Inventory sync: Feed records deduct stock, Milk records add stock
-- Run this once, AFTER schema_phase6.sql (inventory tables must exist)
-- ============================================

USE technica_bayezid;

-- Add "Produce" category (for milk and future sellable outputs)
ALTER TABLE inventory_items MODIFY COLUMN category ENUM('Feed','Medicine','Equipment','Produce','Other') NOT NULL DEFAULT 'Other';

-- Link feed records to the inventory item they were deducted from (nullable — free-text feed still allowed)
ALTER TABLE feed_records ADD COLUMN inventory_item_id INT NULL AFTER feed_type;
ALTER TABLE feed_records ADD CONSTRAINT fk_feed_inventory_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items(id) ON DELETE SET NULL;

-- Link milk records to the "Milk" inventory item they were added to
ALTER TABLE milk_records ADD COLUMN inventory_item_id INT NULL AFTER quantity_liters;
ALTER TABLE milk_records ADD CONSTRAINT fk_milk_inventory_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items(id) ON DELETE SET NULL;