<?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.analytics.mapper.AnalyticsMapper">

  <resultMap id="statMap" type="com.zioinfo.cms.analytics.CmsContentStat">
    <id property="contentId" column="content_id"/>
    <result property="slug" column="slug"/>
    <result property="title" column="title"/>
    <result property="views" column="views"/>
    <result property="dwellSeconds" column="dwell_seconds"/>
    <result property="conversions" column="conversions"/>
    <result property="conversionRate" column="conversion_rate"/>
  </resultMap>

  <update id="recordEvent">
    INSERT INTO cms_content_stat (content_id, views, dwell_seconds, conversions, updated_at)
    VALUES (
      #{contentId},
      CASE WHEN #{eventType} = 'VIEW' THEN #{amount} ELSE 0 END,
      CASE WHEN #{eventType} = 'DWELL' THEN #{amount} ELSE 0 END,
      CASE WHEN #{eventType} = 'CONVERSION' THEN #{amount} ELSE 0 END,
      NOW()
    )
    ON CONFLICT (content_id) DO UPDATE SET
      views = cms_content_stat.views + (CASE WHEN #{eventType} = 'VIEW' THEN #{amount} ELSE 0 END),
      dwell_seconds = cms_content_stat.dwell_seconds + (CASE WHEN #{eventType} = 'DWELL' THEN #{amount} ELSE 0 END),
      conversions = cms_content_stat.conversions + (CASE WHEN #{eventType} = 'CONVERSION' THEN #{amount} ELSE 0 END),
      updated_at = NOW()
  </update>

  <select id="topContent" resultMap="statMap">
    SELECT s.content_id, c.slug, c.title, s.views, s.dwell_seconds, s.conversions,
      CASE WHEN s.views = 0 THEN 0 ELSE ROUND(s.conversions::numeric / s.views * 100, 2) END AS conversion_rate
    FROM cms_content_stat s
    LEFT JOIN cms_content c ON c.id = s.content_id
    ORDER BY s.views DESC
    LIMIT #{limit}
  </select>

  <select id="findByContent" resultMap="statMap">
    SELECT s.content_id, c.slug, c.title, s.views, s.dwell_seconds, s.conversions,
      CASE WHEN s.views = 0 THEN 0 ELSE ROUND(s.conversions::numeric / s.views * 100, 2) END AS conversion_rate
    FROM cms_content_stat s
    LEFT JOIN cms_content c ON c.id = s.content_id
    WHERE s.content_id = #{contentId}
  </select>

  <select id="totalViews" resultType="long">SELECT COALESCE(SUM(views),0) FROM cms_content_stat</select>
  <select id="totalConversions" resultType="long">SELECT COALESCE(SUM(conversions),0) FROM cms_content_stat</select>

</mapper>
