<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.tasteby.mapper.ChannelMapper">

    <resultMap id="channelResultMap" type="com.tasteby.domain.Channel">
        <id property="id" column="id"/>
        <result property="channelId" column="channel_id"/>
        <result property="channelName" column="channel_name"/>
        <result property="titleFilter" column="title_filter"/>
        <result property="description" column="description"/>
        <result property="tags" column="tags"/>
        <result property="sortOrder" column="sort_order"/>
        <result property="videoCount" column="video_count"/>
        <result property="lastVideoAt" column="last_video_at"/>
    </resultMap>

    <select id="findAllActive" resultMap="channelResultMap">
        SELECT c.id, c.channel_id, c.channel_name, c.title_filter, c.description, c.tags, c.sort_order,
               (SELECT COUNT(*) FROM videos v WHERE v.channel_id = c.id) AS video_count,
               (SELECT MAX(v.published_at) FROM videos v WHERE v.channel_id = c.id) AS last_video_at
        FROM channels c
        WHERE c.is_active = 1
        ORDER BY c.sort_order, c.channel_name
    </select>

    <insert id="insert">
        INSERT INTO channels (id, channel_id, channel_name, title_filter)
        VALUES (#{id}, #{channelId}, #{channelName}, #{titleFilter})
    </insert>

    <update id="deactivateByChannelId">
        UPDATE channels SET is_active = 0
        WHERE channel_id = #{channelId} AND is_active = 1
    </update>

    <update id="deactivateById">
        UPDATE channels SET is_active = 0
        WHERE id = #{id} AND is_active = 1
    </update>

    <update id="updateChannel">
        UPDATE channels SET description = #{description}, tags = #{tags}, sort_order = #{sortOrder}
        WHERE id = #{id}
    </update>

    <select id="findByChannelId" resultMap="channelResultMap">
        SELECT id, channel_id, channel_name, title_filter
        FROM channels
        WHERE channel_id = #{channelId} AND is_active = 1
    </select>

</mapper>
