-- migrate-v3.sql: Phase 6 확장 — 캘린더/메일/뉴스 마이그레이션
-- 실행: docker exec -i bot-postgres psql -U bot -d chatbot < init/migrate-v3.sql

-- ========================
-- 캘린더 확장
-- ========================

ALTER TABLE calendar_events ADD COLUMN IF NOT EXISTS caldav_uid VARCHAR(200);
ALTER TABLE calendar_events ADD COLUMN IF NOT EXISTS description TEXT;
ALTER TABLE calendar_events ADD COLUMN IF NOT EXISTS created_by VARCHAR(100);

CREATE INDEX IF NOT EXISTS idx_cal_start ON calendar_events(start_time);
CREATE INDEX IF NOT EXISTS idx_cal_caldav_uid ON calendar_events(caldav_uid);
CREATE INDEX IF NOT EXISTS idx_cal_created_by ON calendar_events(created_by);

-- ========================
-- 메일 인덱스 (Stage C용)
-- ========================

CREATE INDEX IF NOT EXISTS idx_mail_date ON mail_logs(mail_date);
CREATE INDEX IF NOT EXISTS idx_mail_label ON mail_logs(label);
CREATE INDEX IF NOT EXISTS idx_mail_account ON mail_logs(account_id);

-- ========================
-- 뉴스 다이제스트 로그 (Stage E용)
-- ========================

CREATE TABLE IF NOT EXISTS news_digest_log (
    id SERIAL PRIMARY KEY,
    article_url TEXT UNIQUE,
    source VARCHAR(50),
    original_lang VARCHAR(10),
    title_ko TEXT,
    summary_ko TEXT,
    processed_at TIMESTAMPTZ DEFAULT NOW(),
    qdrant_id VARCHAR(100),
    devonthink_uuid VARCHAR(100)
);
CREATE INDEX IF NOT EXISTS idx_news_processed ON news_digest_log(processed_at);