-- ============================================================
-- 生产订单完整删除函数
-- 适用场景：待开始、进行中、已完成三种状态的生产订单删除
-- 生成日期：2026-06-11
-- ============================================================

DELIMITER $$

DROP FUNCTION IF EXISTS fn_delete_production_order$$

CREATE FUNCTION fn_delete_production_order(
    p_order_id BIGINT,
    p_force_delete TINYINT DEFAULT 0  -- 0=安全模式(已生产不允许删除), 1=强制删除
) RETURNS VARCHAR(500)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE v_order_status INT DEFAULT 0;
    DECLARE v_complete_quantity DECIMAL(20,4) DEFAULT 0;
    DECLARE v_quantity DECIMAL(20,4) DEFAULT 0;
    DECLARE v_nps_no VARCHAR(100) DEFAULT '';
    DECLARE v_has_pick_record INT DEFAULT 0;
    DECLARE v_has_product_main INT DEFAULT 0;
    DECLARE v_has_quality_inspect INT DEFAULT 0;
    DECLARE v_has_stock_record INT DEFAULT 0;
    DECLARE v_deleted_count INT DEFAULT 0;
    DECLARE v_result VARCHAR(500) DEFAULT '';

    -- 查询订单基本信息
    SELECT status, complete_quantity, quantity, nps_no
    INTO v_order_status, v_complete_quantity, v_quantity, v_nps_no
    FROM production_order
    WHERE id = p_order_id;

    -- 订单不存在
    IF v_order_status IS NULL THEN
        RETURN CONCAT('删除失败：生产订单不存在，ID=', p_order_id);
    END IF;

    -- 安全模式下检查是否允许删除
    IF p_force_delete = 0 THEN
        -- 状态检查：只有待开始(1)和已取消(4)允许删除
        IF v_order_status = 2 THEN
            RETURN CONCAT('删除失败：订单[', v_nps_no, ']正在进行中，不允许删除');
        END IF;

        IF v_order_status = 3 THEN
            RETURN CONCAT('删除失败：订单[', v_nps_no, ']已完成，不允许删除');
        END IF;

        IF v_order_status = 5 THEN
            RETURN CONCAT('删除失败：订单[', v_nps_no, ']已结束，不允许删除');
        END IF;
    END IF;

    -- ========== 第一步：检查关联数据是否存在 ==========

    -- 检查领料记录
    SELECT COUNT(*) INTO v_has_pick_record
    FROM production_order_pick_record
    WHERE production_order_id = p_order_id;

    -- 检查报工记录
    SELECT COUNT(*) INTO v_has_product_main
    FROM production_product_main ppm
    INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
    WHERE pot.production_order_id = p_order_id;

    -- 检查质检记录
    SELECT COUNT(*) INTO v_has_quality_inspect
    FROM quality_inspect qi
    INNER JOIN production_product_main ppm ON qi.product_main_id = ppm.id
    INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
    WHERE pot.production_order_id = p_order_id;

    -- 检查库存记录（通过报工入库）
    SELECT COUNT(*) INTO v_has_stock_record
    FROM stock_in_record sir
    INNER JOIN production_product_main ppm ON sir.record_id = ppm.id
    INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
    WHERE pot.production_order_id = p_order_id
    AND sir.record_type IN ('2', '5');  -- 生产报工入库

    -- ========== 第二步：删除关联数据（从叶子到根） ==========

    -- 【第1层】删除质检附件
    DELETE FROM quality_inspect_file
    WHERE inspect_id IN (
        SELECT qi.id FROM quality_inspect qi
        INNER JOIN production_product_main ppm ON qi.product_main_id = ppm.id
        INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
        WHERE pot.production_order_id = p_order_id
    );
    SET v_deleted_count = v_deleted_count + ROW_COUNT();

    -- 【第2层】删除质检参数
    DELETE FROM quality_inspect_param
    WHERE inspect_id IN (
        SELECT qi.id FROM quality_inspect qi
        INNER JOIN production_product_main ppm ON qi.product_main_id = ppm.id
        INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
        WHERE pot.production_order_id = p_order_id
    );
    SET v_deleted_count = v_deleted_count + ROW_COUNT();

    -- 【第3层】删除不合格品记录
    DELETE FROM quality_unqualified
    WHERE inspect_id IN (
        SELECT qi.id FROM quality_inspect qi
        INNER JOIN production_product_main ppm ON qi.product_main_id = ppm.id
        INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
        WHERE pot.production_order_id = p_order_id
    );
    SET v_deleted_count = v_deleted_count + ROW_COUNT();

    -- 【第4层】删除质检记录（过程检验、出厂检验）
    DELETE FROM quality_inspect
    WHERE product_main_id IN (
        SELECT ppm.id FROM production_product_main ppm
        INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
        WHERE pot.production_order_id = p_order_id
    );
    SET v_deleted_count = v_deleted_count + ROW_COUNT();

    -- 【第5层】删除库存入库记录（生产报工入库、报废入库）
    DELETE FROM stock_in_record
    WHERE record_id IN (
        SELECT ppm.id FROM production_product_main ppm
        INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
        WHERE pot.production_order_id = p_order_id
    ) AND record_type IN ('2', '5', '7');  -- 生产报工入库、报废入库

    -- 删除质检入库记录
    DELETE FROM stock_in_record
    WHERE record_id IN (
        SELECT qi.id FROM quality_inspect qi
        INNER JOIN production_product_main ppm ON qi.product_main_id = ppm.id
        INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
        WHERE pot.production_order_id = p_order_id
    ) AND record_type = '6';  -- 质检入库
    SET v_deleted_count = v_deleted_count + ROW_COUNT();

    -- 【第6层】删除库存出库记录（生产报工出库）
    DELETE FROM stock_out_record
    WHERE record_id IN (
        SELECT ppm.id FROM production_product_main ppm
        INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
        WHERE pot.production_order_id = p_order_id
    ) AND record_type = '13';  -- 生产报工出库
    SET v_deleted_count = v_deleted_count + ROW_COUNT();

    -- 【第7层】删除报工产出
    DELETE FROM production_product_output
    WHERE production_product_main_id IN (
        SELECT ppm.id FROM production_product_main ppm
        INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
        WHERE pot.production_order_id = p_order_id
    );
    SET v_deleted_count = v_deleted_count + ROW_COUNT();

    -- 【第8层】删除报工投入
    DELETE FROM production_product_input
    WHERE production_product_main_id IN (
        SELECT ppm.id FROM production_product_main ppm
        INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
        WHERE pot.production_order_id = p_order_id
    );
    SET v_deleted_count = v_deleted_count + ROW_COUNT();

    -- 【第9层】删除生产核算
    DELETE FROM production_account
    WHERE production_product_main_id IN (
        SELECT ppm.id FROM production_product_main ppm
        INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
        WHERE pot.production_order_id = p_order_id
    );
    SET v_deleted_count = v_deleted_count + ROW_COUNT();

    -- 【第10层】删除报工工序参数（通过production_product_main_id关联）
    DELETE FROM production_order_routing_operation_param
    WHERE production_product_main_id IN (
        SELECT ppm.id FROM production_product_main ppm
        INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
        WHERE pot.production_order_id = p_order_id
    );
    SET v_deleted_count = v_deleted_count + ROW_COUNT();

    -- 【第11层】删除报工主表
    DELETE FROM production_product_main
    WHERE production_operation_task_id IN (
        SELECT id FROM production_operation_task
        WHERE production_order_id = p_order_id
    );
    SET v_deleted_count = v_deleted_count + ROW_COUNT();

    -- 【第12层】删除领料记录
    DELETE FROM production_order_pick_record
    WHERE production_order_id = p_order_id;
    SET v_deleted_count = v_deleted_count + ROW_COUNT();

    -- 【第13层】删除领料入库记录（退料）
    DELETE FROM stock_in_record
    WHERE record_id IN (
        SELECT id FROM production_order_pick
        WHERE production_order_id = p_order_id
    ) AND record_type IN ('20', '22');  -- 领料退料入库
    SET v_deleted_count = v_deleted_count + ROW_COUNT();

    -- 【第14层】删除领料单
    DELETE FROM production_order_pick
    WHERE production_order_id = p_order_id;
    SET v_deleted_count = v_deleted_count + ROW_COUNT();

    -- 【第15层】删除生产工单
    DELETE FROM production_operation_task
    WHERE production_order_id = p_order_id;
    SET v_deleted_count = v_deleted_count + ROW_COUNT();

    -- 【第16层】删除工序参数（订单级别）
    DELETE FROM production_order_routing_operation_param
    WHERE production_order_id = p_order_id;
    SET v_deleted_count = v_deleted_count + ROW_COUNT();

    -- 【第17层】删除工序
    DELETE FROM production_order_routing_operation
    WHERE production_order_id = p_order_id;
    SET v_deleted_count = v_deleted_count + ROW_COUNT();

    -- 【第18层】删除工艺路线
    DELETE FROM production_order_routing
    WHERE production_order_id = p_order_id;
    SET v_deleted_count = v_deleted_count + ROW_COUNT();

    -- 【第19层】删除BOM结构
    DELETE FROM production_bom_structure
    WHERE production_order_id = p_order_id;
    SET v_deleted_count = v_deleted_count + ROW_COUNT();

    -- 【第20层】删除订单BOM
    DELETE FROM production_order_bom
    WHERE production_order_id = p_order_id;
    SET v_deleted_count = v_deleted_count + ROW_COUNT();

    -- 【第21层】删除生产计划关联（回退计划已下发数量）
    -- 注意：这里需要根据业务逻辑回退production_plan的quantity_issued和status
    -- 由于production_plan_ids是JSON格式[1,2,3]，需要特殊处理
    -- 此处仅删除订单，计划回退逻辑建议在应用层处理

    -- 【第22层】删除生产订单主表
    DELETE FROM production_order
    WHERE id = p_order_id;
    SET v_deleted_count = v_deleted_count + ROW_COUNT();

    -- 返回结果
    SET v_result = CONCAT(
        '删除成功：订单[', v_nps_no, ']，',
        '状态=', CASE v_order_status
            WHEN 1 THEN '待开始'
            WHEN 2 THEN '进行中'
            WHEN 3 THEN '已完成'
            WHEN 4 THEN '已取消'
            WHEN 5 THEN '已结束'
            ELSE '未知'
        END, '，',
        '共删除', v_deleted_count, '条关联数据'
    );

    RETURN v_result;

