CREATE TABLE bom_headers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    bom_number VARCHAR(50) NOT NULL,
    title VARCHAR(180) NOT NULL,
    style_id BIGINT UNSIGNED NULL,
    product_id BIGINT UNSIGNED NULL,
    buyer_id BIGINT UNSIGNED NULL,
    base_quantity DECIMAL(18,4) NOT NULL DEFAULT 1,
    uom_id BIGINT UNSIGNED NULL,
    current_version_number INT UNSIGNED NOT NULL DEFAULT 1,
    status ENUM('draft', 'active', 'inactive', 'obsolete') NOT NULL DEFAULT 'draft',
    notes TEXT NULL,
    created_by BIGINT UNSIGNED NOT 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_bom_number (company_id, bom_number),
    KEY idx_bom_header_status (company_id, status),
    CONSTRAINT fk_bom_header_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_bom_header_style FOREIGN KEY (style_id) REFERENCES styles (id),
    CONSTRAINT fk_bom_header_product FOREIGN KEY (product_id) REFERENCES products (id),
    CONSTRAINT fk_bom_header_buyer FOREIGN KEY (buyer_id) REFERENCES buyers (id),
    CONSTRAINT fk_bom_header_uom FOREIGN KEY (uom_id) REFERENCES uoms (id),
    CONSTRAINT fk_bom_header_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE bom_versions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    bom_header_id BIGINT UNSIGNED NOT NULL,
    version_number INT UNSIGNED NOT NULL,
    effective_date DATE NOT NULL,
    change_reason VARCHAR(500) NULL,
    status ENUM('draft', 'submitted', 'approved', 'rejected', 'obsolete') NOT NULL DEFAULT 'draft',
    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_bom_version (bom_header_id, version_number),
    CONSTRAINT fk_bom_version_header FOREIGN KEY (bom_header_id) REFERENCES bom_headers (id),
    CONSTRAINT fk_bom_version_creator FOREIGN KEY (created_by) REFERENCES users (id),
    CONSTRAINT fk_bom_version_approver FOREIGN KEY (approved_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE bom_details (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    bom_version_id BIGINT UNSIGNED NOT NULL,
    parent_detail_id BIGINT UNSIGNED NULL,
    line_type ENUM('material', 'operation', 'sub_bom', 'note') NOT NULL DEFAULT 'material',
    description VARCHAR(255) NOT NULL,
    quantity DECIMAL(18,6) NOT NULL DEFAULT 0,
    uom_id BIGINT UNSIGNED NULL,
    wastage_percent DECIMAL(9,4) NOT NULL DEFAULT 0,
    unit_cost DECIMAL(18,6) NOT NULL DEFAULT 0,
    total_cost DECIMAL(18,4) NOT NULL DEFAULT 0,
    remarks VARCHAR(500) NULL,
    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_bom_detail_version (bom_version_id, sort_order),
    CONSTRAINT fk_bom_detail_version FOREIGN KEY (bom_version_id) REFERENCES bom_versions (id),
    CONSTRAINT fk_bom_detail_parent FOREIGN KEY (parent_detail_id) REFERENCES bom_details (id),
    CONSTRAINT fk_bom_detail_uom FOREIGN KEY (uom_id) REFERENCES uoms (id)
) ENGINE=InnoDB;

CREATE TABLE bom_materials (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    bom_version_id BIGINT UNSIGNED NOT NULL,
    bom_detail_id BIGINT UNSIGNED NULL,
    supplier_id BIGINT UNSIGNED NULL,
    material_code VARCHAR(80) NULL,
    material_name VARCHAR(180) NOT NULL,
    material_type VARCHAR(80) NULL,
    uom_id BIGINT UNSIGNED NULL,
    consumption DECIMAL(18,6) NOT NULL DEFAULT 0,
    wastage_percent DECIMAL(9,4) NOT NULL DEFAULT 0,
    unit_price DECIMAL(18,6) NOT NULL DEFAULT 0,
    total_cost DECIMAL(18,4) NOT NULL DEFAULT 0,
    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_bom_material_version (bom_version_id, sort_order),
    CONSTRAINT fk_bom_material_version FOREIGN KEY (bom_version_id) REFERENCES bom_versions (id),
    CONSTRAINT fk_bom_material_detail FOREIGN KEY (bom_detail_id) REFERENCES bom_details (id),
    CONSTRAINT fk_bom_material_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers (id),
    CONSTRAINT fk_bom_material_uom FOREIGN KEY (uom_id) REFERENCES uoms (id)
) ENGINE=InnoDB;

CREATE TABLE bom_operations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    bom_version_id BIGINT UNSIGNED NOT NULL,
    bom_detail_id BIGINT UNSIGNED NULL,
    operation_id BIGINT UNSIGNED NULL,
    sequence_number INT UNSIGNED NOT NULL DEFAULT 0,
    description VARCHAR(180) NOT NULL,
    smv DECIMAL(12,4) NOT NULL DEFAULT 0,
    rate_per_minute DECIMAL(18,6) NOT NULL DEFAULT 0,
    total_cost DECIMAL(18,4) NOT NULL DEFAULT 0,
    machine_type_id BIGINT UNSIGNED NULL,
    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_bom_operation_version (bom_version_id, sequence_number),
    CONSTRAINT fk_bom_operation_version FOREIGN KEY (bom_version_id) REFERENCES bom_versions (id),
    CONSTRAINT fk_bom_operation_detail FOREIGN KEY (bom_detail_id) REFERENCES bom_details (id),
    CONSTRAINT fk_bom_operation_operation FOREIGN KEY (operation_id) REFERENCES operations (id),
    CONSTRAINT fk_bom_operation_machine_type FOREIGN KEY (machine_type_id) REFERENCES machine_types (id)
) ENGINE=InnoDB;

