-- ExposedGays v2 migration: albums, canned messages, ID verification
-- Run after schema.sql on SUPABASE_01

-- Profile extensions
ALTER TABLE public.profiles
  ADD COLUMN IF NOT EXISTS state TEXT,
  ADD COLUMN IF NOT EXISTS id_verified BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS id_verified_at TIMESTAMPTZ;

-- ============================================================
-- GALLERY ALBUMS (max 5 per user)
-- ============================================================
CREATE TABLE IF NOT EXISTS public.gallery_albums (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  name TEXT NOT NULL CHECK (char_length(name) <= 40),
  is_default BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_gallery_albums_user ON public.gallery_albums (user_id);

-- Add album_id to gallery_photos
ALTER TABLE public.gallery_photos
  ADD COLUMN IF NOT EXISTS album_id UUID REFERENCES public.gallery_albums(id) ON DELETE SET NULL;

-- ============================================================
-- CANNED MESSAGES (max 20 per user)
-- ============================================================
CREATE TABLE IF NOT EXISTS public.canned_messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  title TEXT NOT NULL CHECK (char_length(title) <= 40),
  content TEXT NOT NULL CHECK (char_length(content) <= 500),
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_canned_messages_user ON public.canned_messages (user_id, sort_order);

-- ============================================================
-- LIMIT ENFORCEMENT TRIGGERS
-- ============================================================
CREATE OR REPLACE FUNCTION public.enforce_gallery_album_limit()
RETURNS TRIGGER AS $$
BEGIN
  IF (SELECT COUNT(*) FROM public.gallery_albums WHERE user_id = NEW.user_id) >= 5 THEN
    RAISE EXCEPTION 'Maximum 5 albums per user';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_gallery_album_limit ON public.gallery_albums;
CREATE TRIGGER trg_gallery_album_limit
  BEFORE INSERT ON public.gallery_albums
  FOR EACH ROW EXECUTE FUNCTION public.enforce_gallery_album_limit();

CREATE OR REPLACE FUNCTION public.enforce_gallery_photo_limit()
RETURNS TRIGGER AS $$
BEGIN
  IF (SELECT COUNT(*) FROM public.gallery_photos WHERE user_id = NEW.user_id) >= 15 THEN
    RAISE EXCEPTION 'Maximum 15 photos per user';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_gallery_photo_limit ON public.gallery_photos;
CREATE TRIGGER trg_gallery_photo_limit
  BEFORE INSERT ON public.gallery_photos
  FOR EACH ROW EXECUTE FUNCTION public.enforce_gallery_photo_limit();

CREATE OR REPLACE FUNCTION public.enforce_canned_message_limit()
RETURNS TRIGGER AS $$
BEGIN
  IF (SELECT COUNT(*) FROM public.canned_messages WHERE user_id = NEW.user_id) >= 20 THEN
    RAISE EXCEPTION 'Maximum 20 canned messages per user';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_canned_message_limit ON public.canned_messages;
CREATE TRIGGER trg_canned_message_limit
  BEFORE INSERT ON public.canned_messages
  FOR EACH ROW EXECUTE FUNCTION public.enforce_canned_message_limit();

-- ============================================================
-- RLS
-- ============================================================
ALTER TABLE public.gallery_albums ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.canned_messages ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Albums viewable by everyone"
  ON public.gallery_albums FOR SELECT USING (true);

CREATE POLICY "Users manage own albums"
  ON public.gallery_albums FOR ALL USING (auth.uid() = user_id);

CREATE POLICY "Canned messages owner only"
  ON public.canned_messages FOR ALL USING (auth.uid() = user_id);