package com.zioinfo.kintex.work.notice;

import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/** 공지 매퍼. */
@Mapper
public interface NoticeMapper {

    @Select("""
            <script>
            SELECT id, event_id AS "eventId", category, title, content, pinned,
                   author_id AS "authorId", author_name AS "authorName", view_count AS "viewCount",
                   to_char(published_at AT TIME ZONE 'UTC','YYYY-MM-DD"T"HH24:MI:SS"Z"') AS "publishedAt",
                   to_char(created_at AT TIME ZONE 'UTC','YYYY-MM-DD"T"HH24:MI:SS"Z"') AS "createdAt"
            FROM notice
            <where>
              <if test="eventId != null and eventId != ''">AND event_id = #{eventId}</if>
              <if test="category != null and category != ''">AND category = #{category}</if>
              <if test="keyword != null and keyword != ''">AND title ILIKE '%'||#{keyword}||'%'</if>
            </where>
            ORDER BY pinned DESC, created_at DESC LIMIT #{size} OFFSET #{offset}
            </script>
            """)
    List<Map<String, Object>> search(Map<String, Object> q);

    @Select("""
            <script>
            SELECT count(*) FROM notice
            <where>
              <if test="eventId != null and eventId != ''">AND event_id = #{eventId}</if>
              <if test="category != null and category != ''">AND category = #{category}</if>
              <if test="keyword != null and keyword != ''">AND title ILIKE '%'||#{keyword}||'%'</if>
            </where>
            </script>
            """)
    long count(Map<String, Object> q);

    @Select("""
            SELECT id, event_id AS "eventId", category, title, content, pinned,
                   author_id AS "authorId", author_name AS "authorName", view_count AS "viewCount",
                   to_char(published_at AT TIME ZONE 'UTC','YYYY-MM-DD"T"HH24:MI:SS"Z"') AS "publishedAt",
                   to_char(created_at AT TIME ZONE 'UTC','YYYY-MM-DD"T"HH24:MI:SS"Z"') AS "createdAt"
            FROM notice WHERE id = #{id}
            """)
    Map<String, Object> findById(@Param("id") String id);

    @Update("UPDATE notice SET view_count = view_count + 1 WHERE id = #{id}")
    int incrementView(@Param("id") String id);

    @Insert("""
            INSERT INTO notice (id, event_id, category, title, content, pinned, author_id, author_name,
                                published_at)
            VALUES (#{id}, #{eventId}, COALESCE(#{category},'GENERAL'), #{title}, #{content},
                    COALESCE(#{pinned},false), #{authorId}, #{authorName}, now())
            """)
    int insert(Map<String, Object> p);

    @Update("""
            UPDATE notice SET category=#{category}, title=#{title}, content=#{content},
                   pinned=COALESCE(#{pinned},false), event_id=#{eventId}, updated_at=now()
            WHERE id = #{id}
            """)
    int update(Map<String, Object> p);

    @Delete("DELETE FROM notice WHERE id = #{id}")
    int delete(@Param("id") String id);
}
