CREATE TABLE costing_op_headers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    op_number VARCHAR(60) NOT NULL UNIQUE,
    buyer_id BIGINT UNSIGNED NULL,
    brand_id BIGINT UNSIGNED NULL,
    season_id BIGINT UNSIGNED NULL,
    style_id BIGINT UNSIGNED NULL,
    style_name VARCHAR(180) NULL,
    style_number VARCHAR(100) NULL,
    fabric_description VARCHAR(500) NOT NULL,
    wash_description VARCHAR(500) NULL,
    size_range VARCHAR(100) NULL,
    base_quantity DECIMAL(18,4) NOT NULL DEFAULT 1,
    productivity_per_hour DECIMAL(18,4) NOT NULL DEFAULT 0,
    material_lcm_percent DECIMAL(9,4) NOT NULL DEFAULT 6,
    wash_lcm_percent DECIMAL(9,4) NOT NULL DEFAULT 1,
    pre_cost_per_dozen DECIMAL(18,4) NOT NULL DEFAULT 0,
    net_profit_per_dozen DECIMAL(18,4) NOT NULL DEFAULT 0,
    worker_wage_per_piece DECIMAL(18,4) NOT NULL DEFAULT 0,
    commission_percent DECIMAL(9,4) NOT NULL DEFAULT 0,
    commission_method ENUM('inverse', 'additive') NOT NULL DEFAULT 'inverse',
    currency_id BIGINT UNSIGNED NOT NULL,
    status ENUM('draft', 'confirmed', 'approved', 'obsolete') NOT NULL DEFAULT 'draft',
    notes VARCHAR(500) 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,
    CONSTRAINT fk_costing_op_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_costing_op_buyer FOREIGN KEY (buyer_id) REFERENCES buyers (id),
    CONSTRAINT fk_costing_op_brand FOREIGN KEY (brand_id) REFERENCES brands (id),
    CONSTRAINT fk_costing_op_season FOREIGN KEY (season_id) REFERENCES seasons (id),
    CONSTRAINT fk_costing_op_style FOREIGN KEY (style_id) REFERENCES styles (id),
    CONSTRAINT fk_costing_op_currency FOREIGN KEY (currency_id) REFERENCES currencies (id),
    CONSTRAINT fk_costing_op_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE costing_op_options (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    costing_op_id BIGINT UNSIGNED NOT NULL,
    option_name VARCHAR(80) NOT NULL,
    quantity DECIMAL(18,4) NOT NULL,
    is_confirmed BOOLEAN NOT NULL DEFAULT FALSE,
    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_costing_op_option (costing_op_id, option_name),
    CONSTRAINT fk_costing_op_option_header FOREIGN KEY (costing_op_id) REFERENCES costing_op_headers (id)
) ENGINE=InnoDB;

CREATE TABLE costing_op_lines (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    costing_op_id BIGINT UNSIGNED NOT NULL,
    item_group ENUM('fabric', 'trim', 'service') NOT NULL,
    item_name VARCHAR(150) NOT NULL,
    reference_description VARCHAR(500) NULL,
    consumption_for ENUM('piece', 'dozen', 'hundred', 'gross', 'thousand') NOT NULL DEFAULT 'piece',
    consumption_unit VARCHAR(30) NULL,
    costing_price DECIMAL(18,6) NOT NULL DEFAULT 0,
    costing_consumption DECIMAL(18,6) NOT NULL DEFAULT 1,
    confirmed_price DECIMAL(18,6) NOT NULL DEFAULT 0,
    confirmed_consumption DECIMAL(18,6) NOT NULL DEFAULT 1,
    actual_price DECIMAL(18,6) NOT NULL DEFAULT 0,
    actual_consumption DECIMAL(18,6) NOT NULL DEFAULT 1,
    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,
    CONSTRAINT fk_costing_op_line_header FOREIGN KEY (costing_op_id) REFERENCES costing_op_headers (id)
) ENGINE=InnoDB;

CREATE TABLE costing_op_line_options (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    costing_op_line_id BIGINT UNSIGNED NOT NULL,
    costing_op_option_id BIGINT UNSIGNED NOT NULL,
    wastage_percent DECIMAL(9,4) NOT NULL DEFAULT 0,
    costing_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    confirmed_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    actual_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,
    UNIQUE KEY uq_costing_op_line_option (costing_op_line_id, costing_op_option_id),
    CONSTRAINT fk_costing_op_line_option_line FOREIGN KEY (costing_op_line_id) REFERENCES costing_op_lines (id),
    CONSTRAINT fk_costing_op_line_option_option FOREIGN KEY (costing_op_option_id) REFERENCES costing_op_options (id)
) ENGINE=InnoDB;

CREATE TABLE costing_op_wash_details (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    costing_op_id BIGINT UNSIGNED NOT NULL,
    costing_op_option_id BIGINT UNSIGNED NOT NULL,
    description VARCHAR(250) NOT NULL,
    productivity DECIMAL(18,4) NOT NULL DEFAULT 0,
    costing_cost DECIMAL(18,4) NOT NULL DEFAULT 0,
    costing_profit DECIMAL(18,4) NOT NULL DEFAULT 0,
    confirmed_cost DECIMAL(18,4) NOT NULL DEFAULT 0,
    confirmed_profit DECIMAL(18,4) NOT NULL DEFAULT 0,
    actual_cost DECIMAL(18,4) NOT NULL DEFAULT 0,
    actual_profit 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,
    CONSTRAINT fk_costing_op_wash_header FOREIGN KEY (costing_op_id) REFERENCES costing_op_headers (id),
    CONSTRAINT fk_costing_op_wash_option FOREIGN KEY (costing_op_option_id) REFERENCES costing_op_options (id)
) ENGINE=InnoDB;