-- 013_playlist_schedules.sql
-- 플레이리스트 예약 스케줄 + Web Push (VAPID) 구독 + SMS 전화번호 인증.
--
-- 운영 정책:
--  • playlists 는 *스케줄이 걸린 것만* 서버에 동기화 (그 외에는 클라 localStorage).
--  • playlist_schedules.next_fire_at 을 미리 계산 → cron 1분 tick 이 인덱스 스캔만.
--  • push_subscriptions 는 단말별 (endpoint UNIQUE).
--  • users.phone_e164 는 user_profiles 에 추가 (별도 인증 테이블).

BEGIN;

-- ───────── playlists ─────────
CREATE TABLE IF NOT EXISTS public.playlists (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    name            TEXT NOT NULL,
    items           JSONB NOT NULL DEFAULT '[]'::jsonb,
                    -- [{entryId, repeatCount?, repeatDurationSec?,
                    --   momentId?, slotValues?, presetId?, voice?}]
                    -- moment* 필드는 cache miss 시 다른 단말이 재합성하기 위한 메타.
    client_id       TEXT,           -- 클라이언트가 만든 id (pl_xxx) — 동기화 매칭용
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (user_id, client_id)
);

CREATE INDEX IF NOT EXISTS idx_playlists_user
    ON public.playlists(user_id);

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

    kind            TEXT NOT NULL CHECK (kind IN ('once', 'daily', 'weekly')),
    -- once  : scheduled_at 사용 (절대 일시)
    -- daily : time_of_day 사용 ('HH:MM')
    -- weekly: time_of_day + weekdays (배열 [0..6], 0=일요일)
    scheduled_at    TIMESTAMPTZ,
    time_of_day     TEXT,           -- 'HH:MM'
    weekdays        SMALLINT[],     -- e.g. {1,3,5} (월수금)
    timezone        TEXT NOT NULL DEFAULT 'Asia/Seoul',

    notify_push     BOOLEAN NOT NULL DEFAULT TRUE,
    notify_sms      BOOLEAN NOT NULL DEFAULT FALSE,

    title           TEXT,           -- 알림 제목 override (null 이면 플레이리스트 이름 사용)
    body            TEXT,           -- 알림 본문 override

    active          BOOLEAN NOT NULL DEFAULT TRUE,
    last_fired_at   TIMESTAMPTZ,
    next_fire_at    TIMESTAMPTZ,    -- 미리 계산. NULL 이면 fired-once 종료.

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

CREATE INDEX IF NOT EXISTS idx_schedules_next_fire
    ON public.playlist_schedules(next_fire_at)
    WHERE active = TRUE AND next_fire_at IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_schedules_user
    ON public.playlist_schedules(user_id);

CREATE INDEX IF NOT EXISTS idx_schedules_playlist
    ON public.playlist_schedules(playlist_id);

-- ───────── push_subscriptions (W3C Web Push + VAPID) ─────────
CREATE TABLE IF NOT EXISTS public.push_subscriptions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    endpoint        TEXT NOT NULL UNIQUE,   -- FCM push service endpoint
    p256dh          TEXT NOT NULL,          -- public key (base64url)
    auth            TEXT NOT NULL,          -- auth secret (base64url)
    user_agent      TEXT,
    last_used_at    TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_push_sub_user
    ON public.push_subscriptions(user_id);

-- ───────── user_profiles 에 전화번호 컬럼 ─────────
-- user_profiles 테이블이 이미 존재한다고 가정 (이전 마이그레이션에서 생성).
DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.tables
        WHERE table_schema = 'public' AND table_name = 'user_profiles'
    ) THEN
        BEGIN
            ALTER TABLE public.user_profiles ADD COLUMN IF NOT EXISTS phone_e164 TEXT;
            ALTER TABLE public.user_profiles ADD COLUMN IF NOT EXISTS phone_verified_at TIMESTAMPTZ;
        EXCEPTION WHEN duplicate_column THEN
            -- 이미 추가됨 — 무시
            NULL;
        END;
    END IF;
END $$;

-- ───────── sms_verifications (인증 코드) ─────────
CREATE TABLE IF NOT EXISTS public.sms_verifications (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    phone_e164      TEXT NOT NULL,
    code_hash       TEXT NOT NULL,          -- SHA-256 hex
    attempts        INT NOT NULL DEFAULT 0,
    expires_at      TIMESTAMPTZ NOT NULL,
    consumed_at     TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_sms_verif_user
    ON public.sms_verifications(user_id);

-- ───────── RLS ─────────
ALTER TABLE public.playlists ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.playlist_schedules ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.push_subscriptions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.sms_verifications ENABLE ROW LEVEL SECURITY;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='playlists' AND policyname='playlists_owner') THEN
        CREATE POLICY playlists_owner ON public.playlists
            USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='playlist_schedules' AND policyname='schedules_owner') THEN
        CREATE POLICY schedules_owner ON public.playlist_schedules
            USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='push_subscriptions' AND policyname='push_sub_owner') THEN
        CREATE POLICY push_sub_owner ON public.push_subscriptions
            USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='sms_verifications' AND policyname='sms_verif_owner') THEN
        CREATE POLICY sms_verif_owner ON public.sms_verifications
            USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
    END IF;
END $$;

COMMIT;