<?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.media.mapper.MediaMapper">

  <resultMap id="mediaMap" type="com.zioinfo.cms.media.CmsMedia">
    <id property="id" column="id"/>
    <result property="fileName" column="file_name"/>
    <result property="mediaType" column="media_type"/>
    <result property="url" column="url"/>
    <result property="folder" column="folder"/>
    <result property="mimeType" column="mime_type"/>
    <result property="sizeBytes" column="size_bytes"/>
    <result property="altText" column="alt_text"/>
    <result property="tags" column="tags"/>
    <result property="refCount" column="ref_count"/>
    <result property="createdBy" column="created_by"/>
    <result property="createdAt" column="created_at"/>
  </resultMap>

  <select id="findAll" resultMap="mediaMap">
    SELECT * FROM cms_media
    <where>
      <if test="folder != null and folder != ''">AND folder = #{folder}</if>
      <if test="mediaType != null and mediaType != ''">AND media_type = #{mediaType}</if>
      <if test="keyword != null and keyword != ''">AND (file_name ILIKE '%'||#{keyword}||'%' OR tags ILIKE '%'||#{keyword}||'%')</if>
    </where>
    ORDER BY id DESC
  </select>

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

  <insert id="insert" parameterType="com.zioinfo.cms.media.CmsMedia" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO cms_media (file_name, media_type, url, folder, mime_type, size_bytes, alt_text, tags, ref_count, created_by)
    VALUES (#{fileName}, #{mediaType}, #{url}, #{folder}, #{mimeType}, #{sizeBytes}, #{altText}, #{tags}, #{refCount}, #{createdBy})
  </insert>

  <update id="updateMeta">
    UPDATE cms_media SET alt_text = #{altText}, tags = #{tags} WHERE id = #{id}
  </update>

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

</mapper>
