Blame for backend/sql/020_care_messages.sql

-- 안부 메시지 (`/message`) — 본인 음색으로 가족/친구에게 보낼 짧은 음성 메시지.
--
-- 흐름:
--   1) 사용자가 음성/텍스트로 "이런 안부 보내줘" 지시
--   2) LLM 이 1~2문장 본문 생성 + 사용자 음색 ICL TTS 합성
--   3) wav 를 Supabase Storage (scheduled-audio bucket 재사용) 에 업로드
--   4) 즉시 공유: 사용자가 카카오 JS SDK 로 본인 카톡에서 가족 채팅방 선택 → /listen/<id> 링크 발송
--   5) 예약: 시각 도달 시 사용자 본인에게 알림톡 ("준비됐어요. 공유하기") → 클릭 → /listen/<id> 페이지의
--           [공유하기] 버튼 → 카톡 공유
--
-- 발신은 항상 사용자 본인 — 알림톡은 사용자 자신에게만 발송 (정책 안전), 가족에게는 사용자 카톡으로 직접.
-- 받는 사람 번호 수집 없음 (PIPA 부담 회피). recipient_label 은 사용자가 자기만 알아보는 메모.

CREATE TABLE IF NOT EXISTS public.care_messages (
    id                 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id            UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

    -- 입력
    recipient_label    TEXT NOT NULL DEFAULT '',  -- "어머니" / "아빠" 등 사용자 메모
    voice_input_text   TEXT,                       -- STT 또는 직접 입력한 지시문 (이력)

    -- 생성된 결과
    generated_text     TEXT NOT NULL DEFAULT '',  -- LLM 이 만든 본문 (실제 음성 텍스트)
    audio_file_path    TEXT,                       -- storage 경로 (삭제용)
    audio_file_url     TEXT,                       -- 1년 signed URL (목록 미리듣기)
    voice_storage_path TEXT,                       -- 합성에 사용한 사용자 음색 (재합성 추적)

    -- 예약 (NULL = 즉시 공유, 예약 없음)
    scheduled_at       TIMESTAMPTZ,
    timezone           TEXT NOT NULL DEFAULT 'Asia/Seoul',
    next_fire_at       TIMESTAMPTZ,
    last_fired_at      TIMESTAMPTZ,
    active             BOOLEAN NOT NULL DEFAULT TRUE,

    -- 분석
    shared_at          TIMESTAMPTZ,                -- 사용자가 [공유하기] 버튼 누른 시각

    created_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_care_messages_user
    ON public.care_messages (user_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_care_messages_due
    ON public.care_messages (active, next_fire_at)
    WHERE next_fire_at IS NOT NULL;

ALTER TABLE public.care_messages ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS care_messages_owner ON public.care_messages;
CREATE POLICY care_messages_owner ON public.care_messages
    FOR ALL
    USING (user_id = auth.uid())
    WITH CHECK (user_id = auth.uid());

-- updated_at 자동 갱신
CREATE OR REPLACE FUNCTION public._care_messages_touch_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS care_messages_touch_updated_at ON public.care_messages;
CREATE TRIGGER care_messages_touch_updated_at
    BEFORE UPDATE ON public.care_messages
    FOR EACH ROW EXECUTE FUNCTION public._care_messages_touch_updated_at();

-- ───────── Storage 정책 ─────────
-- scheduled-audio bucket 재사용 (sql/016 에서 이미 생성). 같은 폴더 (<user_id>/)
-- 안에 care 메시지 wav 를 두므로 추가 정책 불필요.
-- care 파일명 컨벤션: <user_id>/care-<message_id>-<timestamp>.wav