package com.zioinfo.kintex.home;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

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

/**
 * SCR-HOME 집계 매퍼 — 읽기 전용. 타 도메인 테이블(approval·required_document·auction·invoice·event·hall·
 * visitor_registration·event_member)을 직접 SELECT 하며, 타 패키지의 매퍼/서비스는 수정하지 않는다.
 *
 * <p><b>역할 자기선별</b>: 각 할 일/요약 서브쿼리는 {@code event_member} 멤버십 EXISTS 로 스스로 스코프된다.
 * 홀매니저·관리자(감독 권한, {@code broad=true})는 멤버십 게이트를 우회해 테넌트 전체를 본다.
 *
 * <p><b>테넌트</b>: {@code TenantContext.currentTenantId()}(대문자 'KINTEX', V31 정규화)로 {@code event.tenant_id}
 * 필터. 파라미터가 아닌 컨텍스트를 신뢰 원천으로 스코프한다(§8-2).
 *
 * <p><b>파싱 규칙</b>: 본 매퍼는 전부 <b>정적 @Select</b>(동적 {@code <script>} 미사용)이므로 비교연산 {@code <=,>}
 * 를 그대로 사용해도 XML 파싱 대상이 아니다(MapperAnnotationParseTest 통과). Map 반환 카멜케이스 별칭은
 * PostgreSQL 소문자 폴딩 회피를 위해 {@code AS "camelCase"} 쌍따옴표를 강제한다.
 */
@Mapper
public interface HomeMapper {

    // ── 내 할 일(todos) — 원천별 개별 조회. 서비스에서 병합·정렬·상위 6건 ───────────────

    /** 결재 대기: 내가 현재 단계 결재자(line_status='CURRENT')인 진행 중 문서. dday 없음(후순위 정렬). */
    @Select("""
            SELECT 'approval' AS "type",
                   '결재 대기' AS "title",
                   a.title AS "target",
                   NULL AS "dueDate",
                   NULL AS "dday",
                   '/work/approval' AS "route"
            FROM approval_line al
            JOIN approval a ON a.id = al.approval_id
            WHERE al.approver_id = #{userId}
              AND al.line_status = 'CURRENT'
              AND a.status IN ('SUBMITTED', 'IN_PROGRESS')
            ORDER BY a.submitted_at ASC NULLS LAST
            LIMIT 10
            """)
    List<Map<String, Object>> findApprovalTodos(@Param("userId") String userId);

    /** 서류 마감 임박(M6): 승인 전 필수 서류 중 마감 {@code horizon}(D-7) 이내. 내가 참여한 행사(또는 broad). */
    @Select("""
            SELECT 'document' AS "type",
                   '신고서류 제출' AS "title",
                   rd.name AS "target",
                   to_char(rd.due_date, 'YYYY-MM-DD') AS "dueDate",
                   (rd.due_date - CURRENT_DATE) AS "dday",
                   '/docs' AS "route"
            FROM required_document rd
            JOIN event e ON e.id = rd.event_id
            WHERE e.tenant_id = #{tenantId}
              AND rd.status NOT IN ('approved')
              AND rd.due_date IS NOT NULL
              AND rd.due_date <= CAST(#{horizon} AS date)
              AND (#{broad} OR EXISTS (
                    SELECT 1 FROM event_member m
                    WHERE m.event_id = e.id AND m.user_id = #{userId}))
            ORDER BY rd.due_date ASC
            LIMIT 10
            """)
    List<Map<String, Object>> findDocumentTodos(@Param("userId") String userId,
                                                @Param("tenantId") String tenantId,
                                                @Param("horizon") String horizon,
                                                @Param("broad") boolean broad);

    /** 옥션 낙찰 선정 대기: 마감 경과 + 낙찰(award) 미확정. 내가 주최한 행사(또는 broad). */
    @Select("""
            SELECT 'auction' AS "type",
                   '옥션 낙찰 선정' AS "title",
                   a.title AS "target",
                   to_char(a.deadline, 'YYYY-MM-DD') AS "dueDate",
                   (a.deadline::date - CURRENT_DATE) AS "dday",
                   ('/auctions/' || a.id || '/award') AS "route"
            FROM auction a
            JOIN event e ON e.id = a.event_id
            WHERE e.tenant_id = #{tenantId}
              AND CURRENT_TIMESTAMP > a.deadline
              AND NOT EXISTS (SELECT 1 FROM award aw WHERE aw.auction_id = a.id)
              AND (#{broad} OR EXISTS (
                    SELECT 1 FROM event_member m
                    WHERE m.event_id = e.id AND m.user_id = #{userId} AND m.role_code = 'ORGANIZER'))
            ORDER BY a.deadline ASC
            LIMIT 10
            """)
    List<Map<String, Object>> findAuctionAwardTodos(@Param("userId") String userId,
                                                    @Param("tenantId") String tenantId,
                                                    @Param("broad") boolean broad);

