CREATE TABLE production_lines (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    factory_id BIGINT UNSIGNED NULL,
    code VARCHAR(30) NOT NULL,
    name VARCHAR(120) NOT NULL,
    line_type VARCHAR(50) NULL,
    daily_capacity DECIMAL(18,4) NOT NULL DEFAULT 0,
    status ENUM('active', 'inactive', 'maintenance') 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_production_line (company_id, code),
    CONSTRAINT fk_production_line_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_production_line_factory FOREIGN KEY (factory_id) REFERENCES factories (id)
) ENGINE=InnoDB;

CREATE TABLE production_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    production_order_number VARCHAR(60) NOT NULL,
    title VARCHAR(180) NOT NULL,
    style_id BIGINT UNSIGNED NULL,
    product_id BIGINT UNSIGNED NULL,
    buyer_id BIGINT UNSIGNED NULL,
    bom_version_id BIGINT UNSIGNED NULL,
    order_quantity DECIMAL(18,4) NOT NULL,
    planned_start_date DATE NOT NULL,
    planned_end_date DATE NOT NULL,
    actual_start_date DATE NULL,
    actual_end_date DATE NULL,
    input_warehouse_id BIGINT UNSIGNED NULL,
    output_warehouse_id BIGINT UNSIGNED NULL,
    produced_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    rejected_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    status ENUM('draft', 'released', 'in_progress', 'completed', 'closed', 'cancelled') NOT NULL DEFAULT 'draft',
    priority ENUM('low', 'normal', 'high', 'urgent') NOT NULL DEFAULT 'normal',
    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_production_order (company_id, production_order_number),
    CONSTRAINT fk_production_order_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_production_order_style FOREIGN KEY (style_id) REFERENCES styles (id),
    CONSTRAINT fk_production_order_product FOREIGN KEY (product_id) REFERENCES products (id),
    CONSTRAINT fk_production_order_buyer FOREIGN KEY (buyer_id) REFERENCES buyers (id),
    CONSTRAINT fk_production_order_bom_version FOREIGN KEY (bom_version_id) REFERENCES bom_versions (id),
    CONSTRAINT fk_production_order_input_warehouse FOREIGN KEY (input_warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_production_order_output_warehouse FOREIGN KEY (output_warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_production_order_creator FOREIGN KEY (created_by) REFERENCES users (id),
    CONSTRAINT fk_production_order_approver FOREIGN KEY (approved_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE cutting_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    production_order_id BIGINT UNSIGNED NOT NULL,
    cutting_order_number VARCHAR(60) NOT NULL UNIQUE,
    fabric_item_id BIGINT UNSIGNED NULL,
    warehouse_id BIGINT UNSIGNED NULL,
    planned_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    cut_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    rejected_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    planned_date DATE NOT NULL,
    completed_date DATE NULL,
    status ENUM('draft', 'released', 'in_progress', 'completed', 'cancelled') NOT NULL DEFAULT 'draft',
    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_cutting_order_production FOREIGN KEY (production_order_id) REFERENCES production_orders (id),
    CONSTRAINT fk_cutting_order_fabric FOREIGN KEY (fabric_item_id) REFERENCES items (id),
    CONSTRAINT fk_cutting_order_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_cutting_order_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE cutting_details (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    production_order_id BIGINT UNSIGNED NOT NULL,
    cutting_order_id BIGINT UNSIGNED NOT NULL,
    color_id BIGINT UNSIGNED NULL,
    size_id BIGINT UNSIGNED NULL,
    marker_number VARCHAR(80) NULL,
    lay_number VARCHAR(80) NULL,
    layer_count INT UNSIGNED NOT NULL DEFAULT 0,
    planned_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    cut_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    rejected_quantity 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_cutting_detail_production FOREIGN KEY (production_order_id) REFERENCES production_orders (id),
    CONSTRAINT fk_cutting_detail_order FOREIGN KEY (cutting_order_id) REFERENCES cutting_orders (id),
    CONSTRAINT fk_cutting_detail_color FOREIGN KEY (color_id) REFERENCES colors (id),
    CONSTRAINT fk_cutting_detail_size FOREIGN KEY (size_id) REFERENCES sizes (id)
) ENGINE=InnoDB;

CREATE TABLE bundle_cards (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    production_order_id BIGINT UNSIGNED NOT NULL,
    cutting_detail_id BIGINT UNSIGNED NOT NULL,
    bundle_number VARCHAR(80) NOT NULL UNIQUE,
    color_id BIGINT UNSIGNED NULL,
    size_id BIGINT UNSIGNED NULL,
    quantity DECIMAL(18,4) NOT NULL,
    barcode VARCHAR(150) NULL UNIQUE,
    qr_code VARCHAR(255) NULL UNIQUE,
    current_stage VARCHAR(80) NULL,
    production_line_id BIGINT UNSIGNED NULL,
    status ENUM('created', 'issued', 'in_process', 'completed', 'hold', 'rejected') NOT NULL DEFAULT 'created',
    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_bundle_card_production FOREIGN KEY (production_order_id) REFERENCES production_orders (id),
    CONSTRAINT fk_bundle_card_cutting_detail FOREIGN KEY (cutting_detail_id) REFERENCES cutting_details (id),
    CONSTRAINT fk_bundle_card_color FOREIGN KEY (color_id) REFERENCES colors (id),
    CONSTRAINT fk_bundle_card_size FOREIGN KEY (size_id) REFERENCES sizes (id),
    CONSTRAINT fk_bundle_card_line FOREIGN KEY (production_line_id) REFERENCES production_lines (id)
) ENGINE=InnoDB;

CREATE TABLE sewing_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    production_order_id BIGINT UNSIGNED NOT NULL,
    sewing_order_number VARCHAR(60) NOT NULL UNIQUE,
    production_line_id BIGINT UNSIGNED NOT NULL,
    planned_quantity DECIMAL(18,4) NOT NULL,
    input_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    output_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    rejected_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    planned_start_date DATE NOT NULL,
    planned_end_date DATE NOT NULL,
    status ENUM('draft', 'released', 'in_progress', 'completed', 'cancelled') NOT NULL DEFAULT 'draft',
    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_sewing_order_production FOREIGN KEY (production_order_id) REFERENCES production_orders (id),
    CONSTRAINT fk_sewing_order_line FOREIGN KEY (production_line_id) REFERENCES production_lines (id),
    CONSTRAINT fk_sewing_order_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE line_production (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    production_order_id BIGINT UNSIGNED NOT NULL,
    sewing_order_id BIGINT UNSIGNED NOT NULL,
    production_date DATE NOT NULL,
    shift_name VARCHAR(50) NULL,
    operation_id BIGINT UNSIGNED NULL,
    input_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    output_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    rejected_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    rework_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    operator_count INT UNSIGNED NOT NULL DEFAULT 0,
    working_minutes DECIMAL(12,4) NOT NULL DEFAULT 0,
    earned_minutes DECIMAL(12,4) NOT NULL DEFAULT 0,
    efficiency_percent DECIMAL(9,4) NOT NULL DEFAULT 0,
    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_line_production_order FOREIGN KEY (production_order_id) REFERENCES production_orders (id),
    CONSTRAINT fk_line_production_sewing FOREIGN KEY (sewing_order_id) REFERENCES sewing_orders (id),
    CONSTRAINT fk_line_production_operation FOREIGN KEY (operation_id) REFERENCES operations (id),
    CONSTRAINT fk_line_production_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE washing_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    production_order_id BIGINT UNSIGNED NOT NULL,
    process_number VARCHAR(60) NOT NULL UNIQUE,
    wash_type VARCHAR(100) NOT NULL,
    supplier_id BIGINT UNSIGNED NULL,
    planned_quantity DECIMAL(18,4) NOT NULL,
    input_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    output_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    rejected_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    process_date DATE NOT NULL,
    unit_cost DECIMAL(18,6) NOT NULL DEFAULT 0,
    status ENUM('draft', 'sent', 'in_process', 'received', 'completed', 'cancelled') NOT NULL DEFAULT 'draft',
    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_washing_order_production FOREIGN KEY (production_order_id) REFERENCES production_orders (id),
    CONSTRAINT fk_washing_order_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers (id),
    CONSTRAINT fk_washing_order_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE printing_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    production_order_id BIGINT UNSIGNED NOT NULL,
    process_number VARCHAR(60) NOT NULL UNIQUE,
    print_type VARCHAR(100) NOT NULL,
    supplier_id BIGINT UNSIGNED NULL,
    color_count INT UNSIGNED NOT NULL DEFAULT 0,
    planned_quantity DECIMAL(18,4) NOT NULL,
    output_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    rejected_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    process_date DATE NOT NULL,
    unit_cost DECIMAL(18,6) NOT NULL DEFAULT 0,
    status ENUM('draft', 'sent', 'in_process', 'received', 'completed', 'cancelled') NOT NULL DEFAULT 'draft',
    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_printing_order_production FOREIGN KEY (production_order_id) REFERENCES production_orders (id),
    CONSTRAINT fk_printing_order_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers (id),
    CONSTRAINT fk_printing_order_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE embroidery_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    production_order_id BIGINT UNSIGNED NOT NULL,
    process_number VARCHAR(60) NOT NULL UNIQUE,
    embroidery_type VARCHAR(100) NOT NULL,
    supplier_id BIGINT UNSIGNED NULL,
    stitch_count INT UNSIGNED NOT NULL DEFAULT 0,
    planned_quantity DECIMAL(18,4) NOT NULL,
    output_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    rejected_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    process_date DATE NOT NULL,
    unit_cost DECIMAL(18,6) NOT NULL DEFAULT 0,
    status ENUM('draft', 'sent', 'in_process', 'received', 'completed', 'cancelled') NOT NULL DEFAULT 'draft',
    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_embroidery_order_production FOREIGN KEY (production_order_id) REFERENCES production_orders (id),
    CONSTRAINT fk_embroidery_order_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers (id),
    CONSTRAINT fk_embroidery_order_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE finishing_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    production_order_id BIGINT UNSIGNED NOT NULL,
    process_number VARCHAR(60) NOT NULL UNIQUE,
    planned_quantity DECIMAL(18,4) NOT NULL,
    input_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    ironed_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    inspected_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    passed_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    rejected_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    process_date DATE NOT NULL,
    status ENUM('draft', 'in_progress', 'completed', 'cancelled') NOT NULL DEFAULT 'draft',
    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_finishing_order_production FOREIGN KEY (production_order_id) REFERENCES production_orders (id),
    CONSTRAINT fk_finishing_order_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE packing_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    production_order_id BIGINT UNSIGNED NOT NULL,
    packing_number VARCHAR(60) NOT NULL UNIQUE,
    planned_quantity DECIMAL(18,4) NOT NULL,
    packed_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    carton_quantity INT UNSIGNED NOT NULL DEFAULT 0,
    pieces_per_carton INT UNSIGNED NOT NULL DEFAULT 0,
    packing_date DATE NOT NULL,
    warehouse_id BIGINT UNSIGNED NULL,
    status ENUM('draft', 'in_progress', 'completed', 'dispatched', 'cancelled') NOT NULL DEFAULT 'draft',
    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_packing_order_production FOREIGN KEY (production_order_id) REFERENCES production_orders (id),
    CONSTRAINT fk_packing_order_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_packing_order_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE production_costs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    production_order_id BIGINT UNSIGNED NOT NULL,
    cost_type ENUM('material', 'labor', 'machine', 'utility', 'subcontract', 'overhead', 'other') NOT NULL,
    stage VARCHAR(80) NULL,
    description VARCHAR(255) NOT NULL,
    quantity DECIMAL(18,6) NOT NULL DEFAULT 0,
    unit_rate DECIMAL(18,6) NOT NULL DEFAULT 0,
    amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    currency_id BIGINT UNSIGNED NULL,
    reference_type VARCHAR(80) NULL,
    reference_id BIGINT UNSIGNED NULL,
    cost_date DATE NOT 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_production_cost_order FOREIGN KEY (production_order_id) REFERENCES production_orders (id),
    CONSTRAINT fk_production_cost_currency FOREIGN KEY (currency_id) REFERENCES currencies (id),
    CONSTRAINT fk_production_cost_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE production_losses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    production_order_id BIGINT UNSIGNED NOT NULL,
    stage VARCHAR(80) NOT NULL,
    loss_type ENUM('wastage', 'damage', 'defect', 'rejection', 'shrinkage', 'other') NOT NULL,
    quantity DECIMAL(18,6) NOT NULL,
    unit_cost DECIMAL(18,6) NOT NULL DEFAULT 0,
    loss_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    reason VARCHAR(500) NOT NULL,
    action_taken VARCHAR(500) NULL,
    loss_date DATE NOT NULL,
    reported_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_production_loss_order FOREIGN KEY (production_order_id) REFERENCES production_orders (id),
    CONSTRAINT fk_production_loss_reporter FOREIGN KEY (reported_by) REFERENCES users (id)
) ENGINE=InnoDB;