DATABASE_MIGRATION.sql

-- DATABASE MIGRATION FOR NOTIFICATION SYSTEM
-- Run this in your Supabase SQL Editor

-- 1. Create the FCM tokens table
CREATE TABLE IF NOT EXISTS public.user_fcm_tokens (
    id UUID PRIMARY KEY DEFAULT extensions.uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    token TEXT NOT NULL,
    device_info TEXT,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now(),
    UNIQUE(user_id, token)
);

-- 2. Create the Notifications log table (referenced as user_notifications in backup)
CREATE TABLE IF NOT EXISTS public.user_notifications (
    id UUID PRIMARY KEY DEFAULT extensions.uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    milestone_id UUID REFERENCES public.milestones(id) ON DELETE SET NULL,
    title TEXT NOT NULL,
    message TEXT NOT NULL,
    type TEXT CHECK (type = ANY (ARRAY['reminder', 'achievement', 'verse', 'prayer', 'reflection', 'system', 'test'])),
    read BOOLEAN DEFAULT false,
    action_url TEXT,
    metadata JSONB,
    scheduled_for TIMESTAMPTZ,
    sent_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- 3. Enable RLS
ALTER TABLE public.user_fcm_tokens ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.user_notifications ENABLE ROW LEVEL SECURITY;

-- 4. RLS Policies
CREATE POLICY "Users can manage their own tokens" ON public.user_fcm_tokens
    FOR ALL USING (auth.uid() = user_id);

CREATE POLICY "Users can view their own notifications" ON public.user_notifications
    FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can update their own notification read status" ON public.user_notifications
    FOR UPDATE USING (auth.uid() = user_id)
    WITH CHECK (auth.uid() = user_id);

-- 5. Updated At Trigger for tokens
CREATE TRIGGER update_fcm_tokens_updated_at 
    BEFORE UPDATE ON public.user_fcm_tokens 
    FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

-- 6. Cron Job Setup (requires pg_cron extension enabled in Supabase)
-- SELECT cron.schedule(
--   'daily-verse-notifications',
--   '0 8 * * *', -- 8:00 AM daily
--   $$ SELECT net.http_post(
--       url := 'https://YOUR_PROJECT_REF.supabase.co/functions/v1/schedule-daily-notifications',
--       headers := '{"Content-Type": "application/json", "Authorization": "Bearer YOUR_SERVICE_ROLE_KEY"}'::jsonb
--   ) $$
-- );