    /** 응찰 마감 임박: 마감 D-3 이내 진행 중 옥션 중, 내 소속 등록업체(CONTRACTOR)가 초대된 건. */
    @Select("""
            SELECT 'auction' AS "type",
                   '응찰 마감 임박' AS "title",
                   a.title AS "target",
                   to_char(a.deadline, 'YYYY-MM-DD') AS "dueDate",
                   (a.deadline::date - CURRENT_DATE) AS "dday",
                   ('/auctions/' || a.id) AS "route"
            FROM auction a
            JOIN event e ON e.id = a.event_id
            WHERE e.tenant_id = #{tenantId}
              AND a.deadline > CURRENT_TIMESTAMP
              AND a.deadline <= CURRENT_TIMESTAMP + INTERVAL '3 day'
              AND EXISTS (
                    SELECT 1 FROM auction_invite ai
                    JOIN event_member m ON m.company_id = ai.company_id
                    WHERE ai.auction_id = a.id
                      AND m.user_id = #{userId} AND m.role_code = 'CONTRACTOR')
            ORDER BY a.deadline ASC
            LIMIT 10
            """)
    List<Map<String, Object>> findAuctionBidTodos(@Param("userId") String userId,
                                                  @Param("tenantId") String tenantId);

    /** 미수 청구(M9): 미발행/연체 청구서. 내가 주최한 행사(또는 broad). exhibitor_name 은 표시용(민감정보 아님). */
    @Select("""
            SELECT 'settlement' AS "type",
                   '미수 청구' AS "title",
                   i.exhibitor_name AS "target",
                   to_char(i.due_date, 'YYYY-MM-DD') AS "dueDate",
                   (i.due_date - CURRENT_DATE) AS "dday",
                   '/settlement' AS "route"
            FROM invoice i
            JOIN event e ON e.id = i.event_id
            WHERE e.tenant_id = #{tenantId}
              AND i.status IN ('pending', 'overdue')
              AND (#{broad} OR EXISTS (
                    SELECT 1 FROM event_member m
                    WHERE m.event_id = e.id AND m.user_id = #{userId} AND m.role_code = 'ORGANIZER'))
            ORDER BY i.due_date ASC NULLS LAST
            LIMIT 10
            """)
    List<Map<String, Object>> findSettlementTodos(@Param("userId") String userId,
                                                  @Param("tenantId") String tenantId,
                                                  @Param("broad") boolean broad);

    /**
     * 마일스톤 준비(M6): 미완료 표준 일정 노드 중 마감 {@code horizon}(D-7) 이내. 내가 참여한 행사(또는 broad).
     * target 은 "행사명 · 노드라벨"(민감정보 아님). route 는 서류·마일스톤 화면(SCR-22/23).
     */
    @Select("""
            SELECT 'milestone' AS "type",
                   '마일스톤 준비' AS "title",
                   (e.name || ' · ' || em.label) AS "target",
                   to_char(em.due_date, 'YYYY-MM-DD') AS "dueDate",
                   (em.due_date - CURRENT_DATE) AS "dday",
                   '/docs' AS "route"
            FROM event_milestone em
            JOIN event e ON e.id = em.event_id
            WHERE e.tenant_id = #{tenantId}
              AND em.state <> 'done'
              AND em.due_date IS NOT NULL
              AND em.due_date <= CAST(#{horizon} AS date)
              AND (#{broad} OR EXISTS (
                    SELECT 1 FROM event_member m
                    WHERE m.event_id = e.id AND m.user_id = #{userId}))
            ORDER BY em.due_date ASC
            LIMIT 10
            """)
    List<Map<String, Object>> findMilestoneTodos(@Param("userId") String userId,
                                                 @Param("tenantId") String tenantId,
                                                 @Param("horizon") String horizon,
                                                 @Param("broad") boolean broad);

