<?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.ga.core.mapper.receive.ReceiveMapper">

    <resultMap id="ProfileMap"  type="com.ga.core.vo.receive.CompanyProfileVO"/>
    <resultMap id="FieldMap"    type="com.ga.core.vo.receive.CompanyFieldMappingVO"/>
    <resultMap id="CodeMap"     type="com.ga.core.vo.receive.CodeMappingVO"/>
    <resultMap id="RawMap"      type="com.ga.core.vo.receive.RawCommissionDataVO"/>
    <resultMap id="ErrorMap"    type="com.ga.core.vo.receive.ParseErrorLogVO"/>

    <!-- profile -->
    <select id="selectAllProfiles" resultMap="ProfileMap">
        SELECT * FROM company_profile ORDER BY company_code
    </select>

    <select id="selectProfile" resultMap="ProfileMap">
        SELECT * FROM company_profile WHERE company_code = #{companyCode}
    </select>

    <insert id="insertProfile" parameterType="com.ga.core.vo.receive.CompanyProfileVO">
        INSERT INTO company_profile (company_code, data_format, receive_method, file_encoding,
            delimiter, header_row, data_start_row, sheet_name, date_format, amount_format,
            api_url, ftp_host, ftp_path, receive_schedule, is_active)
        VALUES (#{companyCode}, #{dataFormat}, #{receiveMethod}, COALESCE(#{fileEncoding},'UTF-8'),
            #{delimiter}, COALESCE(#{headerRow},1), COALESCE(#{dataStartRow},2),
            #{sheetName}, #{dateFormat}, #{amountFormat},
            #{apiUrl}, #{ftpHost}, #{ftpPath}, #{receiveSchedule}, COALESCE(#{isActive},'Y'))
    </insert>

    <update id="updateProfile" parameterType="com.ga.core.vo.receive.CompanyProfileVO">
        UPDATE company_profile SET
            data_format = #{dataFormat}, receive_method = #{receiveMethod},
            file_encoding = #{fileEncoding}, delimiter = #{delimiter},
            header_row = #{headerRow}, data_start_row = #{dataStartRow},
            sheet_name = #{sheetName}, date_format = #{dateFormat},
            amount_format = #{amountFormat}, api_url = #{apiUrl},
            ftp_host = #{ftpHost}, ftp_path = #{ftpPath},
            receive_schedule = #{receiveSchedule}, is_active = #{isActive}
        WHERE company_code = #{companyCode}
    </update>

    <!-- field_mapping -->
    <select id="selectFieldMappings" resultMap="FieldMap">
        SELECT * FROM company_field_mapping
        WHERE company_code = #{companyCode}
        <if test="targetTable != null and targetTable != ''">AND target_table = #{targetTable}</if>
        ORDER BY target_table, sort_order
    </select>

    <insert id="insertFieldMapping" parameterType="com.ga.core.vo.receive.CompanyFieldMappingVO"
            useGeneratedKeys="true" keyProperty="mappingId">
        INSERT INTO company_field_mapping (company_code, source_field, source_col_index,
            target_table, target_field, data_type, transform_rule, default_value,
            is_required, sort_order, version, effective_from)
        VALUES (#{companyCode}, #{sourceField}, #{sourceColIndex},
                #{targetTable}, #{targetField}, #{dataType}, #{transformRule}, #{defaultValue},
                COALESCE(#{isRequired},'N'), COALESCE(#{sortOrder},0),
                COALESCE(#{version},1), COALESCE(#{effectiveFrom}, CURRENT_DATE))
    </insert>

    <update id="updateFieldMapping" parameterType="com.ga.core.vo.receive.CompanyFieldMappingVO">
        UPDATE company_field_mapping SET
            source_field = #{sourceField}, source_col_index = #{sourceColIndex},
            target_table = #{targetTable}, target_field = #{targetField},
            data_type = #{dataType}, transform_rule = #{transformRule},
            default_value = #{defaultValue}, is_required = #{isRequired},
            sort_order = #{sortOrder}
        WHERE mapping_id = #{mappingId}
    </update>

    <delete id="deleteFieldMapping">DELETE FROM company_field_mapping WHERE mapping_id = #{mappingId}</delete>

    <!-- code_mapping -->
    <select id="selectCodeMappings" resultMap="CodeMap">
        SELECT * FROM code_mapping
        WHERE company_code = #{companyCode}
        <if test="codeType != null and codeType != ''">AND code_type = #{codeType}</if>
        ORDER BY code_type, source_code
    </select>

    <select id="findTargetCode" resultType="string">
        SELECT target_code FROM code_mapping
        WHERE company_code = #{companyCode} AND code_type = #{codeType}
          AND source_code = #{sourceCode} AND is_active = 'Y'
        LIMIT 1
    </select>

    <insert id="insertCodeMapping" parameterType="com.ga.core.vo.receive.CodeMappingVO"
            useGeneratedKeys="true" keyProperty="codeId">
        INSERT INTO code_mapping (company_code, code_type, source_code, source_name,
                                  target_code, target_name, is_active)
        VALUES (#{companyCode}, #{codeType}, #{sourceCode}, #{sourceName},
                #{targetCode}, #{targetName}, COALESCE(#{isActive},'Y'))
    </insert>

    <update id="updateCodeMapping" parameterType="com.ga.core.vo.receive.CodeMappingVO">
        UPDATE code_mapping SET
            source_name = #{sourceName}, target_code = #{targetCode},
            target_name = #{targetName}, is_active = #{isActive}
        WHERE code_id = #{codeId}
    </update>

    <delete id="deleteCodeMapping">DELETE FROM code_mapping WHERE code_id = #{codeId}</delete>

    <!-- raw -->
    <insert id="insertRaw" parameterType="com.ga.core.vo.receive.RawCommissionDataVO"
            useGeneratedKeys="true" keyProperty="rawId">
        INSERT INTO raw_commission_data (company_code, batch_id, settle_month, data_type,
                                         raw_content, parse_status, file_name, row_number, received_at)
        VALUES (#{companyCode}, #{batchId}, #{settleMonth}, #{dataType},
                #{rawContent}, COALESCE(#{parseStatus},'PENDING'),
                #{fileName}, #{rowNumber}, NOW())
    </insert>

    <insert id="insertRawBatch">
        INSERT INTO raw_commission_data (company_code, batch_id, settle_month, data_type,
                                         raw_content, parse_status, file_name, row_number, received_at)
        VALUES
        <foreach collection="list" item="r" separator=",">
            (#{r.companyCode}, #{r.batchId}, #{r.settleMonth}, #{r.dataType},
             #{r.rawContent}, COALESCE(#{r.parseStatus},'PENDING'),
             #{r.fileName}, #{r.rowNumber}, NOW())
        </foreach>
    </insert>

    <select id="selectRawList" resultMap="RawMap">
        SELECT * FROM raw_commission_data
        <where>
            <if test="companyCode != null and companyCode != ''">AND company_code = #{companyCode}</if>
            <if test="settleMonth != null and settleMonth != ''">AND settle_month = #{settleMonth}</if>
            <if test="parseStatus != null and parseStatus != ''">AND parse_status = #{parseStatus}</if>
        </where>
        ORDER BY received_at DESC
    </select>

    <update id="updateRawStatus">
        UPDATE raw_commission_data SET
            parse_status = #{parseStatus},
            mapped_ledger_id = #{mappedLedgerId},
            mapped_table = #{mappedTable},
            parsed_at = NOW()
        WHERE raw_id = #{rawId}
    </update>

    <!-- error -->
    <select id="selectErrors" resultMap="ErrorMap">
        SELECT * FROM parse_error_log
        <where>
            <if test="companyCode != null and companyCode != ''">AND company_code = #{companyCode}</if>
            <if test="resolveStatus != null and resolveStatus != ''">AND resolve_status = #{resolveStatus}</if>
        </where>
        ORDER BY created_at DESC
    </select>

    <insert id="insertError" parameterType="com.ga.core.vo.receive.ParseErrorLogVO"
            useGeneratedKeys="true" keyProperty="errorId">
        INSERT INTO parse_error_log (raw_id, company_code, error_type, error_field, error_value,
                                     error_message, resolve_status, created_at)
        VALUES (#{rawId}, #{companyCode}, #{errorType}, #{errorField}, #{errorValue},
                #{errorMessage}, COALESCE(#{resolveStatus},'OPEN'), NOW())
    </insert>

    <update id="resolveError">
        UPDATE parse_error_log SET
            resolve_status = #{status}, resolve_note = #{note},
            resolved_by = #{userId}, resolved_at = NOW()
        WHERE error_id = #{errorId}
    </update>

</mapper>
