<?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.form.mapper.FormMapper">

  <resultMap id="formMap" type="com.zioinfo.cms.form.CmsForm">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="slug" column="slug"/>
    <result property="fields" column="fields"/>
    <result property="active" column="is_active"/>
    <result property="createdBy" column="created_by"/>
    <result property="createdAt" column="created_at"/>
  </resultMap>

  <resultMap id="subMap" type="com.zioinfo.cms.form.CmsFormSubmission">
    <id property="id" column="id"/>
    <result property="formId" column="form_id"/>
    <result property="payload" column="payload"/>
    <result property="spam" column="is_spam"/>
    <result property="spamRisk" column="spam_risk"/>
    <result property="createdAt" column="created_at"/>
  </resultMap>

  <select id="findAll" resultMap="formMap">SELECT * FROM cms_form ORDER BY id DESC</select>
  <select id="findById" resultMap="formMap">SELECT * FROM cms_form WHERE id = #{id}</select>
  <select id="findBySlug" resultMap="formMap">SELECT * FROM cms_form WHERE slug = #{slug}</select>

  <insert id="insert" parameterType="com.zioinfo.cms.form.CmsForm" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO cms_form (name, slug, fields, is_active, created_by)
    VALUES (#{name}, #{slug}, #{fields}::jsonb, #{active}, #{createdBy})
  </insert>

  <update id="update" parameterType="com.zioinfo.cms.form.CmsForm">
    UPDATE cms_form SET name = #{name}, slug = #{slug}, fields = #{fields}::jsonb, is_active = #{active} WHERE id = #{id}
  </update>

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

  <insert id="insertSubmission" parameterType="com.zioinfo.cms.form.CmsFormSubmission" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO cms_form_submission (form_id, payload, is_spam, spam_risk)
    VALUES (#{formId}, #{payload}::jsonb, #{spam}, #{spamRisk})
  </insert>

  <select id="findSubmissions" resultMap="subMap">
    SELECT * FROM cms_form_submission
    WHERE form_id = #{formId}
    <if test="!includeSpam">AND is_spam = FALSE</if>
    ORDER BY id DESC LIMIT 500
  </select>

</mapper>