    /**
     * 체크인 준비(M10): 개장 D-3 이내(오늘~+3) 이면서 배지 미발급 관람객이 남은 행사. 내가 주최(또는 broad).
     * target 은 "행사명 · 대기 N명". dday 는 개장까지 잔여(0~3). route 는 관람객 관리 화면.
     */
    @Select("""
            SELECT 'checkin' AS "type",
                   '체크인 준비' AS "title",
                   (e.name || ' · 대기 ' || w.cnt || '명') AS "target",
                   to_char(e.start_date, 'YYYY-MM-DD') AS "dueDate",
                   (e.start_date - CURRENT_DATE) AS "dday",
                   '/visitors' AS "route"
            FROM event e
            JOIN LATERAL (
                SELECT count(*) AS cnt FROM visitor_registration v
                WHERE v.event_id = e.id AND v.badge_issued = false
            ) w ON true
            WHERE e.tenant_id = #{tenantId}
              AND e.start_date >= CURRENT_DATE
              AND e.start_date <= CURRENT_DATE + 3
              AND w.cnt > 0
              AND (#{broad} OR EXISTS (
                    SELECT 1 FROM event_member m
                    WHERE m.event_id = e.id AND m.user_id = #{userId} AND m.role_code = 'ORGANIZER'))
            ORDER BY e.start_date ASC
            LIMIT 10
            """)
    List<Map<String, Object>> findCheckinPrepTodos(@Param("userId") String userId,
                                                   @Param("tenantId") String tenantId,
                                                   @Param("broad") boolean broad);

    /**
     * 핫리드 팔로업(M10): 고득점(score≥80) 리드로 팔로업이 필요한 건. 내가 참가업체(EXHIBITOR)인 행사(또는 broad).
     * 종료 후 7일 지난 행사는 제외(경과 리드 정리). 기한 없음(dday=null → 서비스가 warning 으로 부각).
     * ★PII 금지: name/phone/email 은 SELECT 목록에서 완전 제외 — company/product/score(민감정보 아님)만 노출.
     */
    @Select("""
            SELECT 'lead' AS "type",
                   '핫리드 팔로업' AS "title",
                   (COALESCE(NULLIF(l.company, ''), '리드') || COALESCE(' · ' || l.product, '')) AS "target",
                   NULL AS "dueDate",
                   NULL AS "dday",
                   '/leads' AS "route"
            FROM lead l
            JOIN event e ON e.id = l.event_id
            WHERE e.tenant_id = #{tenantId}
              AND l.score >= 80
              AND e.end_date >= CURRENT_DATE - 7
              AND (#{broad} OR EXISTS (
                    SELECT 1 FROM event_member m
                    WHERE m.event_id = e.id AND m.user_id = #{userId} AND m.role_code = 'EXHIBITOR'))
            ORDER BY l.score DESC
            LIMIT 10
            """)
    List<Map<String, Object>> findLeadFollowupTodos(@Param("userId") String userId,
                                                    @Param("tenantId") String tenantId,
                                                    @Param("broad") boolean broad);

    // ── 쇼케이스(진행 중·다가오는 전시) ────────────────────────────────────────────

    /**
     * 탭별 전시 8~12건. tab: ongoing(오늘 포함 기간) | thisMonth(당월 개장) | nextMonth(익월 개장).
     * hallLabel 은 hall_assignment(대표 홀) 우선, 없으면 V23 {@code event.hall_label} 폴백.
     * posterUrl 은 V32 크롤 적재 컬럼(NULL 이면 프론트가 그라디언트+행사명 폴백 카드 렌더).
     * status·dday 는 날짜 기준 파생. 정적 @Select 이므로 비교연산 그대로 사용.
     */
    @Select("""
            SELECT e.id AS "id",
                   e.name AS "name",
                   to_char(e.start_date, 'YYYY-MM-DD') AS "startDate",
                   to_char(e.end_date,   'YYYY-MM-DD') AS "endDate",
                   COALESCE(h.label, e.hall_label) AS "hallLabel",
                   e.poster_url AS "posterUrl",
                   (e.start_date - CURRENT_DATE) AS "dday",
                   CASE WHEN e.start_date <= CURRENT_DATE AND e.end_date >= CURRENT_DATE THEN 'ongoing'
                        WHEN e.start_date > CURRENT_DATE THEN 'upcoming'
                        ELSE 'ended' END AS "status"
            FROM event e
            LEFT JOIN LATERAL (
                SELECT ha.hall_id FROM hall_assignment ha
                WHERE ha.event_id = e.id
                ORDER BY ha.is_primary DESC
                LIMIT 1
            ) pa ON true
            LEFT JOIN hall h ON h.id = pa.hall_id
            WHERE e.tenant_id = #{tenantId}
              AND e.start_date IS NOT NULL AND e.end_date IS NOT NULL
              AND (
                (#{tab} = 'ongoing'
                   AND e.start_date <= CURRENT_DATE AND e.end_date >= CURRENT_DATE)
                OR (#{tab} = 'thisMonth'
                   AND e.start_date >= date_trunc('month', CURRENT_DATE)::date
                   AND e.start_date <  (date_trunc('month', CURRENT_DATE) + INTERVAL '1 month')::date)
                OR (#{tab} = 'nextMonth'
                   AND e.start_date >= (date_trunc('month', CURRENT_DATE) + INTERVAL '1 month')::date
                   AND e.start_date <  (date_trunc('month', CURRENT_DATE) + INTERVAL '2 month')::date)
              )
            ORDER BY e.start_date ASC, e.id ASC
            LIMIT 12
            """)
    List<Map<String, Object>> findShowcase(@Param("tenantId") String tenantId,
                                           @Param("tab") String tab);

