database/migrations/006_reports_agents_settlements.sql

-- ============================================================================
-- Carboany Harness System — 리포트, AI 에이전트, 정산 스키마
-- Migration: 006_reports_agents_settlements
--
-- 설계 원칙:
--   1. 리포트 다목적: NGMS, ESG Impact, 감사, Scope 3 인증서
--   2. AI 에이전트 태스크 큐: 비동기 작업 관리
--   3. 정산: 원장(001)과 별도로 주기별 집계 정산 레코드
-- ============================================================================

-- ────────────────────────────────────────────────────────────────────────────
-- 1. 리포트 (Reports)
--    캠페인/이벤트별 생성된 PDF/보고서 메타데이터
-- ────────────────────────────────────────────────────────────────────────────

CREATE TABLE reports (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    campaign_id     UUID REFERENCES campaigns(id),
    event_id        UUID REFERENCES events(id),
    org_id          UUID REFERENCES organizations(id),

    -- 리포트 유형 및 상태
    report_type     VARCHAR(30) NOT NULL
                        CHECK (report_type IN ('ngms', 'esg_impact', 'audit', 'scope3_cert')),
    format          VARCHAR(10) DEFAULT 'pdf' NOT NULL,
    status          VARCHAR(20) DEFAULT 'generating' NOT NULL
                        CHECK (status IN ('generating', 'completed', 'failed')),

    -- 파일 정보
    file_url        VARCHAR(500),                 -- GCS 경로 (생성 완료 시)
    page_count      INT,

    generated_at    TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE INDEX idx_reports_campaign ON reports(campaign_id) WHERE campaign_id IS NOT NULL;
CREATE INDEX idx_reports_event ON reports(event_id) WHERE event_id IS NOT NULL;
CREATE INDEX idx_reports_org ON reports(org_id) WHERE org_id IS NOT NULL;
CREATE INDEX idx_reports_type ON reports(report_type);

-- ────────────────────────────────────────────────────────────────────────────
-- 2. AI 에이전트 태스크 (Agent Tasks)
--    LLM 기반 비동기 작업 큐 — 리포트 생성, 데이터 분석 등
-- ────────────────────────────────────────────────────────────────────────────

CREATE TABLE agent_tasks (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    agent_type      VARCHAR(30) NOT NULL
                        CHECK (agent_type IN (
                            'report_gen', 'data_analysis', 'fraud_scan',
                            'methodology_match', 'notification', 'audit'
                        )),
    title           VARCHAR(300) NOT NULL,
    description     TEXT,

    -- 상태
    status          VARCHAR(20) DEFAULT 'queued' NOT NULL
                        CHECK (status IN ('queued', 'running', 'completed', 'failed', 'cancelled')),

    -- 결과
    result          JSONB,                        -- 에이전트 실행 결과

    created_at      TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    completed_at    TIMESTAMPTZ
);

CREATE INDEX idx_agent_tasks_status ON agent_tasks(status, created_at);
CREATE INDEX idx_agent_tasks_type ON agent_tasks(agent_type);

-- ────────────────────────────────────────────────────────────────────────────
-- 3. 정산 레코드 (Settlements)
--    캠페인별 주기 정산 — 원장(settlement_ledger)의 집계 스냅샷
-- ────────────────────────────────────────────────────────────────────────────

CREATE TABLE settlements (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    campaign_id         UUID NOT NULL REFERENCES campaigns(id),

    -- 정산 기간
    period              VARCHAR(20) NOT NULL,     -- 예: '2026-04-W1', '2026-Q1'

    -- 정산 금액
    total_credits       DECIMAL(12, 2) NOT NULL,  -- 총 크레딧 (tCO₂e)
    total_amount        BIGINT NOT NULL,           -- 총 정산 금액 (KRW)
    user_share          BIGINT NOT NULL,           -- 참여자 보상 (70%)
    platform_share      BIGINT NOT NULL,           -- 플랫폼 수수료 (30%)
    participants        INT DEFAULT 0,             -- 정산 대상 참여자 수

    -- 지급 정보
    payout_method       VARCHAR(30)
                            CHECK (payout_method IN ('naver_pay', 'welfare_mall', 'bank_transfer')),
    payout_ref          VARCHAR(200),             -- 외부 결제 트랜잭션 ID

    -- 상태
    status              VARCHAR(20) DEFAULT 'pending' NOT NULL
                            CHECK (status IN ('pending', 'processing', 'completed', 'failed')),
    ledger_entry_id     UUID,                     -- 원장 연결 (settlement_ledger.transaction_id)

    processed_at        TIMESTAMPTZ,
    created_at          TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE INDEX idx_settlements_campaign ON settlements(campaign_id);
CREATE INDEX idx_settlements_status ON settlements(status);
CREATE INDEX idx_settlements_period ON settlements(campaign_id, period);