<?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.accounting.AccountingCloseMapper">

    <resultMap id="RespMap" type="com.ga.core.vo.accounting.AccountingCloseResp"/>
    <resultMap id="SnapMap" type="com.ga.core.vo.accounting.AccountBalanceSnapshotVO"/>

    <sql id="joinCols">
        ac.close_id,
        ac.close_period,
        ac.close_type,
        (SELECT cc.code_name FROM common_code cc
         WHERE cc.group_code = 'ACC_CLOSE_TYPE' AND cc.code = ac.close_type) AS close_type_name,
        ac.close_status,
        (SELECT cc.code_name FROM common_code cc
         WHERE cc.group_code = 'ACC_CLOSE_STATUS' AND cc.code = ac.close_status) AS close_status_name,
        ac.journal_prefix,
        ac.journal_count,
        ac.total_debit,
        ac.total_credit,
        (ac.total_debit - ac.total_credit) AS balance_diff,
        ac.closed_at,
        ac.closed_by,
        uc.user_name AS closed_by_name,
        ac.reopened_at,
        ac.reopened_by,
        ur.user_name AS reopened_by_name,
        ac.reopen_reason,
        ac.created_at,
        ac.updated_at
    </sql>

    <sql id="joinFrom">
        FROM accounting_close ac
        LEFT JOIN users uc ON uc.user_id = ac.closed_by
        LEFT JOIN users ur ON ur.user_id = ac.reopened_by
    </sql>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        <include refid="joinFrom"/>
        <where>
            <if test="closePeriod != null and closePeriod != ''">AND ac.close_period = #{closePeriod}</if>
            <if test="closeType   != null and closeType   != ''">AND ac.close_type   = #{closeType}</if>
            <if test="closeStatus != null and closeStatus != ''">AND ac.close_status = #{closeStatus}</if>
        </where>
        ORDER BY ac.close_period DESC, ac.close_type
    </select>

    <select id="selectDetailById" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        <include refid="joinFrom"/>
        WHERE ac.close_id = #{closeId}
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="closeId">
        INSERT INTO accounting_close
            (close_period, close_type, close_status, journal_prefix,
             journal_count, total_debit, total_credit,
             created_at, created_by, updated_at)
        VALUES
            (#{closePeriod}, #{closeType}, COALESCE(#{closeStatus}, 'DRAFT'), #{journalPrefix},
             0, 0, 0,
             now(), #{createdBy}, now())
    </insert>

    <update id="updateClose">
        UPDATE accounting_close
           SET close_status   = 'CLOSED',
               closed_at      = now(),
               closed_by      = #{closedBy},
               journal_count  = #{journalCount},
               total_debit    = #{totalDebit},
               total_credit   = #{totalCredit},
               updated_at     = now(),
               updated_by     = #{closedBy}
         WHERE close_id = #{closeId}
           AND close_status IN ('DRAFT','REOPENED')
    </update>

    <update id="updateReopen">
        UPDATE accounting_close
           SET close_status  = 'REOPENED',
               reopened_at   = now(),
               reopened_by   = #{reopenedBy},
               reopen_reason = #{reopenReason},
               updated_at    = now(),
               updated_by    = #{reopenedBy}
         WHERE close_id = #{closeId}
           AND close_status = 'CLOSED'
    </update>

    <!--
        마감 대상 미전기 분개 ID 목록.
        - MONTHLY:   YYYY-MM   → entry_date를 to_char(YYYY-MM)으로 비교
        - QUARTERLY: YYYY-Qn   → EXTRACT(QUARTER) 비교
        - YEARLY:    YYYY      → EXTRACT(YEAR)    비교
    -->
    <select id="selectUnpostedEntryIds" resultType="java.lang.Long">
        SELECT cae.entry_id
          FROM contract_accounting_entry cae
         WHERE cae.journal_no IS NULL
        <choose>
            <when test="closeType == 'MONTHLY'">
                AND to_char(cae.entry_date, 'YYYY-MM') = #{closePeriod}
            </when>
            <when test="closeType == 'QUARTERLY'">
                AND EXTRACT(YEAR    FROM cae.entry_date) = CAST(SPLIT_PART(#{closePeriod}, '-Q', 1) AS INT)
                AND EXTRACT(QUARTER FROM cae.entry_date) = CAST(SPLIT_PART(#{closePeriod}, '-Q', 2) AS INT)
            </when>
            <when test="closeType == 'YEARLY'">
                AND EXTRACT(YEAR FROM cae.entry_date) = CAST(#{closePeriod} AS INT)
            </when>
        </choose>
        ORDER BY cae.entry_date, cae.entry_id
    </select>

    <!--
        결산 시점 계정과목별 차변/대변 합계 집계.
        journal_prefix로 시작하는 journal_no 분개를 account_code 별로 SUM.
        debit_account / credit_account를 별도 SELECT로 합산 후 FULL OUTER JOIN.
    -->
    <select id="selectBalanceAggregate" resultMap="SnapMap">
        WITH d AS (
            SELECT cae.debit_account AS account_code, SUM(cae.amount) AS debit_total
              FROM contract_accounting_entry cae
             WHERE cae.journal_no LIKE CONCAT(#{journalPrefix}, '-%')
             GROUP BY cae.debit_account
        ), c AS (
            SELECT cae.credit_account AS account_code, SUM(cae.amount) AS credit_total
              FROM contract_accounting_entry cae
             WHERE cae.journal_no LIKE CONCAT(#{journalPrefix}, '-%')
             GROUP BY cae.credit_account
        )
        SELECT COALESCE(d.account_code, c.account_code) AS account_code,
               ac.account_name,
               ac.account_type,
               COALESCE(d.debit_total,  0) AS debit_total,
               COALESCE(c.credit_total, 0) AS credit_total,
               COALESCE(d.debit_total, 0) - COALESCE(c.credit_total, 0) AS balance
          FROM d
          FULL OUTER JOIN c ON c.account_code = d.account_code
          LEFT JOIN account_code ac ON ac.account_code = COALESCE(d.account_code, c.account_code)
         ORDER BY 1
    </select>

    <insert id="insertBalanceSnapshot">
        INSERT INTO account_balance_snapshot
            (close_id, account_code, account_name, account_type,
             debit_total, credit_total, balance)
        VALUES
        <foreach collection="list" item="s" separator=",">
            (#{s.closeId}, #{s.accountCode}, #{s.accountName}, #{s.accountType},
             #{s.debitTotal}, #{s.creditTotal}, #{s.balance})
        </foreach>
    </insert>

    <select id="selectSnapshotByCloseId" resultMap="SnapMap">
        SELECT snapshot_id, close_id, account_code, account_name, account_type,
               debit_total, credit_total, balance, created_at
          FROM account_balance_snapshot
         WHERE close_id = #{closeId}
         ORDER BY account_code
    </select>

</mapper>
