-- 통합 대시보드(BI)용 분석 결과 저장 스키마 — Supabase SQL Editor에 붙여 실행
-- 분석 1회 = (keyword, run_date) 단위. Tableau/Looker가 바로 조회할 수 있는 플랫 구조.

-- 1) 분석 요약 (키워드·날짜별 1행)
create table if not exists analysis_run (
    keyword               text not null,
    run_date              date not null,
    total_search_volume   bigint,
    keyword_count         integer,
    cluster_count         integer,
    intent_info           bigint default 0,   -- 정보 탐색형 검색량
    intent_transactional  bigint default 0,   -- 구매/거래형
    intent_navigational   bigint default 0,   -- 브랜드/내비게이션형
    intent_mixed          bigint default 0,   -- 혼합형
    marketing_suggestion  text,
    primary key (keyword, run_date)
);

-- 2) 클러스터별 (검색 의도 그룹)
create table if not exists analysis_cluster (
    id                   bigserial primary key,
    keyword              text not null,
    run_date             date not null,
    theme                text,        -- AI 테마 그룹명
    rep_keyword          text,        -- 대표 키워드
    intent               text,        -- info | transactional | navigational | mixed
    total_search_volume  bigint,
    keyword_count        integer,
    top_keywords         text         -- 주요 포함 키워드 (쉼표 구분)
);
create index if not exists idx_cluster_kw_date on analysis_cluster (keyword, run_date);

-- 3) 인구통계 추정 비중 (성별×연령)
create table if not exists analysis_demographic (
    id        bigserial primary key,
    keyword   text not null,
    run_date  date not null,
    gender    text,    -- 남성 | 여성
    age       text,    -- 20대 이하 | 30대 | 40대 | 50대 이상
    pct       numeric  -- 추정 비중(%)
);
create index if not exists idx_demo_kw_date on analysis_demographic (keyword, run_date);