package com.zioinfo.kintex.analytics;

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;

/**
 * 경영분석 BI 집계 매퍼 — booth(PostGIS ST_Area)·utility_order·master_data(요율) read-only.
 * 매출은 판매면적×임대 요율(원/㎡·12h)로 산출한다. 마이그레이션 불변.
 */
@Mapper
public interface AnalyticsMapper {

    /** 전시홀 임대 요율(원/㎡) — master_data RATE.exhibitionHallPerM2. 없으면 null. */
    @Select("""
            SELECT (value_json #>> '{}')::numeric
            FROM master_data WHERE category = 'RATE' AND code = 'exhibitionHallPerM2'
            """)
    Double findRentalRatePerM2();

    /** 행사 부스 총 판매면적(㎡)·부스 수. */
    @Select("""
            SELECT COALESCE(sum(ST_Area(b.geom)), 0) AS "salesArea",
                   count(*) AS "boothCount"
            FROM booth b JOIN layout l ON l.id = b.layout_id
            WHERE l.event_id = #{eventId}
            """)
    Map<String, Object> findAreaAndCount(@Param("eventId") String eventId);

    /** 월별 부스 생성 추이(period=YYYY-MM, 면적·부스 수). days&gt;0 이면 최근 N일로 제한. */
    @Select("""
            <script>
            SELECT to_char(b.created_at, 'YYYY-MM') AS period,
                   COALESCE(sum(ST_Area(b.geom)), 0) AS "salesArea",
                   count(*) AS booths
            FROM booth b JOIN layout l ON l.id = b.layout_id
            WHERE l.event_id = #{eventId}
            <if test="days > 0">AND b.created_at >= now() - make_interval(days => #{days})</if>
            GROUP BY to_char(b.created_at, 'YYYY-MM')
            ORDER BY period
            </script>
            """)
    List<Map<String, Object>> findMonthlyTrend(@Param("eventId") String eventId, @Param("days") int days);

    /** 유틸리티 매출 — quote.total 합계(숫자 형태만, 없으면 0). jsonb {@code ?} 연산자는 JDBC 파라미터와 충돌하므로 회피. */
    @Select("""
            SELECT COALESCE(sum((quote->>'total')::numeric), 0)
            FROM utility_order
            WHERE event_id = #{eventId} AND quote->>'total' ~ '^[0-9]+(\\.[0-9]+)?$'
            """)
    Double findUtilityRevenue(@Param("eventId") String eventId);

    // ── 테넌트 전역(행사 횡단) BI 집계 — M16 잔여 갭 ──────────────────────────
    //    ★멀티테넌시 2단계: 행사 횡단 집계는 tenant_id(event/hall) 선행 필터로 격리(§1A-2 · §8-2).
    //    booth/layout/utility_order는 tenant_id 컬럼이 없으므로 event 조인으로 간접 스코프(신규 컬럼 금지).

    /** 전체 전시홀 수(가동률 분모 산정용) — 테넌트 스코프. */
    @Select("SELECT count(*) FROM hall WHERE tenant_id = #{tenantId}")
    long countHalls(@Param("tenantId") String tenantId);

    /** 전체 개최 행사 수(기간 무관, KPI용) — 테넌트 스코프. */
    @Select("SELECT count(*) FROM event WHERE tenant_id = #{tenantId}")
    long countEvents(@Param("tenantId") String tenantId);

    /**
     * 월별 개최 행사-일수(booked hall-days 근사) — 최근 N개월. 테넌트 스코프.
     * 행사 기간(end-start+1, 최소 1일)을 시작월에 합산. 가동률은 서비스가 홀수·그달 일수로 환산.
     */
    @Select("""
            SELECT to_char(e.start_date, 'YYYY-MM') AS "period",
                   COALESCE(sum(GREATEST((e.end_date - e.start_date) + 1, 1)), 0) AS "bookedDays"
            FROM event e
            WHERE e.tenant_id = #{tenantId}
              AND e.start_date IS NOT NULL
              AND e.start_date >= (date_trunc('month', now()) - make_interval(months => #{months}))
            GROUP BY to_char(e.start_date, 'YYYY-MM')
            ORDER BY 1
            """)
    List<Map<String, Object>> findMonthlyEventDays(@Param("tenantId") String tenantId,
                                                   @Param("months") int months);