    // ── 전시장(센터) 동적 목록 ────────────────────────────────────────────────────

    /** 홀 마스터의 전시장 구분(exhibition_center) distinct·오름차순. 라벨 조립은 서비스에서 1곳. */
    @Select("""
            SELECT DISTINCT exhibition_center AS "center"
            FROM hall
            WHERE tenant_id = #{tenantId} AND exhibition_center IS NOT NULL
            ORDER BY exhibition_center
            """)
    List<Map<String, Object>> findHallCenters(@Param("tenantId") String tenantId);

    // ── 역할별 요약 KPI(단일 행 집계) ─────────────────────────────────────────────

    /**
     * 요약 KPI 원자 집계(단일 행). 사용자·테넌트·감독여부(broad)로 스코프. 정적 @Select.
     * 각 서브쿼리는 원천 부재/0건이어도 안전(count=0 · min=NULL).
     */
    @Select("""
            SELECT
              (SELECT count(*) FROM event e
                 WHERE e.tenant_id = #{tenantId}
                   AND e.start_date <= CURRENT_DATE AND e.end_date >= CURRENT_DATE) AS "ongoingEvents",
              (SELECT count(*) FROM visitor_registration v JOIN event e ON e.id = v.event_id
                 WHERE e.tenant_id = #{tenantId}
                   AND v.checkin_at IS NOT NULL AND v.checkin_at::date = CURRENT_DATE) AS "todayCheckins",
              (SELECT min(e.start_date - CURRENT_DATE) FROM event e
                 WHERE e.tenant_id = #{tenantId} AND e.start_date >= CURRENT_DATE) AS "nearestOpeningDday",
              (SELECT count(*) FROM approval_line al JOIN approval a ON a.id = al.approval_id
                 WHERE al.approver_id = #{userId} AND al.line_status = 'CURRENT'
                   AND a.status IN ('SUBMITTED', 'IN_PROGRESS')) AS "pendingApprovals",
              (SELECT count(*) FROM required_document rd JOIN event e ON e.id = rd.event_id
                 WHERE e.tenant_id = #{tenantId} AND rd.status NOT IN ('approved')
                   AND (#{broad} OR EXISTS (SELECT 1 FROM event_member m
                        WHERE m.event_id = e.id AND m.user_id = #{userId}))) AS "docsToSubmit",
              (SELECT count(*) FROM invoice i JOIN event e ON e.id = i.event_id
                 WHERE e.tenant_id = #{tenantId} AND i.status IN ('pending', 'overdue')
                   AND (#{broad} OR EXISTS (SELECT 1 FROM event_member m
                        WHERE m.event_id = e.id AND m.user_id = #{userId} AND m.role_code = 'ORGANIZER'))) AS "unpaidInvoices",
              (SELECT count(*) FROM auction a JOIN event e ON e.id = a.event_id
                 WHERE e.tenant_id = #{tenantId} AND a.deadline > CURRENT_TIMESTAMP
                   AND EXISTS (SELECT 1 FROM auction_invite ai JOIN event_member m ON m.company_id = ai.company_id
                        WHERE ai.auction_id = a.id AND m.user_id = #{userId} AND m.role_code = 'CONTRACTOR')) AS "activeAuctions",
              (SELECT count(*) FROM award aw JOIN bid b ON b.id = aw.bid_id
                 WHERE EXISTS (SELECT 1 FROM event_member m
                        WHERE m.company_id = b.company_id AND m.user_id = #{userId} AND m.role_code = 'CONTRACTOR')) AS "awardedBooths"
            """)
    Map<String, Object> findSummaryCounts(@Param("userId") String userId,
                                          @Param("tenantId") String tenantId,
                                          @Param("broad") boolean broad);

    /** 사용자의 행사 역할 코드 목록(중복 제거) — 주 역할 판정용. */
    @Select("""
            SELECT DISTINCT role_code AS "roleCode"
            FROM event_member
            WHERE user_id = #{userId}
            """)
    List<String> findUserEventRoles(@Param("userId") String userId);
}
