-- =============================================
-- 정부 사업 공고 대시보드 Supabase 스키마
-- Supabase SQL Editor에서 실행하세요
-- =============================================

-- 1. 공고 테이블
CREATE TABLE IF NOT EXISTS announcements (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    source          TEXT NOT NULL,              -- 'MSS' | 'MOTIE'
    title           TEXT NOT NULL,              -- 공고명
    announcement_no TEXT,                       -- 공고번호
    department      TEXT,                       -- 담당 부서
    business_type   TEXT,                       -- 사업 유형
    apply_start     DATE,                       -- 신청기간 시작
    apply_end       DATE,                       -- 신청기간 종료
    detail_url      TEXT,                       -- 상세 페이지 URL
    registered_at   DATE,                       -- 공고 등록일
    fetched_at      TIMESTAMPTZ DEFAULT NOW(),  -- 수집 시각
    period_source   TEXT,                       -- 'list' | 'detail' | 'attachment' | null
    UNIQUE (source, announcement_no)
);

-- 2. 첨부파일 테이블
CREATE TABLE IF NOT EXISTS attachments (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    announcement_id     UUID NOT NULL REFERENCES announcements(id) ON DELETE CASCADE,
    file_name           TEXT NOT NULL,
    file_url            TEXT NOT NULL,
    file_type           TEXT                    -- 'pdf' | 'hwp' | 'docx' | 'zip' 등
);

-- 3. 마지막 업데이트 시각 추적 테이블
CREATE TABLE IF NOT EXISTS crawl_log (
    id          SERIAL PRIMARY KEY,
    crawled_at  TIMESTAMPTZ DEFAULT NOW(),
    new_count   INTEGER DEFAULT 0,
    status      TEXT DEFAULT 'success',         -- 'success' | 'error'
    message     TEXT
);

-- 4. 인덱스
CREATE INDEX IF NOT EXISTS idx_announcements_source ON announcements(source);
CREATE INDEX IF NOT EXISTS idx_announcements_apply_end ON announcements(apply_end);
CREATE INDEX IF NOT EXISTS idx_attachments_announcement_id ON attachments(announcement_id);