-- User Suspensions Migration
-- Adds user_suspensions table for admin suspend/ban controls

-- 1. Create suspension type enum
DROP TYPE IF EXISTS public.suspension_type CASCADE;
CREATE TYPE public.suspension_type AS ENUM ('suspended', 'banned');

-- 2. Create user_suspensions table
CREATE TABLE IF NOT EXISTS public.user_suspensions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES public.user_profiles(id) ON DELETE CASCADE,
  suspended_by UUID NOT NULL REFERENCES public.user_profiles(id) ON DELETE CASCADE,
  suspension_type public.suspension_type NOT NULL DEFAULT 'suspended',
  reason TEXT NOT NULL,
  duration_days INTEGER NULL, -- NULL = permanent (ban)
  expires_at TIMESTAMPTZ NULL, -- NULL = permanent
  is_active BOOLEAN NOT NULL DEFAULT true,
  lifted_at TIMESTAMPTZ NULL,
  lifted_by UUID NULL REFERENCES public.user_profiles(id) ON DELETE SET NULL,
  lift_reason TEXT NULL,
  email_sent BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 3. Indexes
CREATE INDEX IF NOT EXISTS idx_user_suspensions_user_id ON public.user_suspensions(user_id);
CREATE INDEX IF NOT EXISTS idx_user_suspensions_active ON public.user_suspensions(user_id, is_active);
CREATE INDEX IF NOT EXISTS idx_user_suspensions_expires_at ON public.user_suspensions(expires_at) WHERE is_active = true;

-- 4. Add suspension_status column to user_profiles if not exists
ALTER TABLE public.user_profiles
  ADD COLUMN IF NOT EXISTS suspension_status TEXT DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS suspension_reason TEXT DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS suspended_until TIMESTAMPTZ DEFAULT NULL;

-- 5. Helper function: check if user is currently suspended
CREATE OR REPLACE FUNCTION public.is_user_suspended(p_user_id UUID)
RETURNS BOOLEAN
LANGUAGE sql
STABLE
SECURITY DEFINER
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.user_suspensions
    WHERE user_id = p_user_id
      AND is_active = true
      AND (expires_at IS NULL OR expires_at > now())
  )
$$;

-- 6. Helper function: get active suspension for user
CREATE OR REPLACE FUNCTION public.get_active_suspension(p_user_id UUID)
RETURNS TABLE(
  suspension_id UUID,
  suspension_type TEXT,
  reason TEXT,
  expires_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ
)
LANGUAGE sql
STABLE
SECURITY DEFINER
AS $$
  SELECT id, suspension_type::TEXT, reason, expires_at, created_at
  FROM public.user_suspensions
  WHERE user_id = p_user_id
    AND is_active = true
    AND (expires_at IS NULL OR expires_at > now())
  ORDER BY created_at DESC
  LIMIT 1
$$;

-- 7. Function to auto-expire suspensions
CREATE OR REPLACE FUNCTION public.expire_suspensions()
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  UPDATE public.user_suspensions
  SET is_active = false, updated_at = now()
  WHERE is_active = true
    AND expires_at IS NOT NULL
    AND expires_at <= now();

  -- Sync user_profiles suspension_status
  UPDATE public.user_profiles
  SET suspension_status = NULL,
      suspension_reason = NULL,
      suspended_until = NULL
  WHERE id IN (
    SELECT DISTINCT user_id FROM public.user_suspensions
    WHERE is_active = false
      AND expires_at IS NOT NULL
      AND expires_at <= now()
  )
  AND NOT public.is_user_suspended(id);
END;
$$;

-- 8. Enable RLS
ALTER TABLE public.user_suspensions ENABLE ROW LEVEL SECURITY;

-- 9. RLS Policies
DROP POLICY IF EXISTS "admins_manage_suspensions" ON public.user_suspensions;
CREATE POLICY "admins_manage_suspensions"
ON public.user_suspensions
FOR ALL
TO authenticated
USING (
  EXISTS (
    SELECT 1 FROM public.user_profiles
    WHERE id = auth.uid() AND role IN ('admin', 'moderator')
  )
)
WITH CHECK (
  EXISTS (
    SELECT 1 FROM public.user_profiles
    WHERE id = auth.uid() AND role IN ('admin', 'moderator')
  )
);

DROP POLICY IF EXISTS "users_view_own_suspension" ON public.user_suspensions;
CREATE POLICY "users_view_own_suspension"
ON public.user_suspensions
FOR SELECT
TO authenticated
USING (user_id = auth.uid());