CREATE TABLE bom_colors (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    bom_version_id BIGINT UNSIGNED NOT NULL,
    color_id BIGINT UNSIGNED NOT NULL,
    color_code_override VARCHAR(50) NULL,
    extra_percent DECIMAL(9,4) NOT NULL DEFAULT 0,
    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,
    UNIQUE KEY uq_bom_version_color (bom_version_id, color_id),
    CONSTRAINT fk_bom_color_version FOREIGN KEY (bom_version_id) REFERENCES bom_versions (id),
    CONSTRAINT fk_bom_color_color FOREIGN KEY (color_id) REFERENCES colors (id)
) ENGINE=InnoDB;

CREATE TABLE bom_sizes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    bom_version_id BIGINT UNSIGNED NOT NULL,
    size_id BIGINT UNSIGNED NOT NULL,
    ratio DECIMAL(12,4) NOT NULL DEFAULT 1,
    extra_percent DECIMAL(9,4) NOT NULL DEFAULT 0,
    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,
    UNIQUE KEY uq_bom_version_size (bom_version_id, size_id),
    CONSTRAINT fk_bom_size_version FOREIGN KEY (bom_version_id) REFERENCES bom_versions (id),
    CONSTRAINT fk_bom_size_size FOREIGN KEY (size_id) REFERENCES sizes (id)
) ENGINE=InnoDB;

CREATE TABLE bom_consumptions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    bom_version_id BIGINT UNSIGNED NOT NULL,
    bom_material_id BIGINT UNSIGNED NOT NULL,
    color_id BIGINT UNSIGNED NULL,
    size_id BIGINT UNSIGNED NULL,
    net_consumption DECIMAL(18,6) NOT NULL DEFAULT 0,
    wastage_percent DECIMAL(9,4) NOT NULL DEFAULT 0,
    gross_consumption DECIMAL(18,6) NOT NULL DEFAULT 0,
    notes VARCHAR(500) 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_bom_consumption_version (bom_version_id),
    CONSTRAINT fk_bom_consumption_version FOREIGN KEY (bom_version_id) REFERENCES bom_versions (id),
    CONSTRAINT fk_bom_consumption_material FOREIGN KEY (bom_material_id) REFERENCES bom_materials (id),
    CONSTRAINT fk_bom_consumption_color FOREIGN KEY (color_id) REFERENCES colors (id),
    CONSTRAINT fk_bom_consumption_size FOREIGN KEY (size_id) REFERENCES sizes (id)
) ENGINE=InnoDB;

CREATE TABLE bom_approvals (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    bom_version_id BIGINT UNSIGNED NOT NULL,
    approval_level VARCHAR(80) NOT NULL,
    status ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
    approver_id BIGINT UNSIGNED NOT NULL,
    comments VARCHAR(500) NULL,
    action_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_bom_approval_version (bom_version_id, status),
    CONSTRAINT fk_bom_approval_version FOREIGN KEY (bom_version_id) REFERENCES bom_versions (id),
    CONSTRAINT fk_bom_approval_user FOREIGN KEY (approver_id) REFERENCES users (id)
) ENGINE=InnoDB;