    /** 부스가 배치된 행사의 판매면적·부스 수 랭킹(매출 상위 N) — 테넌트 스코프(event 조인). */
    @Select("""
            SELECT l.event_id AS "eventId",
                   e.name     AS "eventName",
                   COALESCE(sum(ST_Area(b.geom)), 0) AS "areaM2",
                   count(b.id) AS "boothCount"
            FROM booth b
            JOIN layout l ON l.id = b.layout_id
            JOIN event  e ON e.id = l.event_id
            WHERE e.tenant_id = #{tenantId}
            GROUP BY l.event_id, e.name
            HAVING count(b.id) > 0
            ORDER BY sum(ST_Area(b.geom)) DESC
            LIMIT #{limit}
            """)
    List<Map<String, Object>> findEventAreaRanking(@Param("tenantId") String tenantId,
                                                   @Param("limit") int limit);

    /** 행사별 유틸리티 매출 합(매출 비교 조립용, N+1 회피) — 테넌트 스코프(event 조인). */
    @Select("""
            SELECT uo.event_id AS "eventId",
                   COALESCE(sum((uo.quote->>'total')::numeric), 0) AS "utility"
            FROM utility_order uo
            JOIN event e ON e.id = uo.event_id
            WHERE e.tenant_id = #{tenantId}
              AND uo.quote->>'total' ~ '^[0-9]+(\\.[0-9]+)?$'
            GROUP BY uo.event_id
            """)
    List<Map<String, Object>> findUtilityRevenueByEvent(@Param("tenantId") String tenantId);

    /**
     * 참가사 리텐션 요약 — 부스 배정사(booth.assigned_company_name) 기준 고유 참가사 수와
     * 2개 이상 행사에 참가한 재참가사 수. 테넌트 스코프(event 조인).
     */
    @Select("""
            SELECT COUNT(*) AS "totalCompanies",
                   COUNT(*) FILTER (WHERE ec > 1) AS "returningCompanies"
            FROM (
                SELECT lower(btrim(b.assigned_company_name)) AS company,
                       count(DISTINCT l.event_id) AS ec
                FROM booth b
                JOIN layout l ON l.id = b.layout_id
                JOIN event  e ON e.id = l.event_id
                WHERE e.tenant_id = #{tenantId}
                  AND b.assigned_company_name IS NOT NULL AND btrim(b.assigned_company_name) <> ''
                GROUP BY lower(btrim(b.assigned_company_name))
            ) t
            """)
    Map<String, Object> findRetentionSummary(@Param("tenantId") String tenantId);

    /** 연도별 리텐션 — 해당 연도 참가사 중 이전 연도에도 참가한 참가사 수. 테넌트 스코프. */
    @Select("""
            WITH ce AS (
                SELECT DISTINCT lower(btrim(b.assigned_company_name)) AS company,
                       EXTRACT(YEAR FROM e.start_date)::int AS yr
                FROM booth b
                JOIN layout l ON l.id = b.layout_id
                JOIN event  e ON e.id = l.event_id
                WHERE e.tenant_id = #{tenantId}
                  AND b.assigned_company_name IS NOT NULL AND btrim(b.assigned_company_name) <> ''
                  AND e.start_date IS NOT NULL
            )
            SELECT c.yr::text AS "period",
                   count(*)   AS "companies",
                   count(*) FILTER (WHERE EXISTS (
                       SELECT 1 FROM ce p WHERE p.company = c.company AND p.yr < c.yr
                   )) AS "returningCompanies"
            FROM ce c
            GROUP BY c.yr
            ORDER BY c.yr
            """)
    List<Map<String, Object>> findRetentionByYear(@Param("tenantId") String tenantId);

    /** 연도별 개최 행사 수(수요예측 추세 입력) — 테넌트 스코프. */
    @Select("""
            SELECT EXTRACT(YEAR FROM start_date)::int AS "yr",
                   count(*) AS "eventCount"
            FROM event
            WHERE tenant_id = #{tenantId}
              AND start_date IS NOT NULL
            GROUP BY EXTRACT(YEAR FROM start_date)
            ORDER BY 1
            """)
    List<Map<String, Object>> findYearlyEventCounts(@Param("tenantId") String tenantId);
}