END$$

DELIMITER ;

-- ============================================================
-- 批量删除生产订单存储过程
-- ============================================================

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_batch_delete_production_order$$

CREATE PROCEDURE sp_batch_delete_production_order(
    IN p_order_ids TEXT,           -- 订单ID列表，逗号分隔，如 "1,2,3"
    IN p_force_delete TINYINT      -- 0=安全模式, 1=强制删除
)
BEGIN
    DECLARE v_order_id BIGINT;
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE v_cursor CURSOR FOR
        SELECT CAST(TRIM(value) AS UNSIGNED)
        FROM JSON_TABLE(CONCAT('[', p_order_ids, ']'), '$[*]' COLUMNS(value VARCHAR(20) PATH '$')) AS jt;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

    DECLARE v_success_count INT DEFAULT 0;
    DECLARE v_fail_count INT DEFAULT 0;
    DECLARE v_result_msg VARCHAR(500);

    -- 创建临时表记录结果
    DROP TEMPORARY TABLE IF EXISTS tmp_delete_result;
    CREATE TEMPORARY TABLE tmp_delete_result (
        order_id BIGINT,
        result VARCHAR(500)
    );

    OPEN v_cursor;

    read_loop: LOOP
        FETCH v_cursor INTO v_order_id;
        IF v_done THEN
            LEAVE read_loop;
        END IF;

        -- 调用删除函数
        SET v_result_msg = fn_delete_production_order(v_order_id, p_force_delete);

        -- 记录结果
        INSERT INTO tmp_delete_result (order_id, result) VALUES (v_order_id, v_result_msg);

        -- 统计
        IF v_result_msg LIKE '删除成功%' THEN
            SET v_success_count = v_success_count + 1;
        ELSE
            SET v_fail_count = v_fail_count + 1;
        END IF;

    END LOOP;

    CLOSE v_cursor;

    -- 输出结果
    SELECT
        CONCAT('批量删除完成：成功', v_success_count, '个，失败', v_fail_count, '个') AS summary;

    SELECT * FROM tmp_delete_result;

    DROP TEMPORARY TABLE IF EXISTS tmp_delete_result;

END$$

DELIMITER ;

-- ============================================================
-- 使用示例
-- ============================================================

-- 示例1：安全模式删除单个订单（只能删除待开始/已取消状态）
-- SELECT fn_delete_production_order(123, 0);

-- 示例2：强制删除单个订单（忽略状态检查）
-- SELECT fn_delete_production_order(123, 1);

-- 示例3：批量删除订单
-- CALL sp_batch_delete_production_order('123,124,125', 0);

-- 示例4：批量强制删除
-- CALL sp_batch_delete_production_order('123,124,125', 1);