CREATE TABLE mrp_headers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    mrp_number VARCHAR(50) NOT NULL,
    title VARCHAR(180) NOT NULL,
    bom_header_id BIGINT UNSIGNED NOT NULL,
    bom_version_id BIGINT UNSIGNED NOT NULL,
    style_id BIGINT UNSIGNED NULL,
    product_id BIGINT UNSIGNED NULL,
    warehouse_id BIGINT UNSIGNED NULL,
    demand_quantity DECIMAL(18,4) NOT NULL DEFAULT 1,
    demand_date DATE NOT NULL,
    planning_date DATE NOT NULL,
    status ENUM('draft', 'calculated', 'approved', 'closed', 'cancelled') NOT NULL DEFAULT 'draft',
    total_materials INT UNSIGNED NOT NULL DEFAULT 0,
    total_required_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    total_shortage_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    notes TEXT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    approved_by BIGINT UNSIGNED NULL,
    approved_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_company_mrp_number (company_id, mrp_number),
    KEY idx_mrp_header_status (company_id, status),
    CONSTRAINT fk_mrp_header_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_mrp_header_bom FOREIGN KEY (bom_header_id) REFERENCES bom_headers (id),
    CONSTRAINT fk_mrp_header_bom_version FOREIGN KEY (bom_version_id) REFERENCES bom_versions (id),
    CONSTRAINT fk_mrp_header_style FOREIGN KEY (style_id) REFERENCES styles (id),
    CONSTRAINT fk_mrp_header_product FOREIGN KEY (product_id) REFERENCES products (id),
    CONSTRAINT fk_mrp_header_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_mrp_header_creator FOREIGN KEY (created_by) REFERENCES users (id),
    CONSTRAINT fk_mrp_header_approver FOREIGN KEY (approved_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE mrp_details (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mrp_header_id BIGINT UNSIGNED NOT NULL,
    bom_material_id BIGINT UNSIGNED NULL,
    material_code VARCHAR(80) NULL,
    material_name VARCHAR(180) NOT NULL,
    uom_id BIGINT UNSIGNED NULL,
    required_quantity DECIMAL(18,6) NOT NULL DEFAULT 0,
    available_quantity DECIMAL(18,6) NOT NULL DEFAULT 0,
    reserved_quantity DECIMAL(18,6) NOT NULL DEFAULT 0,
    shortage_quantity DECIMAL(18,6) NOT NULL DEFAULT 0,
    required_date DATE NOT NULL,
    preferred_supplier_id BIGINT UNSIGNED NULL,
    status ENUM('required', 'partially_reserved', 'reserved', 'ordered', 'closed') NOT NULL DEFAULT 'required',
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    KEY idx_mrp_detail_header (mrp_header_id, sort_order),
    CONSTRAINT fk_mrp_detail_header FOREIGN KEY (mrp_header_id) REFERENCES mrp_headers (id),
    CONSTRAINT fk_mrp_detail_bom_material FOREIGN KEY (bom_material_id) REFERENCES bom_materials (id),
    CONSTRAINT fk_mrp_detail_uom FOREIGN KEY (uom_id) REFERENCES uoms (id),
    CONSTRAINT fk_mrp_detail_supplier FOREIGN KEY (preferred_supplier_id) REFERENCES suppliers (id)
) ENGINE=InnoDB;

CREATE TABLE material_requirements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mrp_header_id BIGINT UNSIGNED NOT NULL,
    mrp_detail_id BIGINT UNSIGNED NOT NULL,
    requirement_type ENUM('gross', 'net', 'safety', 'replacement') NOT NULL DEFAULT 'net',
    gross_requirement DECIMAL(18,6) NOT NULL DEFAULT 0,
    on_hand_quantity DECIMAL(18,6) NOT NULL DEFAULT 0,
    scheduled_receipt_quantity DECIMAL(18,6) NOT NULL DEFAULT 0,
    reserved_quantity DECIMAL(18,6) NOT NULL DEFAULT 0,
    net_requirement DECIMAL(18,6) NOT NULL DEFAULT 0,
    planned_order_quantity DECIMAL(18,6) NOT NULL DEFAULT 0,
    required_date DATE NOT NULL,
    planned_order_date DATE NULL,
    lead_time_days INT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_material_requirement_detail (mrp_detail_id),
    CONSTRAINT fk_material_requirement_header FOREIGN KEY (mrp_header_id) REFERENCES mrp_headers (id),
    CONSTRAINT fk_material_requirement_detail FOREIGN KEY (mrp_detail_id) REFERENCES mrp_details (id)
) ENGINE=InnoDB;

CREATE TABLE shortage_reports (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mrp_header_id BIGINT UNSIGNED NOT NULL,
    mrp_detail_id BIGINT UNSIGNED NOT NULL,
    material_requirement_id BIGINT UNSIGNED NOT NULL,
    shortage_quantity DECIMAL(18,6) NOT NULL DEFAULT 0,
    shortage_value DECIMAL(18,4) NOT NULL DEFAULT 0,
    severity ENUM('low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'medium',
    status ENUM('open', 'actioned', 'resolved', 'waived') NOT NULL DEFAULT 'open',
    recommended_action VARCHAR(500) NULL,
    resolved_by BIGINT UNSIGNED NULL,
    resolved_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_shortage_report_detail (mrp_detail_id),
    CONSTRAINT fk_shortage_report_header FOREIGN KEY (mrp_header_id) REFERENCES mrp_headers (id),
    CONSTRAINT fk_shortage_report_detail FOREIGN KEY (mrp_detail_id) REFERENCES mrp_details (id),
    CONSTRAINT fk_shortage_report_requirement FOREIGN KEY (material_requirement_id) REFERENCES material_requirements (id),
    CONSTRAINT fk_shortage_report_resolver FOREIGN KEY (resolved_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE stock_reservations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mrp_header_id BIGINT UNSIGNED NOT NULL,
    mrp_detail_id BIGINT UNSIGNED NOT NULL,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    warehouse_location_id BIGINT UNSIGNED NULL,
    reservation_number VARCHAR(60) NOT NULL UNIQUE,
    reserved_quantity DECIMAL(18,6) NOT NULL,
    status ENUM('active', 'released', 'consumed', 'cancelled') NOT NULL DEFAULT 'active',
    reference VARCHAR(120) NULL,
    reserved_by BIGINT UNSIGNED NOT NULL,
    reserved_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    released_by BIGINT UNSIGNED NULL,
    released_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    KEY idx_stock_reservation_detail (mrp_detail_id, status),
    CONSTRAINT fk_stock_reservation_header FOREIGN KEY (mrp_header_id) REFERENCES mrp_headers (id),
    CONSTRAINT fk_stock_reservation_detail FOREIGN KEY (mrp_detail_id) REFERENCES mrp_details (id),
    CONSTRAINT fk_stock_reservation_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_stock_reservation_location FOREIGN KEY (warehouse_location_id) REFERENCES warehouse_locations (id),
    CONSTRAINT fk_stock_reservation_user FOREIGN KEY (reserved_by) REFERENCES users (id),
    CONSTRAINT fk_stock_reservation_releaser FOREIGN KEY (released_by) REFERENCES users (id)
) ENGINE=InnoDB;