CREATE TABLE cost_components (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(30) NOT NULL,
    name VARCHAR(120) NOT NULL,
    component_type ENUM('material', 'labor', 'machine', 'utility', 'overhead', 'commercial', 'other') NOT NULL,
    calculation_method ENUM('quantity_rate', 'percentage', 'fixed', 'per_unit') NOT NULL DEFAULT 'quantity_rate',
    default_rate DECIMAL(18,6) NOT NULL DEFAULT 0,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    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_company_cost_component (company_id, code),
    CONSTRAINT fk_cost_component_company FOREIGN KEY (company_id) REFERENCES companies (id)
) ENGINE=InnoDB;

CREATE TABLE cost_headers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    cost_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,
    currency_id BIGINT UNSIGNED NOT NULL,
    order_quantity DECIMAL(18,4) NOT NULL DEFAULT 1,
    exchange_rate DECIMAL(18,6) NOT NULL DEFAULT 1,
    revision_number INT UNSIGNED NOT NULL DEFAULT 1,
    effective_date DATE NOT NULL,
    status ENUM('draft', 'submitted', 'approved', 'rejected', 'obsolete') NOT NULL DEFAULT 'draft',
    material_total DECIMAL(18,4) NOT NULL DEFAULT 0,
    conversion_total DECIMAL(18,4) NOT NULL DEFAULT 0,
    commercial_total DECIMAL(18,4) NOT NULL DEFAULT 0,
    overhead_total DECIMAL(18,4) NOT NULL DEFAULT 0,
    total_cost DECIMAL(18,4) NOT NULL DEFAULT 0,
    cost_per_unit DECIMAL(18,6) NOT NULL DEFAULT 0,
    target_price 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_cost_number (company_id, cost_number),
    KEY idx_cost_header_status (company_id, status),
    CONSTRAINT fk_cost_header_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_cost_header_style FOREIGN KEY (style_id) REFERENCES styles (id),
    CONSTRAINT fk_cost_header_product FOREIGN KEY (product_id) REFERENCES products (id),
    CONSTRAINT fk_cost_header_buyer FOREIGN KEY (buyer_id) REFERENCES buyers (id),
    CONSTRAINT fk_cost_header_currency FOREIGN KEY (currency_id) REFERENCES currencies (id),
    CONSTRAINT fk_cost_header_creator FOREIGN KEY (created_by) REFERENCES users (id),
    CONSTRAINT fk_cost_header_approver FOREIGN KEY (approved_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE cost_details (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cost_header_id BIGINT UNSIGNED NOT NULL,
    cost_component_id BIGINT UNSIGNED NULL,
    description VARCHAR(255) NOT NULL,
    quantity DECIMAL(18,6) NOT NULL DEFAULT 0,
    consumption DECIMAL(18,6) NOT NULL DEFAULT 1,
    uom_id BIGINT UNSIGNED NULL,
    unit_rate DECIMAL(18,6) NOT NULL DEFAULT 0,
    wastage_percent DECIMAL(9,4) NOT NULL DEFAULT 0,
    amount 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_cost_detail_header (cost_header_id, sort_order),
    CONSTRAINT fk_cost_detail_header FOREIGN KEY (cost_header_id) REFERENCES cost_headers (id),
    CONSTRAINT fk_cost_detail_component FOREIGN KEY (cost_component_id) REFERENCES cost_components (id),
    CONSTRAINT fk_cost_detail_uom FOREIGN KEY (uom_id) REFERENCES uoms (id)
) ENGINE=InnoDB;

CREATE TABLE material_costs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cost_header_id BIGINT UNSIGNED NOT NULL,
    cost_detail_id BIGINT UNSIGNED NULL,
    supplier_id BIGINT UNSIGNED NULL,
    material_code VARCHAR(80) NULL,
    material_name VARCHAR(180) NOT 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,
    amount DECIMAL(18,4) 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,
    CONSTRAINT fk_material_cost_header FOREIGN KEY (cost_header_id) REFERENCES cost_headers (id),
    CONSTRAINT fk_material_cost_detail FOREIGN KEY (cost_detail_id) REFERENCES cost_details (id),
    CONSTRAINT fk_material_cost_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers (id),
    CONSTRAINT fk_material_cost_uom FOREIGN KEY (uom_id) REFERENCES uoms (id)
) ENGINE=InnoDB;

CREATE TABLE labor_costs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cost_header_id BIGINT UNSIGNED NOT NULL,
    cost_detail_id BIGINT UNSIGNED NULL,
    operation_id BIGINT UNSIGNED NULL,
    description VARCHAR(180) NOT NULL,
    smv DECIMAL(12,4) NOT NULL DEFAULT 0,
    operator_count DECIMAL(12,4) NOT NULL DEFAULT 1,
    rate_per_minute DECIMAL(18,6) NOT NULL DEFAULT 0,
    amount DECIMAL(18,4) 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,
    CONSTRAINT fk_labor_cost_header FOREIGN KEY (cost_header_id) REFERENCES cost_headers (id),
    CONSTRAINT fk_labor_cost_detail FOREIGN KEY (cost_detail_id) REFERENCES cost_details (id),
    CONSTRAINT fk_labor_cost_operation FOREIGN KEY (operation_id) REFERENCES operations (id)
) ENGINE=InnoDB;

