<?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.product.mapper.ProductContentMapper">

  <resultMap id="pcMap" type="com.zioinfo.cms.product.CmsProductContent">
    <id property="id" column="id"/>
    <result property="mallProductId" column="mall_product_id"/>
    <result property="productName" column="product_name"/>
    <result property="summary" column="summary"/>
    <result property="detailBlocks" column="detail_blocks"/>
    <result property="specs" column="specs"/>
    <result property="locale" column="locale"/>
    <result property="status" column="status"/>
    <result property="createdBy" column="created_by"/>
    <result property="publishedAt" column="published_at"/>
    <result property="createdAt" column="created_at"/>
    <result property="updatedAt" column="updated_at"/>
  </resultMap>

  <select id="findAll" resultMap="pcMap">
    SELECT * FROM cms_product_content
    <where>
      <if test="status != null and status != ''">AND status = #{status}</if>
      <if test="keyword != null and keyword != ''">AND product_name ILIKE '%'||#{keyword}||'%'</if>
    </where>
    ORDER BY id DESC
  </select>

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

  <select id="findPublishedByMallId" resultMap="pcMap">
    SELECT * FROM cms_product_content
    WHERE mall_product_id = #{mallProductId} AND status = 'PUBLISHED'
    <if test="locale != null and locale != ''">AND locale = #{locale}</if>
    ORDER BY published_at DESC NULLS LAST LIMIT 1
  </select>

  <insert id="insert" parameterType="com.zioinfo.cms.product.CmsProductContent" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO cms_product_content (mall_product_id, product_name, summary, detail_blocks, specs, locale, status, created_by)
    VALUES (#{mallProductId}, #{productName}, #{summary}, #{detailBlocks}::jsonb, #{specs}::jsonb, #{locale}, #{status}, #{createdBy})
  </insert>

  <update id="update" parameterType="com.zioinfo.cms.product.CmsProductContent">
    UPDATE cms_product_content SET mall_product_id = #{mallProductId}, product_name = #{productName}, summary = #{summary},
      detail_blocks = #{detailBlocks}::jsonb, specs = #{specs}::jsonb, locale = #{locale}, updated_at = NOW()
    WHERE id = #{id}
  </update>

  <update id="updateStatus">
    UPDATE cms_product_content SET status = #{status},
      published_at = CASE WHEN #{status} = 'PUBLISHED' THEN NOW() ELSE published_at END, updated_at = NOW()
    WHERE id = #{id}
  </update>

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

</mapper>
