-- Production Module Database Schema

-- 1. Production Plans Table
CREATE TABLE production_plans (
    id INT PRIMARY KEY AUTO_INCREMENT,
    plan_name VARCHAR(100) NOT NULL,
    product_id INT NOT NULL,
    planned_quantity DECIMAL(10,2) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status ENUM('draft', 'planned', 'in_progress', 'completed', 'cancelled') DEFAULT 'draft',
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    notes TEXT,
    created_by INT,
    approved_by INT,
    approved_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (approved_by) REFERENCES users(id),
    CHECK (end_date >= start_date)
);

-- 2. Recipes Table
CREATE TABLE recipes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    yield_quantity DECIMAL(10,2) DEFAULT 1.00, -- Quantity of finished product this recipe produces
    standard_cost DECIMAL(15,2) DEFAULT 0,
    created_by INT,
    updated_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (updated_by) REFERENCES users(id)
);

-- 3. Recipe Ingredients Table
CREATE TABLE recipe_ingredients (
    id INT PRIMARY KEY AUTO_INCREMENT,
    recipe_id INT NOT NULL,
    ingredient_product_id INT NOT NULL, -- The raw material
    quantity_needed DECIMAL(10,2) NOT NULL, -- Quantity per unit of finished product
    waste_percentage DECIMAL(5,2) DEFAULT 0, -- Expected waste in percentage
    sequence_number INT DEFAULT 1, -- Order in which ingredients are used
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (recipe_id) REFERENCES recipes(id) ON DELETE CASCADE,
    FOREIGN KEY (ingredient_product_id) REFERENCES products(id),
    UNIQUE KEY unique_recipe_ingredient (recipe_id, ingredient_product_id)
);

-- 4. Production Orders Table
CREATE TABLE production_orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    production_plan_id INT,
    product_id INT NOT NULL,
    order_number VARCHAR(50) UNIQUE,
    order_quantity DECIMAL(10,2) NOT NULL,
    produced_quantity DECIMAL(10,2) DEFAULT 0,
    remaining_quantity DECIMAL(10,2) AS (order_quantity - produced_quantity) STORED,
    status ENUM('pending', 'in_progress', 'completed', 'cancelled', 'on_hold') DEFAULT 'pending',
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    start_date DATE,
    end_date DATE,
    actual_start_date TIMESTAMP NULL,
    actual_end_date TIMESTAMP NULL,
    expected_cost DECIMAL(15,2) DEFAULT 0,
    actual_cost DECIMAL(15,2) DEFAULT 0,
    created_by INT,
    started_by INT,
    completed_by INT,
    cancelled_by INT,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (production_plan_id) REFERENCES production_plans(id),
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (started_by) REFERENCES users(id),
    FOREIGN KEY (completed_by) REFERENCES users(id),
    FOREIGN KEY (cancelled_by) REFERENCES users(id),
    CHECK (end_date >= start_date)
);

-- 5. Production Order Ingredients Table
CREATE TABLE production_order_ingredients (
    id INT PRIMARY KEY AUTO_INCREMENT,
    production_order_id INT NOT NULL,
    recipe_ingredient_id INT NOT NULL,
    planned_quantity DECIMAL(10,2) NOT NULL,
    actual_quantity_used DECIMAL(10,2) DEFAULT 0,
    variance DECIMAL(10,2) AS (actual_quantity_used - planned_quantity) STORED,
    consumed_date TIMESTAMP NULL,
    consumed_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (production_order_id) REFERENCES production_orders(id) ON DELETE CASCADE,
    FOREIGN KEY (recipe_ingredient_id) REFERENCES recipe_ingredients(id),
    FOREIGN KEY (consumed_by) REFERENCES users(id)
);

-- 6. Production Logs Table
CREATE TABLE production_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    production_order_id INT NOT NULL,
    log_type ENUM('start', 'pause', 'resume', 'stop', 'quality_check', 'material_issue', 'machine_issue', 'other') NOT NULL,
    description TEXT,
    duration_minutes INT DEFAULT 0, -- For tracking downtime
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (production_order_id) REFERENCES production_orders(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- 7. Quality Control Results Table
CREATE TABLE quality_control_results (
    id INT PRIMARY KEY AUTO_INCREMENT,
    production_order_id INT NOT NULL,
    batch_number VARCHAR(100),
    test_type VARCHAR(50) NOT NULL, -- e.g., 'weight', 'appearance', 'taste', 'temperature'
    specification VARCHAR(100), -- e.g., '500g ± 5g', 'no discoloration'
    result ENUM('pass', 'fail', 'conditional_pass') NOT NULL,
    measured_value VARCHAR(100), -- e.g., '498g', 'acceptable'
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (production_order_id) REFERENCES production_orders(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- 8. Production Waste Table
CREATE TABLE production_waste (
    id INT PRIMARY KEY AUTO_INCREMENT,
    production_order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity DECIMAL(10,2) NOT NULL,
    waste_type ENUM('material_defect', 'process_error', 'quality_issue', 'handling_damage') NOT NULL,
    reason TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (production_order_id) REFERENCES production_orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- Create indexes for better performance
CREATE INDEX idx_production_plans_status ON production_plans(status);
CREATE INDEX idx_production_plans_dates ON production_plans(start_date, end_date);
CREATE INDEX idx_production_orders_status ON production_orders(status);
CREATE INDEX idx_production_orders_dates ON production_orders(start_date, end_date);
CREATE INDEX idx_production_logs_order ON production_logs(production_order_id);
CREATE INDEX idx_qc_results_order ON quality_control_results(production_order_id);

-- Generate order numbers
INSERT INTO production_orders (order_number, production_plan_id, product_id, order_quantity, created_by) 
SELECT 
    CONCAT('PROD', DATE_FORMAT(NOW(), '%Y%m'), LPAD(id, 4, '0')) as order_number,
    production_plan_id, product_id, order_quantity, created_by
FROM production_plans
WHERE status = 'planned';

-- Insert sample recipe data
INSERT INTO recipes (product_id, name, description, yield_quantity, created_by) VALUES
(1, 'Basic Bread Recipe', 'Standard bread recipe for 1kg flour', 10, 1); -- Assuming product_id 1 is bread

INSERT INTO recipe_ingredients (recipe_id, ingredient_product_id, quantity_needed, waste_percentage) VALUES
(1, 2, 1.00, 2.0), -- 1kg flour per 10 breads
(1, 3, 0.10, 1.0), -- 0.1kg sugar per 10 breads
(1, 4, 0.01, 0.5); -- 0.01kg salt per 10 breads