<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hdkj.lt.mq.mapper.fault.FhzgFaultAnalyzeCalculateDecudeMapper">

    <select id="getLineNameByFaultId" resultType="com.hdkj.lt.mq.entity.po.FhzgFaultPowerCut">
        select line_id, line_name
        from fhzg_fault_power_cut
        where id = #{faultId}
    </select>

    <select id="getSwitchSection" resultType="com.hdkj.lt.core.bizms.modle.po.fault.FaultAwaitTransferSwitchSection">
        WITH RECURSIVE hierarchy AS (
        SELECT psr_id, `name`, start_kg_id, psr_type, psr_id link_id
        FROM t_feeder_topo
        WHERE feeder = #{feeder}
        AND psr_id = #{psrId}
        UNION ALL
        SELECT child.psr_id, child.name, child.start_kg_id, child.psr_type,
        if(child.normal_open = 'true', '-', child.psr_id) link_id
        FROM t_feeder_topo child
        JOIN hierarchy parent ON child.start_kg_id = parent.psr_id
        WHERE child.feeder = #{feeder}
        AND child.psr_type IN
        <foreach collection='kgPsrType' item='item' open='(' separator=',' close=')'>
            #{item}
        </foreach>
        ) SELECT parent.psr_id AS startSwitchId, parent.name AS startSwitchName, parent.psr_type as startSwitchType,
        GROUP_CONCAT(child.psr_id SEPARATOR ',') AS endSwitchId, GROUP_CONCAT(child.name SEPARATOR ',') AS endSwitchName
        FROM hierarchy child RIGHT JOIN hierarchy parent ON child.start_kg_id = parent.psr_id where parent.link_id !=
        '-'
        GROUP BY parent.psr_id, parent.name, parent.psr_type
    </select>

    <insert id="saveSwitchSectionBatch"
            parameterType="com.hdkj.lt.core.bizms.modle.po.fault.FaultAwaitTransferSwitchSection"
            useGeneratedKeys="true" keyProperty="id">
        insert into
        fhzg_fault_await_transfer_switch_section(fault_id,switch_name,start_switch_id,start_switch_name,area_psr_id,
        start_switch_type,end_switch_id,end_switch_name,switch_loads,creator,create_time)
        values
        <foreach collection='switchSectionList' item='switchSection' separator=','>
            (#{switchSection.faultId},#{switchSection.switchName},#{switchSection.startSwitchId},#{switchSection.startSwitchName},
            #{switchSection.areaPsrId},#{switchSection.startSwitchType},#{switchSection.endSwitchId},#{switchSection.endSwitchName},
            #{switchSection.switchLoads},'111',NOW())
        </foreach>
    </insert>

    <select id="getSwitchSectionList"
            resultType="com.hdkj.lt.core.bizms.modle.po.fault.FaultAwaitTransferSwitchSection">
        select t.* from (
        select t.psr_id, if(count(t.id) > 1, max(kg_section_id), max(id)) id,
        if(count(t.id) > 1, max(kg_id), max(start_switch_id)) start_switch_id,
        if(count(t.id) > 1, max(kg_name), max(start_switch_name)) start_switch_name
        from (
        select ifnull(c.join_ec,a.psr_id) psr_id, b.id, b.start_switch_id, b.start_switch_name,
        e.id kg_section_id, b.start_switch_id kg_id, e.start_switch_name kg_name
        from t_feeder_topo a
        join fhzg_fault_await_transfer_switch_section b on a.start_kg_id = b.start_switch_id
        left join dwd_shb_ds_joinec_transformer_base c on a.psr_id = c.psr_id
        left join t_feeder_topo d on a.start_kg_id = d.psr_id
        left join fhzg_fault_await_transfer_switch_section e on d.start_kg_id = e.start_switch_id
        where a.psr_type in
        <foreach collection='kgPsrType' item='item' open='(' separator=',' close=')'>
            #{item}
        </foreach>
        and a.feeder = #{feeder}
        and b.fault_id = #{faultId}
        ) t group by t.psr_id
        ) t where t.psr_id in
        <foreach collection='psyIdList' item='item' open='(' separator=',' close=')'>
            #{item}
        </foreach>
    </select>
    <select id="getLineVoltageByLineId" resultType="com.hdkj.lt.mq.entity.vo.FeederDeviceVO">
        select t.psr_id,t.name,t.feeder_rate_capacity,s.dict_label volt_value
        from dwd_shb_ds_feeder_base t
        join sys_dict_data s on t.voltage_level=s.dict_value
        join sys_dict_type d on d.dict_type = s.dict_type
        where d.dict_type = 'voltage_level_map'
        and t.psr_id in
        <foreach collection='lineIds' item='item' open='(' separator=',' close=')'>#{item}</foreach>
    </select>

    <update id="updateSwitchSectionSwitchLoads">
        update fhzg_fault_await_transfer_switch_section a
        set a.switch_loads
                = (select sum(user_loads) from fhzg_fault_await_transfer_users b where b.switch_section_id = a.id)
        where a.fault_id = #{faultId}
    </update>

    <update id="updateSwitchingSequenceIsConnect">
        update fhzg_fault_transfer_scheme_switching_sequence a
        set switch_type = "1"
        where exists (select 1
                      from fhzg_fault_transfer_scheme_path_detail b
                      where b.switch_id = a.switch_id
                        and b.scheme_id = a.scheme_id)
          and a.scheme_id in (select id from fhzg_fault_transfer_scheme where fault_id = #{faultId})
    </update>

    <delete id="deleteTransferUserByFaultId">
        delete
        from fhzg_fault_await_transfer_users
        where fault_id = #{faultId}
    </delete>

    <delete id="deleteSwitchSectionByFaultId">
        delete
        from fhzg_fault_await_transfer_switch_section
        where fault_id = #{faultId}
    </delete>

    <insert id="saveSwitchingLoopCheck"
            parameterType="com.hdkj.lt.core.bizms.modle.po.fault.FaultTransferSchemeSwitchingLoopCheck"
            useGeneratedKeys="true" keyProperty="id">
        insert into fhzg_fault_transfer_scheme_switching_loop_check(id, switching_id, line_id, line_name,
                                                                    opposite_line_id, opposite_line_name,
                                                                    phase_angle_diff, phase_angle_diff_threshold,
                                                                    phase_angle_diff_check, line_voltage,
                                                                    opposite_line_voltage,
                                                                    line_voltage_diff, line_voltage_diff_threshold,
                                                                    line_voltage_diff_check, loop_current,
                                                                    loop_current_threshold,
                                                                    loop_current_check, switch_current_check,
                                                                    segment_current_check, creator, create_time)
        values (#{id}, #{switchingId}, #{lineId}, #{lineName}, #{oppositeLineId}, #{oppositeLineName},
                #{phaseAngleDiff},
                #{phaseAngleDiffThreshold}, #{phaseAngleDiffCheck}, #{lineVoltage}, #{oppositeLineVoltage},
                #{lineVoltageDiff},
                #{lineVoltageDiffThreshold}, #{lineVoltageDiffCheck}, #{loopCurrent}, #{loopCurrentThreshold},
                #{loopCurrentCheck}, #{switchCurrentCheck},
                #{segmentCurrentCheck}, '111', NOW())
    </insert>

    <insert id="saveSwitchingSequence"
            parameterType="com.hdkj.lt.core.bizms.modle.po.fault.FaultTransferSchemeSwitchingSequence"
            useGeneratedKeys="true" keyProperty="id">
        insert into fhzg_fault_transfer_scheme_switching_sequence(id, scheme_id, switch_id, switch_name, switch_type,
                                                                  is_auto_switch,
                                                                  operation_content, switching_seq,
                                                                  loop_close_check,
                                                                  final_state_check,
                                                                  creator, create_time)
        values (#{id}, #{schemeId}, #{switchId}, #{switchName}, #{switchType}, #{isAutoSwitch}, #{operationContent},
                #{switchingSeq},
                #{loopCloseCheck},
                #{finalStateCheck},
                '111', NOW())
    </insert>

    <insert id="saveSwitchingFinalCheck"
            parameterType="com.hdkj.lt.core.bizms.modle.po.fault.FaultTransferSchemeSwitchingFinalCheck"
            useGeneratedKeys="true" keyProperty="id">
        insert into fhzg_fault_transfer_scheme_switching_final_check(id, switching_id, line_id, line_name,
                                                                     opposite_line_id, opposite_line_name,
                                                                     allow_max_current, estimate_max_current,
                                                                     estimate_max_load_factor, load_factor_threshold,
                                                                     load_factor_check, switch_current_check,
                                                                     segment_current_check, creator, create_time)
        values (#{id}, #{switchingId}, #{lineId}, #{lineName}, #{oppositeLineId}, #{oppositeLineName},
                #{allowMaxCurrent},
                #{estimateMaxCurrent}, #{estimateMaxLoadFactor}, #{loadFactorThreshold}, #{loadFactorCheck},
                #{switchCurrentCheck}, #{segmentCurrentCheck}, '111', NOW())
    </insert>

    <delete id="deleteTransferSchemeAndLinkInfoByFaultId">
        delete x.* from fhzg_fault_transfer_scheme_switching_loop_check x,(
        SELECT t.id from fhzg_fault_transfer_scheme_switching_sequence t
        join fhzg_fault_transfer_scheme s on t.scheme_id=s.id and s.fault_id = #{faultId}
        ) y where x.switching_id = y.id;
        delete x.* from fhzg_fault_transfer_scheme_switching_final_check x,(
        SELECT t.id from fhzg_fault_transfer_scheme_switching_sequence t
        join fhzg_fault_transfer_scheme s on t.scheme_id=s.id and s.fault_id = #{faultId}
        ) y where x.switching_id = y.id;

        delete x.* from fhzg_fault_transfer_scheme_switching_final_sw_current_detail x,(
        SELECT t.id from fhzg_fault_transfer_scheme_switching_sequence t
        join fhzg_fault_transfer_scheme s on t.scheme_id=s.id and s.fault_id = #{faultId}
        ) y where x.switching_id = y.id;

        delete x.* from fhzg_fault_transfer_scheme_switching_final_seg_current_detail x,(
        SELECT t.id from fhzg_fault_transfer_scheme_switching_sequence t
        join fhzg_fault_transfer_scheme s on t.scheme_id=s.id and s.fault_id = #{faultId}
        ) y where x.switching_id = y.id;

        delete x.* from fhzg_fault_transfer_scheme_switching_sequence x,
        fhzg_fault_transfer_scheme y where x.scheme_id = y.id and y.fault_id = #{faultId};
        <!-- fhzg_fault_breaker_svg_after未使用，使用的是fhzg_info_breaker_svg_after -->
        delete x.* from fhzg_info_breaker_svg_after x,
        fhzg_fault_transfer_scheme y where x.scheme_id = y.id and y.fault_id = #{faultId};

        delete x.* from fhzg_fault_transfer_scheme_path_detail x,
        fhzg_fault_transfer_scheme y where x.scheme_id = y.id and y.fault_id = #{faultId};
        delete x.* from fhzg_fault_transfer_scheme_opposite_line_load_factor x,
        fhzg_fault_transfer_scheme y where x.scheme_id = y.id and y.fault_id = #{faultId};
        delete x.* from fhzg_fault_transfer_scheme_users x,
        fhzg_fault_transfer_scheme y where x.scheme_id = y.id and y.fault_id = #{faultId};
        delete x.* from fhzg_fault_transferable_scheme_users x,
        fhzg_fault_transfer_scheme y where x.scheme_id = y.id and y.fault_id = #{faultId};

        delete x.* from fhzg_fault_opposite_line_concat_switch x,
        fhzg_fault_transfer_scheme y where x.scheme_id = y.id and y.fault_id = #{faultId};
        delete from fhzg_fault_opposite_line_carry_capacity where fault_id = #{faultId};

        delete x.* from fhzg_fault_transfer_scheme_evaluation_index x,
        fhzg_fault_transfer_scheme y where x.scheme_id = y.id and y.fault_id = #{faultId};
        delete x.* from fhzg_fault_transfer_scheme_strategy_score x,
        fhzg_fault_transfer_scheme y where x.scheme_id = y.id and y.fault_id = #{faultId};

        delete from fhzg_fault_await_transfer_users where fault_id = #{faultId};
        delete from fhzg_fault_transfer_scheme where fault_id = #{faultId};
    </delete>
    <sql id="schemeIdsByFaultId">
        SELECT id
        FROM fhzg_fault_transfer_scheme
        WHERE fault_id = #{faultId}
    </sql>

    <sql id="switchingIdsByFaultId">
        SELECT t.id
        FROM fhzg_fault_transfer_scheme_switching_sequence t
                 JOIN fhzg_fault_transfer_scheme s ON t.scheme_id = s.id
        WHERE s.fault_id = #{faultId}
    </sql>

    <!-- 1. 删除 switching 相关子表 -->
    <delete id="deleteSwitchingLoopCheckByFaultId">
        DELETE FROM fhzg_fault_transfer_scheme_switching_loop_check
        WHERE switching_id IN (<include refid="switchingIdsByFaultId"/>)
    </delete>

    <delete id="deleteSwitchingFinalCheckByFaultId">
        DELETE FROM fhzg_fault_transfer_scheme_switching_final_check
        WHERE switching_id IN (<include refid="switchingIdsByFaultId"/>)
    </delete>

    <delete id="deleteLoopSwCurrentDetailByFaultId">
        DELETE FROM fhzg_fault_transfer_scheme_switching_loop_sw_current_detail
        WHERE switching_id IN (<include refid="switchingIdsByFaultId"/>)
    </delete>

    <delete id="deleteLoopSegCurrentDetailByFaultId">
        DELETE FROM fhzg_fault_transfer_scheme_switching_loop_seg_current_detail
        WHERE switching_id IN (<include refid="switchingIdsByFaultId"/>)
    </delete>

    <delete id="deleteFinalSwCurrentDetailByFaultId">
        DELETE FROM fhzg_fault_transfer_scheme_switching_final_sw_current_detail
        WHERE switching_id IN (<include refid="switchingIdsByFaultId"/>)
    </delete>

    <delete id="deleteFinalSegCurrentDetailByFaultId">
        DELETE FROM fhzg_fault_transfer_scheme_switching_final_seg_current_detail
        WHERE switching_id IN (<include refid="switchingIdsByFaultId"/>)
    </delete>

    <!-- 2. 删除 switching_sequence -->
    <delete id="deleteSwitchingSequenceByFaultId">
        DELETE FROM fhzg_fault_transfer_scheme_switching_sequence
        WHERE scheme_id IN (<include refid="schemeIdsByFaultId"/>)
    </delete>

    <!-- 3. 删除其他关联表 -->
    <delete id="deleteBreakerSvgAfterByFaultId">
        DELETE FROM fhzg_info_breaker_svg_after
        WHERE scheme_id IN (<include refid="schemeIdsByFaultId"/>)
    </delete>

    <delete id="deletePathDetailByFaultId">
        DELETE FROM fhzg_fault_transfer_scheme_path_detail
        WHERE scheme_id IN (<include refid="schemeIdsByFaultId"/>)
    </delete>

    <delete id="deleteOppositeLineLoadFactorByFaultId">
        DELETE FROM fhzg_fault_transfer_scheme_opposite_line_load_factor
        WHERE scheme_id IN (<include refid="schemeIdsByFaultId"/>)
    </delete>

    <delete id="deleteSchemeUsersByFaultId">
        DELETE FROM fhzg_fault_transfer_scheme_users
        WHERE scheme_id IN (<include refid="schemeIdsByFaultId"/>)
    </delete>

    <delete id="deleteTransferableSchemeUsersByFaultId">
        DELETE FROM fhzg_fault_transferable_scheme_users
        WHERE scheme_id IN (<include refid="schemeIdsByFaultId"/>)
    </delete>

    <delete id="deleteOppositeLineConcatSwitchByFaultId">
        DELETE FROM fhzg_fault_opposite_line_concat_switch
        WHERE scheme_id IN (<include refid="schemeIdsByFaultId"/>)
    </delete>

    <delete id="deleteEvaluationIndexByFaultId">
        DELETE FROM fhzg_fault_transfer_scheme_evaluation_index
        WHERE scheme_id IN (<include refid="schemeIdsByFaultId"/>)
    </delete>

    <delete id="deleteStrategyScoreByFaultId">
        DELETE FROM fhzg_fault_transfer_scheme_strategy_score
        WHERE scheme_id IN (<include refid="schemeIdsByFaultId"/>)
    </delete>

    <!-- 4. 直接按 fault_id 删除的表 -->
    <delete id="deleteCarryCapacityByFaultId">
        DELETE
        FROM fhzg_fault_opposite_line_carry_capacity
        WHERE fault_id = #{faultId}
    </delete>

    <delete id="deleteAwaitTransferUsersByFaultId">
        DELETE
        FROM fhzg_fault_await_transfer_users
        WHERE fault_id = #{faultId}
    </delete>

    <!-- 5. 最后删除主表 -->
    <delete id="deleteTransferSchemeByFaultId">
        DELETE
        FROM fhzg_fault_transfer_scheme
        WHERE fault_id = #{faultId}
    </delete>

    <insert id="saveConcatSwitchList" useGeneratedKeys="true" keyProperty="id">
        insert into fhzg_fault_opposite_line_concat_switch(scheme_id,capacity_id,concat_psr_id,concat_psr_name) values
        <foreach collection='concatSwitchList' item='dto' separator=','>
            (#{dto.schemeId},#{dto.capacityId},#{dto.concatPsrId},#{dto.concatPsrName})
        </foreach>
    </insert>

    <delete id="deleteSwitchSectionByConnect">
        delete
        a.* FROM fhzg_fault_await_transfer_switch_section a
        WHERE EXISTS ( SELECT 1 FROM fhzg_fault_transfer_scheme b, fhzg_fault_opposite_line_concat_switch c WHERE b.id = c.scheme_id AND b.fault_id = a.fault_id and a.start_switch_id = c.concat_psr_id )
          AND a.fault_id =
        #{faultId}
    </delete>
</mapper>
