CREATE TABLE defect_types (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(40) NOT NULL,
    name VARCHAR(150) NOT NULL,
    defect_category ENUM('fabric', 'cutting', 'sewing', 'washing', 'printing', 'embroidery', 'finishing', 'packing', 'general') NOT NULL DEFAULT 'general',
    severity ENUM('minor', 'major', 'critical') NOT NULL DEFAULT 'minor',
    default_points DECIMAL(9,4) NOT NULL DEFAULT 1,
    description VARCHAR(500) NULL,
    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_defect_code (company_id, code),
    CONSTRAINT fk_defect_type_company FOREIGN KEY (company_id) REFERENCES companies (id)
) ENGINE=InnoDB;

CREATE TABLE fabric_inspections (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    inspection_number VARCHAR(60) NOT NULL UNIQUE,
    item_id BIGINT UNSIGNED NOT NULL,
    item_batch_id BIGINT UNSIGNED NULL,
    fabric_roll_id BIGINT UNSIGNED NULL,
    supplier_id BIGINT UNSIGNED NULL,
    warehouse_id BIGINT UNSIGNED NULL,
    inspection_date DATE NOT NULL,
    inspection_method ENUM('4_point', '10_point', 'visual', 'lab') NOT NULL DEFAULT '4_point',
    inspected_quantity DECIMAL(18,6) NOT NULL,
    sample_quantity DECIMAL(18,6) NOT NULL,
    defect_type_id BIGINT UNSIGNED NULL,
    defect_count INT UNSIGNED NOT NULL DEFAULT 0,
    total_defect_points DECIMAL(18,4) NOT NULL DEFAULT 0,
    points_per_100_units DECIMAL(18,4) NOT NULL DEFAULT 0,
    maximum_allowed_points DECIMAL(18,4) NOT NULL DEFAULT 40,
    grade VARCHAR(20) NULL,
    result ENUM('pass', 'fail', 'hold') NOT NULL,
    defect_summary JSON NULL,
    remarks VARCHAR(500) NULL,
    inspected_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_fabric_inspection_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_fabric_inspection_item FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT fk_fabric_inspection_batch FOREIGN KEY (item_batch_id) REFERENCES item_batches (id),
    CONSTRAINT fk_fabric_inspection_roll FOREIGN KEY (fabric_roll_id) REFERENCES fabric_rolls (id),
    CONSTRAINT fk_fabric_inspection_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers (id),
    CONSTRAINT fk_fabric_inspection_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_fabric_inspection_defect FOREIGN KEY (defect_type_id) REFERENCES defect_types (id),
    CONSTRAINT fk_fabric_inspection_user FOREIGN KEY (inspected_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE inline_qc (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    inspection_number VARCHAR(60) NOT NULL UNIQUE,
    production_order_id BIGINT UNSIGNED NOT NULL,
    production_line_id BIGINT UNSIGNED NULL,
    bundle_card_id BIGINT UNSIGNED NULL,
    operation_id BIGINT UNSIGNED NULL,
    inspection_date DATETIME NOT NULL,
    inspected_quantity DECIMAL(18,4) NOT NULL,
    passed_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,
    defect_type_id BIGINT UNSIGNED NULL,
    defect_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    dhu_percent DECIMAL(9,4) NOT NULL DEFAULT 0,
    pass_rate_percent DECIMAL(9,4) NOT NULL DEFAULT 0,
    result ENUM('pass', 'fail', 'hold') NOT NULL,
    corrective_action VARCHAR(500) NULL,
    remarks VARCHAR(500) NULL,
    inspected_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_inline_qc_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_inline_qc_production FOREIGN KEY (production_order_id) REFERENCES production_orders (id),
    CONSTRAINT fk_inline_qc_line FOREIGN KEY (production_line_id) REFERENCES production_lines (id),
    CONSTRAINT fk_inline_qc_bundle FOREIGN KEY (bundle_card_id) REFERENCES bundle_cards (id),
    CONSTRAINT fk_inline_qc_operation FOREIGN KEY (operation_id) REFERENCES operations (id),
    CONSTRAINT fk_inline_qc_defect FOREIGN KEY (defect_type_id) REFERENCES defect_types (id),
    CONSTRAINT fk_inline_qc_user FOREIGN KEY (inspected_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE final_qc (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    inspection_number VARCHAR(60) NOT NULL UNIQUE,
    production_order_id BIGINT UNSIGNED NOT NULL,
    inspection_date DATETIME NOT NULL,
    aql_level DECIMAL(5,2) NOT NULL DEFAULT 2.50,
    lot_quantity DECIMAL(18,4) NOT NULL,
    sample_quantity DECIMAL(18,4) NOT NULL,
    inspected_quantity DECIMAL(18,4) NOT NULL,
    passed_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    rejected_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    defect_type_id BIGINT UNSIGNED NULL,
    defect_quantity DECIMAL(18,4) NOT NULL DEFAULT 0,
    allowed_defects INT UNSIGNED NOT NULL DEFAULT 0,
    defect_rate_percent DECIMAL(9,4) NOT NULL DEFAULT 0,
    result ENUM('pass', 'fail', 'hold') NOT NULL,
    shipment_release BOOLEAN NOT NULL DEFAULT FALSE,
    corrective_action VARCHAR(500) NULL,
    remarks VARCHAR(500) NULL,
    inspected_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,
    CONSTRAINT fk_final_qc_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_final_qc_production FOREIGN KEY (production_order_id) REFERENCES production_orders (id),
    CONSTRAINT fk_final_qc_defect FOREIGN KEY (defect_type_id) REFERENCES defect_types (id),
    CONSTRAINT fk_final_qc_user FOREIGN KEY (inspected_by) REFERENCES users (id),
    CONSTRAINT fk_final_qc_approver FOREIGN KEY (approved_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE quality_reports (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    report_number VARCHAR(60) NOT NULL UNIQUE,
    report_type ENUM('fabric', 'inline', 'final', 'production_summary', 'defect_analysis') NOT NULL,
    production_order_id BIGINT UNSIGNED NULL,
    date_from DATE NOT NULL,
    date_to DATE NOT NULL,
    total_inspected DECIMAL(18,4) NOT NULL DEFAULT 0,
    total_passed DECIMAL(18,4) NOT NULL DEFAULT 0,
    total_rejected DECIMAL(18,4) NOT NULL DEFAULT 0,
    total_defects DECIMAL(18,4) NOT NULL DEFAULT 0,
    defect_rate_percent DECIMAL(9,4) NOT NULL DEFAULT 0,
    pass_rate_percent DECIMAL(9,4) NOT NULL DEFAULT 0,
    summary_data JSON NULL,
    status ENUM('generated', 'reviewed', 'approved', 'archived') NOT NULL DEFAULT 'generated',
    generated_by BIGINT UNSIGNED NOT NULL,
    generated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reviewed_by BIGINT UNSIGNED NULL,
    reviewed_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,
    CONSTRAINT fk_quality_report_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_quality_report_production FOREIGN KEY (production_order_id) REFERENCES production_orders (id),
    CONSTRAINT fk_quality_report_generator FOREIGN KEY (generated_by) REFERENCES users (id),
    CONSTRAINT fk_quality_report_reviewer FOREIGN KEY (reviewed_by) REFERENCES users (id)
) ENGINE=InnoDB;