CREATE TABLE machine_costs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cost_header_id BIGINT UNSIGNED NOT NULL,
    cost_detail_id BIGINT UNSIGNED NULL,
    machine_id BIGINT UNSIGNED NULL,
    machine_type_id BIGINT UNSIGNED NULL,
    description VARCHAR(180) NOT NULL,
    minutes DECIMAL(12,4) NOT NULL DEFAULT 0,
    rate_per_minute DECIMAL(18,6) NOT NULL DEFAULT 0,
    amount DECIMAL(18,4) 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,
    CONSTRAINT fk_machine_cost_header FOREIGN KEY (cost_header_id) REFERENCES cost_headers (id),
    CONSTRAINT fk_machine_cost_detail FOREIGN KEY (cost_detail_id) REFERENCES cost_details (id),
    CONSTRAINT fk_machine_cost_machine FOREIGN KEY (machine_id) REFERENCES machines (id),
    CONSTRAINT fk_machine_cost_type FOREIGN KEY (machine_type_id) REFERENCES machine_types (id)
) ENGINE=InnoDB;

CREATE TABLE utility_costs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cost_header_id BIGINT UNSIGNED NOT NULL,
    cost_detail_id BIGINT UNSIGNED NULL,
    utility_type VARCHAR(80) NOT NULL,
    consumption DECIMAL(18,6) NOT NULL DEFAULT 0,
    unit_name VARCHAR(30) NULL,
    unit_rate DECIMAL(18,6) NOT NULL DEFAULT 0,
    amount DECIMAL(18,4) 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,
    CONSTRAINT fk_utility_cost_header FOREIGN KEY (cost_header_id) REFERENCES cost_headers (id),
    CONSTRAINT fk_utility_cost_detail FOREIGN KEY (cost_detail_id) REFERENCES cost_details (id)
) ENGINE=InnoDB;

CREATE TABLE overhead_costs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cost_header_id BIGINT UNSIGNED NOT NULL,
    cost_detail_id BIGINT UNSIGNED NULL,
    overhead_type VARCHAR(100) NOT NULL,
    calculation_method ENUM('percentage', 'fixed', 'per_unit') NOT NULL DEFAULT 'percentage',
    basis_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    rate DECIMAL(18,6) NOT NULL DEFAULT 0,
    amount DECIMAL(18,4) 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,
    CONSTRAINT fk_overhead_cost_header FOREIGN KEY (cost_header_id) REFERENCES cost_headers (id),
    CONSTRAINT fk_overhead_cost_detail FOREIGN KEY (cost_detail_id) REFERENCES cost_details (id)
) ENGINE=InnoDB;

CREATE TABLE commercial_costs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cost_header_id BIGINT UNSIGNED NOT NULL,
    cost_detail_id BIGINT UNSIGNED NULL,
    charge_type VARCHAR(100) NOT NULL,
    calculation_method ENUM('percentage', 'fixed', 'per_unit') NOT NULL DEFAULT 'percentage',
    basis_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    rate DECIMAL(18,6) NOT NULL DEFAULT 0,
    amount DECIMAL(18,4) 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,
    CONSTRAINT fk_commercial_cost_header FOREIGN KEY (cost_header_id) REFERENCES cost_headers (id),
    CONSTRAINT fk_commercial_cost_detail FOREIGN KEY (cost_detail_id) REFERENCES cost_details (id)
) ENGINE=InnoDB;

CREATE TABLE profit_analysis (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cost_header_id BIGINT UNSIGNED NOT NULL UNIQUE,
    total_cost DECIMAL(18,4) NOT NULL DEFAULT 0,
    cost_per_unit DECIMAL(18,6) NOT NULL DEFAULT 0,
    selling_price DECIMAL(18,4) NOT NULL DEFAULT 0,
    profit_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    profit_margin_percent DECIMAL(9,4) NOT NULL DEFAULT 0,
    markup_percent DECIMAL(9,4) NOT NULL DEFAULT 0,
    break_even_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_profit_analysis_header FOREIGN KEY (cost_header_id) REFERENCES cost_headers (id)
) ENGINE=InnoDB;

CREATE TABLE cost_revisions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cost_header_id BIGINT UNSIGNED NOT NULL,
    revision_number INT UNSIGNED NOT NULL,
    reason VARCHAR(500) NOT NULL,
    snapshot JSON NOT NULL,
    revised_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_cost_revision (cost_header_id, revision_number),
    CONSTRAINT fk_cost_revision_header FOREIGN KEY (cost_header_id) REFERENCES cost_headers (id),
    CONSTRAINT fk_cost_revision_user FOREIGN KEY (revised_by) REFERENCES users (id)
) ENGINE=InnoDB;

INSERT INTO cost_components (company_id, code, name, component_type, calculation_method, sort_order)
SELECT id, 'MAT', 'Material Cost', 'material', 'quantity_rate', 10 FROM companies WHERE code = 'GPCM';
INSERT INTO cost_components (company_id, code, name, component_type, calculation_method, sort_order)
SELECT id, 'LAB', 'Labor Cost', 'labor', 'quantity_rate', 20 FROM companies WHERE code = 'GPCM';
INSERT INTO cost_components (company_id, code, name, component_type, calculation_method, sort_order)
SELECT id, 'MAC', 'Machine Cost', 'machine', 'quantity_rate', 30 FROM companies WHERE code = 'GPCM';
INSERT INTO cost_components (company_id, code, name, component_type, calculation_method, sort_order)
SELECT id, 'UTL', 'Utility Cost', 'utility', 'quantity_rate', 40 FROM companies WHERE code = 'GPCM';
INSERT INTO cost_components (company_id, code, name, component_type, calculation_method, sort_order)
SELECT id, 'OVH', 'Overhead Cost', 'overhead', 'percentage', 50 FROM companies WHERE code = 'GPCM';
INSERT INTO cost_components (company_id, code, name, component_type, calculation_method, sort_order)
SELECT id, 'COM', 'Commercial Cost', 'commercial', 'percentage', 60 FROM companies WHERE code = 'GPCM';