<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zioinfo.cms.content.mapper.ContentMapper">

  <resultMap id="contentMap" type="com.zioinfo.cms.content.CmsContent">
    <id property="id" column="id"/>
    <result property="contentType" column="content_type"/>
    <result property="slug" column="slug"/>
    <result property="title" column="title"/>
    <result property="summary" column="summary"/>
    <result property="blocks" column="blocks"/>
    <result property="locale" column="locale"/>
    <result property="status" column="status"/>
    <result property="version" column="version"/>
    <result property="menuId" column="menu_id"/>
    <result property="tags" column="tags"/>
    <result property="seoMeta" column="seo_meta"/>
    <result property="scheduledAt" column="scheduled_at"/>
    <result property="publishedAt" column="published_at"/>
    <result property="createdBy" column="created_by"/>
    <result property="updatedBy" column="updated_by"/>
    <result property="createdAt" column="created_at"/>
    <result property="updatedAt" column="updated_at"/>
  </resultMap>

  <select id="findAll" resultMap="contentMap">
    SELECT * FROM cms_content
    <where>
      <if test="contentType != null and contentType != ''">AND content_type = #{contentType}</if>
      <if test="status != null and status != ''">AND status = #{status}</if>
      <if test="locale != null and locale != ''">AND locale = #{locale}</if>
      <if test="keyword != null and keyword != ''">
        AND (title ILIKE '%' || #{keyword} || '%' OR slug ILIKE '%' || #{keyword} || '%')
      </if>
    </where>
    ORDER BY id DESC
  </select>

  <select id="findById" resultMap="contentMap">
    SELECT * FROM cms_content WHERE id = #{id}
  </select>

  <select id="findPublishedBySlug" resultMap="contentMap">
    SELECT * FROM cms_content
    WHERE slug = #{slug} AND status = 'PUBLISHED'
    <if test="locale != null and locale != ''">AND locale = #{locale}</if>
    ORDER BY published_at DESC NULLS LAST LIMIT 1
  </select>

  <select id="countBySlug" resultType="int">
    SELECT COUNT(*) FROM cms_content
    WHERE slug = #{slug}
    <if test="locale != null and locale != ''">AND locale = #{locale}</if>
    <if test="excludeId != null">AND id != #{excludeId}</if>
  </select>

  <insert id="insert" parameterType="com.zioinfo.cms.content.CmsContent"
          useGeneratedKeys="true" keyProperty="id">
    INSERT INTO cms_content
      (content_type, slug, title, summary, blocks, locale, status, version, menu_id, tags, seo_meta,
       scheduled_at, created_by, updated_by)
    VALUES
      (#{contentType}, #{slug}, #{title}, #{summary}, #{blocks}::jsonb, #{locale}, #{status}, #{version},
       #{menuId}, #{tags}, #{seoMeta}::jsonb, #{scheduledAt}, #{createdBy}, #{updatedBy})
  </insert>

  <update id="update" parameterType="com.zioinfo.cms.content.CmsContent">
    UPDATE cms_content SET
      content_type = #{contentType}, slug = #{slug}, title = #{title}, summary = #{summary},
      blocks = #{blocks}::jsonb, locale = #{locale}, status = #{status}, version = #{version},
      menu_id = #{menuId}, tags = #{tags}, seo_meta = #{seoMeta}::jsonb,
      scheduled_at = #{scheduledAt}, updated_by = #{updatedBy}, updated_at = NOW()
    WHERE id = #{id}
  </update>

  <update id="updateStatus">
    UPDATE cms_content SET status = #{status}, updated_by = #{updatedBy}, updated_at = NOW() WHERE id = #{id}
  </update>

  <update id="updatePublished">
    UPDATE cms_content SET status = #{status}, published_at = NOW(), scheduled_at = NULL, updated_at = NOW()
    WHERE id = #{id}
  </update>

  <delete id="delete">
    DELETE FROM cms_content WHERE id = #{id}
  </delete>

  <select id="findDueScheduled" resultMap="contentMap">
    SELECT * FROM cms_content
    WHERE status = 'APPROVED' AND scheduled_at IS NOT NULL AND scheduled_at &lt;= NOW()
  </select>

  <!-- ── 버전 ── -->
  <insert id="insertVersion" parameterType="com.zioinfo.cms.content.CmsContentVersion"
          useGeneratedKeys="true" keyProperty="id">
    INSERT INTO cms_content_version (content_id, version, title, summary, blocks, status, note, created_by)
    VALUES (#{contentId}, #{version}, #{title}, #{summary}, #{blocks}::jsonb, #{status}, #{note}, #{createdBy})
  </insert>

  <select id="findVersions" resultMap="versionMap">
    SELECT * FROM cms_content_version WHERE content_id = #{contentId} ORDER BY version DESC
  </select>

  <select id="findVersion" resultMap="versionMap">
    SELECT * FROM cms_content_version WHERE content_id = #{contentId} AND version = #{version} LIMIT 1
  </select>

  <resultMap id="versionMap" type="com.zioinfo.cms.content.CmsContentVersion">
    <id property="id" column="id"/>
    <result property="contentId" column="content_id"/>
    <result property="version" column="version"/>
    <result property="title" column="title"/>
    <result property="summary" column="summary"/>
    <result property="blocks" column="blocks"/>
    <result property="status" column="status"/>
    <result property="note" column="note"/>
    <result property="createdBy" column="created_by"/>
    <result property="createdAt" column="created_at"/>
  </resultMap>

</mapper>
