Migrations Supabase

-- ============================================
-- COMBINED MIGRATION: Admin + Moderation System
-- Run this script in Supabase SQL Editor
-- ============================================

-- ============================================
-- PART 1: ADMIN SETTINGS (from 008)
-- ============================================

-- Global application settings
CREATE TABLE IF NOT EXISTS public.app_settings (
  id VARCHAR(50) PRIMARY KEY,
  value JSONB NOT NULL DEFAULT '{}',
  description TEXT,
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  updated_by UUID REFERENCES auth.users(id)
);

INSERT INTO public.app_settings (id, value, description) VALUES
  ('email_reminders', '{"enabled": true, "module_reminders": true, "streak_reminders": true, "comeback_reminders": true, "daily_limit": 100}', 'Global email reminder settings'),
  ('registration', '{"enabled": true, "require_email_verification": false}', 'User registration settings'),
  ('maintenance', '{"enabled": false, "message": ""}', 'Maintenance mode settings')
ON CONFLICT (id) DO NOTHING;

-- Admin users table
CREATE TABLE IF NOT EXISTS public.admin_users (
  user_id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  role VARCHAR(20) NOT NULL DEFAULT 'moderator' CHECK (role IN ('super_admin', 'admin', 'moderator')),
  permissions JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  created_by UUID REFERENCES auth.users(id)
);

ALTER TABLE public.app_settings ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.admin_users ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Anyone can read app settings" ON public.app_settings;
CREATE POLICY "Anyone can read app settings" ON public.app_settings
  FOR SELECT USING (true);

DROP POLICY IF EXISTS "Admins can modify settings" ON public.app_settings;
CREATE POLICY "Admins can modify settings" ON public.app_settings
  FOR ALL USING (
    EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = auth.uid() AND role IN ('super_admin', 'admin'))
  );

DROP POLICY IF EXISTS "Super admin can manage admins" ON public.admin_users;
CREATE POLICY "Super admin can manage admins" ON public.admin_users
  FOR ALL USING (
    EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = auth.uid() AND role = 'super_admin')
  );

DROP POLICY IF EXISTS "Admins can view admin list" ON public.admin_users;
CREATE POLICY "Admins can view admin list" ON public.admin_users
  FOR SELECT USING (
    EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = auth.uid())
  );

-- Function to check if user is admin
CREATE OR REPLACE FUNCTION public.is_admin(check_user_id UUID DEFAULT auth.uid())
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = check_user_id);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to get admin role
CREATE OR REPLACE FUNCTION public.get_admin_role(check_user_id UUID DEFAULT auth.uid())
RETURNS VARCHAR AS $$
DECLARE
  user_role VARCHAR;
BEGIN
  SELECT role INTO user_role FROM public.admin_users WHERE user_id = check_user_id;
  RETURN user_role;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to toggle user email reminders (admin only)
CREATE OR REPLACE FUNCTION public.admin_toggle_user_reminders(
  target_user_id UUID,
  disabled BOOLEAN
)
RETURNS BOOLEAN AS $$
BEGIN
  IF NOT public.is_admin() THEN
    RAISE EXCEPTION 'Unauthorized';
  END IF;

  INSERT INTO public.user_notification_preferences (user_id, admin_disabled, updated_at)
  VALUES (target_user_id, disabled, NOW())
  ON CONFLICT (user_id) DO UPDATE SET
    admin_disabled = disabled,
    updated_at = NOW();

  RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to update global settings (admin only)
CREATE OR REPLACE FUNCTION public.admin_update_setting(
  setting_id VARCHAR,
  new_value JSONB
)
RETURNS BOOLEAN AS $$
BEGIN
  IF NOT public.is_admin() THEN
    RAISE EXCEPTION 'Unauthorized';
  END IF;

  UPDATE public.app_settings
  SET value = new_value, updated_at = NOW(), updated_by = auth.uid()
  WHERE id = setting_id;

  RETURN FOUND;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ============================================
-- PART 2: EXTENDED ADMIN FEATURES (from 009)
-- ============================================

-- Admin activity log
CREATE TABLE IF NOT EXISTS public.admin_activity_log (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  admin_id UUID NOT NULL REFERENCES auth.users(id),
  action VARCHAR(100) NOT NULL,
  target_type VARCHAR(50),
  target_id VARCHAR(255),
  details JSONB DEFAULT '{}',
  ip_address INET,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_admin_activity_admin ON public.admin_activity_log(admin_id);
CREATE INDEX IF NOT EXISTS idx_admin_activity_created ON public.admin_activity_log(created_at DESC);

ALTER TABLE public.admin_activity_log ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Admins can view activity log" ON public.admin_activity_log;
CREATE POLICY "Admins can view activity log" ON public.admin_activity_log
  FOR SELECT USING (
    EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = auth.uid())
  );

DROP POLICY IF EXISTS "System can insert activity log" ON public.admin_activity_log;
CREATE POLICY "System can insert activity log" ON public.admin_activity_log
  FOR INSERT WITH CHECK (true);

-- Reported content
CREATE TABLE IF NOT EXISTS public.reported_content (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  reporter_id UUID REFERENCES auth.users(id),
  content_type VARCHAR(50) NOT NULL,
  content_id UUID NOT NULL,
  reason VARCHAR(100) NOT NULL,
  description TEXT,
  status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'reviewed', 'actioned', 'dismissed')),
  reviewed_by UUID REFERENCES auth.users(id),
  reviewed_at TIMESTAMPTZ,
  action_taken VARCHAR(100),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_reported_content_status ON public.reported_content(status);
CREATE INDEX IF NOT EXISTS idx_reported_content_created ON public.reported_content(created_at DESC);

ALTER TABLE public.reported_content ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Users can report content" ON public.reported_content;
CREATE POLICY "Users can report content" ON public.reported_content
  FOR INSERT WITH CHECK (auth.uid() = reporter_id);

DROP POLICY IF EXISTS "Admins can view reports" ON public.reported_content;
CREATE POLICY "Admins can view reports" ON public.reported_content
  FOR SELECT USING (
    EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = auth.uid())
  );

DROP POLICY IF EXISTS "Admins can update reports" ON public.reported_content;
CREATE POLICY "Admins can update reports" ON public.reported_content
  FOR UPDATE USING (
    EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = auth.uid())
  );

-- User bans
CREATE TABLE IF NOT EXISTS public.user_bans (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  banned_by UUID NOT NULL REFERENCES auth.users(id),
  reason TEXT NOT NULL,
  expires_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  lifted_at TIMESTAMPTZ,
  lifted_by UUID REFERENCES auth.users(id)
);

CREATE INDEX IF NOT EXISTS idx_user_bans_user ON public.user_bans(user_id);
CREATE INDEX IF NOT EXISTS idx_user_bans_active ON public.user_bans(user_id) WHERE lifted_at IS NULL;

ALTER TABLE public.user_bans ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Admins can manage bans" ON public.user_bans;
CREATE POLICY "Admins can manage bans" ON public.user_bans
  FOR ALL USING (
    EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = auth.uid())
  );

-- Log admin activity function
CREATE OR REPLACE FUNCTION public.log_admin_activity(
  p_action VARCHAR,
  p_target_type VARCHAR DEFAULT NULL,
  p_target_id VARCHAR DEFAULT NULL,
  p_details JSONB DEFAULT '{}'
)
RETURNS UUID AS $$
DECLARE
  log_id UUID;
BEGIN
  IF NOT public.is_admin() THEN
    RAISE EXCEPTION 'Unauthorized';
  END IF;

  INSERT INTO public.admin_activity_log (admin_id, action, target_type, target_id, details)
  VALUES (auth.uid(), p_action, p_target_type, p_target_id, p_details)
  RETURNING id INTO log_id;

  RETURN log_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Ban user function
CREATE OR REPLACE FUNCTION public.admin_ban_user(
  p_user_id UUID,
  p_reason TEXT,
  p_expires_at TIMESTAMPTZ DEFAULT NULL
)
RETURNS BOOLEAN AS $$
BEGIN
  IF NOT public.is_admin() THEN
    RAISE EXCEPTION 'Unauthorized';
  END IF;

  IF EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = p_user_id) THEN
    RAISE EXCEPTION 'Cannot ban admin users';
  END IF;

  INSERT INTO public.user_bans (user_id, banned_by, reason, expires_at)
  VALUES (p_user_id, auth.uid(), p_reason, p_expires_at);

  PERFORM public.log_admin_activity(
    'ban_user',
    'user',
    p_user_id::VARCHAR,
    jsonb_build_object('reason', p_reason, 'expires_at', p_expires_at)
  );

  RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Unban user function
CREATE OR REPLACE FUNCTION public.admin_unban_user(p_user_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
  IF NOT public.is_admin() THEN
    RAISE EXCEPTION 'Unauthorized';
  END IF;

  UPDATE public.user_bans
  SET lifted_at = NOW(), lifted_by = auth.uid()
  WHERE user_id = p_user_id AND lifted_at IS NULL;

  PERFORM public.log_admin_activity('unban_user', 'user', p_user_id::VARCHAR);

  RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Check if user is banned
CREATE OR REPLACE FUNCTION public.is_user_banned(p_user_id UUID DEFAULT auth.uid())
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM public.user_bans
    WHERE user_id = p_user_id
      AND lifted_at IS NULL
      AND (expires_at IS NULL OR expires_at > NOW())
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Review reported content
CREATE OR REPLACE FUNCTION public.admin_review_report(
  p_report_id UUID,
  p_status VARCHAR,
  p_action_taken VARCHAR DEFAULT NULL
)
RETURNS BOOLEAN AS $$
BEGIN
  IF NOT public.is_admin() THEN
    RAISE EXCEPTION 'Unauthorized';
  END IF;

  UPDATE public.reported_content
  SET
    status = p_status,
    reviewed_by = auth.uid(),
    reviewed_at = NOW(),
    action_taken = p_action_taken
  WHERE id = p_report_id;

  PERFORM public.log_admin_activity(
    'review_report',
    'report',
    p_report_id::VARCHAR,
    jsonb_build_object('status', p_status, 'action', p_action_taken)
  );

  RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ============================================
-- PART 3: CONTENT MODERATION (from 011)
-- ============================================

-- Banned words table
CREATE TABLE IF NOT EXISTS public.banned_words (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  word VARCHAR(100) NOT NULL,
  locale VARCHAR(5) DEFAULT NULL,
  severity VARCHAR(20) DEFAULT 'medium' CHECK (severity IN ('low', 'medium', 'high')),
  is_regex BOOLEAN DEFAULT FALSE,
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(word, locale)
);

CREATE INDEX IF NOT EXISTS idx_banned_words_locale ON public.banned_words(locale);
CREATE INDEX IF NOT EXISTS idx_banned_words_severity ON public.banned_words(severity);

ALTER TABLE public.banned_words ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Admins can manage banned words" ON public.banned_words;
CREATE POLICY "Admins can manage banned words" ON public.banned_words
  FOR ALL USING (
    EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = auth.uid())
  );

-- Add moderation fields to discussions (if table exists)
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'discussions') THEN
    ALTER TABLE public.discussions
    ADD COLUMN IF NOT EXISTS is_hidden BOOLEAN DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS hidden_reason TEXT,
    ADD COLUMN IF NOT EXISTS hidden_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS hidden_by UUID REFERENCES auth.users(id),
    ADD COLUMN IF NOT EXISTS auto_moderated BOOLEAN DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS triggered_word VARCHAR(100);
  END IF;
END $$;

-- Add moderation fields to discussion_replies (if table exists)
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'discussion_replies') THEN
    ALTER TABLE public.discussion_replies
    ADD COLUMN IF NOT EXISTS is_hidden BOOLEAN DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS hidden_reason TEXT,
    ADD COLUMN IF NOT EXISTS hidden_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS hidden_by UUID REFERENCES auth.users(id),
    ADD COLUMN IF NOT EXISTS auto_moderated BOOLEAN DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS triggered_word VARCHAR(100);
  END IF;
END $$;

-- Moderation log table
CREATE TABLE IF NOT EXISTS public.moderation_log (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  content_type VARCHAR(20) NOT NULL CHECK (content_type IN ('discussion', 'reply')),
  content_id UUID NOT NULL,
  action VARCHAR(50) NOT NULL,
  reason TEXT,
  triggered_word VARCHAR(100),
  performed_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_mod_log_content ON public.moderation_log(content_type, content_id);
CREATE INDEX IF NOT EXISTS idx_mod_log_created ON public.moderation_log(created_at DESC);

ALTER TABLE public.moderation_log ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Admins can view moderation log" ON public.moderation_log;
CREATE POLICY "Admins can view moderation log" ON public.moderation_log
  FOR SELECT USING (
    EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = auth.uid())
  );

DROP POLICY IF EXISTS "System can insert moderation log" ON public.moderation_log;
CREATE POLICY "System can insert moderation log" ON public.moderation_log
  FOR INSERT WITH CHECK (true);

-- View for hidden content (Admin Panel)
CREATE OR REPLACE VIEW public.admin_hidden_content AS
SELECT
  'discussion' as content_type,
  d.id as content_id,
  d.title,
  d.content,
  d.author_id,
  d.author_name,
  d.is_hidden,
  d.hidden_reason,
  d.hidden_at,
  d.hidden_by,
  d.auto_moderated,
  d.triggered_word,
  d.created_at
FROM public.discussions d
WHERE d.is_hidden = TRUE
UNION ALL
SELECT
  'reply' as content_type,
  r.id as content_id,
  NULL as title,
  r.content,
  r.author_id,
  r.author_name,
  r.is_hidden,
  r.hidden_reason,
  r.hidden_at,
  r.hidden_by,
  r.auto_moderated,
  r.triggered_word,
  r.created_at
FROM public.discussion_replies r
WHERE r.is_hidden = TRUE
ORDER BY hidden_at DESC NULLS LAST, created_at DESC;

-- Function: Hide content (manual moderation)
CREATE OR REPLACE FUNCTION public.admin_hide_content(
  p_content_type VARCHAR,
  p_content_id UUID,
  p_reason TEXT
)
RETURNS BOOLEAN AS $$
BEGIN
  IF NOT public.is_admin() THEN
    RAISE EXCEPTION 'Unauthorized';
  END IF;

  IF p_content_type = 'discussion' THEN
    UPDATE public.discussions
    SET
      is_hidden = TRUE,
      hidden_reason = p_reason,
      hidden_at = NOW(),
      hidden_by = auth.uid(),
      auto_moderated = FALSE
    WHERE id = p_content_id;
  ELSIF p_content_type = 'reply' THEN
    UPDATE public.discussion_replies
    SET
      is_hidden = TRUE,
      hidden_reason = p_reason,
      hidden_at = NOW(),
      hidden_by = auth.uid(),
      auto_moderated = FALSE
    WHERE id = p_content_id;
  ELSE
    RAISE EXCEPTION 'Invalid content type';
  END IF;

  INSERT INTO public.moderation_log (content_type, content_id, action, reason, performed_by)
  VALUES (p_content_type, p_content_id, 'manual_hide', p_reason, auth.uid());

  PERFORM public.log_admin_activity(
    'hide_content',
    p_content_type,
    p_content_id::VARCHAR,
    jsonb_build_object('reason', p_reason)
  );

  RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function: Unhide content
CREATE OR REPLACE FUNCTION public.admin_unhide_content(
  p_content_type VARCHAR,
  p_content_id UUID
)
RETURNS BOOLEAN AS $$
BEGIN
  IF NOT public.is_admin() THEN
    RAISE EXCEPTION 'Unauthorized';
  END IF;

  IF p_content_type = 'discussion' THEN
    UPDATE public.discussions
    SET
      is_hidden = FALSE,
      hidden_reason = NULL,
      hidden_at = NULL,
      hidden_by = NULL,
      auto_moderated = FALSE,
      triggered_word = NULL
    WHERE id = p_content_id;
  ELSIF p_content_type = 'reply' THEN
    UPDATE public.discussion_replies
    SET
      is_hidden = FALSE,
      hidden_reason = NULL,
      hidden_at = NULL,
      hidden_by = NULL,
      auto_moderated = FALSE,
      triggered_word = NULL
    WHERE id = p_content_id;
  ELSE
    RAISE EXCEPTION 'Invalid content type';
  END IF;

  INSERT INTO public.moderation_log (content_type, content_id, action, performed_by)
  VALUES (p_content_type, p_content_id, 'unhide', auth.uid());

  PERFORM public.log_admin_activity('unhide_content', p_content_type, p_content_id::VARCHAR);

  RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function: Delete content
CREATE OR REPLACE FUNCTION public.admin_delete_content(
  p_content_type VARCHAR,
  p_content_id UUID
)
RETURNS BOOLEAN AS $$
BEGIN
  IF NOT public.is_admin() THEN
    RAISE EXCEPTION 'Unauthorized';
  END IF;

  INSERT INTO public.moderation_log (content_type, content_id, action, performed_by)
  VALUES (p_content_type, p_content_id, 'delete', auth.uid());

  IF p_content_type = 'discussion' THEN
    DELETE FROM public.discussions WHERE id = p_content_id;
  ELSIF p_content_type = 'reply' THEN
    DELETE FROM public.discussion_replies WHERE id = p_content_id;
  ELSE
    RAISE EXCEPTION 'Invalid content type';
  END IF;

  PERFORM public.log_admin_activity('delete_content', p_content_type, p_content_id::VARCHAR);

  RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function: Auto-hide content (called from application)
CREATE OR REPLACE FUNCTION public.auto_hide_content(
  p_content_type VARCHAR,
  p_content_id UUID,
  p_triggered_word VARCHAR
)
RETURNS BOOLEAN AS $$
BEGIN
  IF p_content_type = 'discussion' THEN
    UPDATE public.discussions
    SET
      is_hidden = TRUE,
      hidden_reason = 'Auto-moderated: contains banned word',
      hidden_at = NOW(),
      auto_moderated = TRUE,
      triggered_word = p_triggered_word
    WHERE id = p_content_id;
  ELSIF p_content_type = 'reply' THEN
    UPDATE public.discussion_replies
    SET
      is_hidden = TRUE,
      hidden_reason = 'Auto-moderated: contains banned word',
      hidden_at = NOW(),
      auto_moderated = TRUE,
      triggered_word = p_triggered_word
    WHERE id = p_content_id;
  ELSE
    RETURN FALSE;
  END IF;

  INSERT INTO public.moderation_log (content_type, content_id, action, triggered_word)
  VALUES (p_content_type, p_content_id, 'auto_hide', p_triggered_word);

  RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function: Get banned words (for frontend filtering)
CREATE OR REPLACE FUNCTION public.get_banned_words(p_locale VARCHAR DEFAULT NULL)
RETURNS TABLE (
  word VARCHAR,
  is_regex BOOLEAN,
  severity VARCHAR
) AS $$
BEGIN
  RETURN QUERY
  SELECT bw.word, bw.is_regex, bw.severity
  FROM public.banned_words bw
  WHERE bw.locale IS NULL OR bw.locale = p_locale;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function: Add banned word (admin only)
CREATE OR REPLACE FUNCTION public.admin_add_banned_word(
  p_word VARCHAR,
  p_locale VARCHAR DEFAULT NULL,
  p_severity VARCHAR DEFAULT 'medium',
  p_is_regex BOOLEAN DEFAULT FALSE
)
RETURNS UUID AS $$
DECLARE
  new_id UUID;
BEGIN
  IF NOT public.is_admin() THEN
    RAISE EXCEPTION 'Unauthorized';
  END IF;

  INSERT INTO public.banned_words (word, locale, severity, is_regex, created_by)
  VALUES (p_word, p_locale, p_severity, p_is_regex, auth.uid())
  RETURNING id INTO new_id;

  PERFORM public.log_admin_activity(
    'add_banned_word',
    'banned_word',
    new_id::VARCHAR,
    jsonb_build_object('word', p_word, 'locale', p_locale, 'severity', p_severity)
  );

  RETURN new_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function: Remove banned word (admin only)
CREATE OR REPLACE FUNCTION public.admin_remove_banned_word(p_word_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
  IF NOT public.is_admin() THEN
    RAISE EXCEPTION 'Unauthorized';
  END IF;

  DELETE FROM public.banned_words WHERE id = p_word_id;

  PERFORM public.log_admin_activity('remove_banned_word', 'banned_word', p_word_id::VARCHAR);

  RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Update RLS policies for discussions (if table exists)
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'discussions') THEN
    DROP POLICY IF EXISTS "Users can view discussions" ON public.discussions;
    CREATE POLICY "Users can view discussions" ON public.discussions
      FOR SELECT USING (
        (is_hidden = FALSE OR is_hidden IS NULL)
        OR (is_hidden = TRUE AND author_id = auth.uid())
        OR EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = auth.uid())
      );
  END IF;
END $$;

-- Update RLS policies for discussion_replies (if table exists)
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'discussion_replies') THEN
    DROP POLICY IF EXISTS "Users can view replies" ON public.discussion_replies;
    CREATE POLICY "Users can view replies" ON public.discussion_replies
      FOR SELECT USING (
        (is_hidden = FALSE OR is_hidden IS NULL)
        OR (is_hidden = TRUE AND author_id = auth.uid())
        OR EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = auth.uid())
      );
  END IF;
END $$;

-- ============================================
-- GRANT PERMISSIONS
-- ============================================
GRANT SELECT ON public.admin_hidden_content TO authenticated;
GRANT EXECUTE ON FUNCTION public.is_admin TO authenticated;
GRANT EXECUTE ON FUNCTION public.get_admin_role TO authenticated;
GRANT EXECUTE ON FUNCTION public.admin_hide_content TO authenticated;
GRANT EXECUTE ON FUNCTION public.admin_unhide_content TO authenticated;
GRANT EXECUTE ON FUNCTION public.admin_delete_content TO authenticated;
GRANT EXECUTE ON FUNCTION public.auto_hide_content TO authenticated;
GRANT EXECUTE ON FUNCTION public.get_banned_words TO authenticated;
GRANT EXECUTE ON FUNCTION public.admin_add_banned_word TO authenticated;
GRANT EXECUTE ON FUNCTION public.admin_remove_banned_word TO authenticated;
GRANT EXECUTE ON FUNCTION public.log_admin_activity TO authenticated;
GRANT EXECUTE ON FUNCTION public.admin_ban_user TO authenticated;
GRANT EXECUTE ON FUNCTION public.admin_unban_user TO authenticated;
GRANT EXECUTE ON FUNCTION public.is_user_banned TO authenticated;
GRANT EXECUTE ON FUNCTION public.admin_review_report TO authenticated;
GRANT EXECUTE ON FUNCTION public.admin_toggle_user_reminders TO authenticated;
GRANT EXECUTE ON FUNCTION public.admin_update_setting TO authenticated;

-- ============================================
-- DONE! Now add yourself as super_admin:
--
-- INSERT INTO public.admin_users (user_id, role)
-- VALUES ('YOUR-USER-UUID-HERE', 'super_admin');
-- ============================================

-- =============================================
-- SCILIT ADMIN - CONFIGURATION COMPLETE
-- Exécute ce SQL dans Supabase SQL Editor
-- =============================================

-- ============================================
-- 1. TABLES DE BASE
-- ============================================

-- Table admin_users (si pas déjà créée)
CREATE TABLE IF NOT EXISTS public.admin_users (
  user_id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  role VARCHAR(20) NOT NULL DEFAULT 'moderator' CHECK (role IN ('super_admin', 'admin', 'moderator')),
  permissions JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  created_by UUID REFERENCES auth.users(id)
);

ALTER TABLE public.admin_users ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Super admin can manage admins" ON public.admin_users;
CREATE POLICY "Super admin can manage admins" ON public.admin_users
  FOR ALL USING (
    EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = auth.uid() AND role = 'super_admin')
  );

DROP POLICY IF EXISTS "Admins can view admin list" ON public.admin_users;
CREATE POLICY "Admins can view admin list" ON public.admin_users
  FOR SELECT USING (
    EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = auth.uid())
  );

-- Table pre_registrations
CREATE TABLE IF NOT EXISTS public.pre_registrations (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  full_name VARCHAR(255),
  locale VARCHAR(10) DEFAULT 'fr',
  source VARCHAR(100),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  converted_at TIMESTAMPTZ
);

ALTER TABLE public.pre_registrations ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Admins can read pre_registrations" ON public.pre_registrations;
CREATE POLICY "Admins can read pre_registrations" ON public.pre_registrations
  FOR SELECT USING (
    EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = auth.uid())
  );

DROP POLICY IF EXISTS "Anyone can insert pre_registrations" ON public.pre_registrations;
CREATE POLICY "Anyone can insert pre_registrations" ON public.pre_registrations
  FOR INSERT WITH CHECK (true);

-- S'assurer que converted_at existe
ALTER TABLE public.pre_registrations ADD COLUMN IF NOT EXISTS converted_at TIMESTAMPTZ;

-- Table app_settings
CREATE TABLE IF NOT EXISTS public.app_settings (
  id VARCHAR(100) PRIMARY KEY,
  value JSONB NOT NULL DEFAULT '{}',
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  updated_by UUID REFERENCES auth.users(id)
);

ALTER TABLE public.app_settings ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Admins can read settings" ON public.app_settings;
CREATE POLICY "Admins can read settings" ON public.app_settings
  FOR SELECT USING (
    EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = auth.uid())
  );

DROP POLICY IF EXISTS "Super admin can update settings" ON public.app_settings;
CREATE POLICY "Super admin can update settings" ON public.app_settings
  FOR ALL USING (
    EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = auth.uid() AND role = 'super_admin')
  );

-- Insérer les settings par défaut s'ils n'existent pas
INSERT INTO public.app_settings (id, value) VALUES
  ('email_reminders', '{"enabled": true, "module_reminders": true, "streak_reminders": true, "comeback_reminders": true, "daily_limit": 100}'),
  ('registration', '{"enabled": true, "require_email_verification": false}'),
  ('maintenance', '{"enabled": false, "message": ""}')
ON CONFLICT (id) DO NOTHING;

-- Table email_reminders_log (pour les stats d'emails)
CREATE TABLE IF NOT EXISTS public.email_reminders_log (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  email_type VARCHAR(50) NOT NULL,
  success BOOLEAN DEFAULT true,
  error_message TEXT,
  sent_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE public.email_reminders_log ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Admins can read email logs" ON public.email_reminders_log;
CREATE POLICY "Admins can read email logs" ON public.email_reminders_log
  FOR SELECT USING (
    EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = auth.uid())
  );

-- Table admin_activity_log
CREATE TABLE IF NOT EXISTS public.admin_activity_log (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  admin_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  action VARCHAR(100) NOT NULL,
  target_type VARCHAR(50),
  target_id VARCHAR(255),
  details JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE public.admin_activity_log ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Admins can read activity log" ON public.admin_activity_log;
CREATE POLICY "Admins can read activity log" ON public.admin_activity_log
  FOR SELECT USING (
    EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = auth.uid())
  );

-- ============================================
-- 2. FONCTIONS ADMIN
-- ============================================

-- Fonction pour vérifier si l'utilisateur est admin
CREATE OR REPLACE FUNCTION public.is_admin(check_user_id UUID DEFAULT auth.uid())
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = check_user_id);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Fonction pour obtenir le rôle admin
CREATE OR REPLACE FUNCTION public.get_admin_role(check_user_id UUID DEFAULT auth.uid())
RETURNS VARCHAR AS $$
DECLARE
  user_role VARCHAR;
BEGIN
  SELECT role INTO user_role FROM public.admin_users WHERE user_id = check_user_id;
  RETURN user_role;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Fonction pour récupérer les pré-inscriptions
CREATE OR REPLACE FUNCTION public.admin_get_preregistrations(
  p_locale VARCHAR DEFAULT NULL,
  p_converted BOOLEAN DEFAULT NULL
)
RETURNS TABLE (
  email VARCHAR,
  full_name VARCHAR,
  locale VARCHAR,
  created_at TIMESTAMPTZ,
  converted BOOLEAN
) AS $$
BEGIN
  IF NOT public.is_admin() THEN
    RAISE EXCEPTION 'Unauthorized';
  END IF;

  RETURN QUERY
  SELECT
    pr.email,
    pr.full_name,
    pr.locale,
    pr.created_at,
    (pr.converted_at IS NOT NULL) as converted
  FROM public.pre_registrations pr
  WHERE
    (p_locale IS NULL OR pr.locale = p_locale)
    AND (p_converted IS NULL OR (pr.converted_at IS NOT NULL) = p_converted)
  ORDER BY pr.created_at DESC;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Fonction pour mettre à jour un setting
CREATE OR REPLACE FUNCTION public.admin_update_setting(
  setting_id VARCHAR,
  new_value JSONB
)
RETURNS BOOLEAN AS $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM public.admin_users WHERE user_id = auth.uid() AND role IN ('super_admin', 'admin')) THEN
    RAISE EXCEPTION 'Unauthorized';
  END IF;

  UPDATE public.app_settings
  SET value = new_value, updated_at = NOW(), updated_by = auth.uid()
  WHERE id = setting_id;

  RETURN FOUND;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Fonction pour toggler les reminders d'un utilisateur
CREATE OR REPLACE FUNCTION public.admin_toggle_user_reminders(
  target_user_id UUID,
  disabled BOOLEAN
)
RETURNS BOOLEAN AS $$
BEGIN
  IF NOT public.is_admin() THEN
    RAISE EXCEPTION 'Unauthorized';
  END IF;

  -- Log l'action
  INSERT INTO public.admin_activity_log (admin_id, action, target_type, target_id, details)
  VALUES (auth.uid(), 'toggle_reminders', 'user', target_user_id::TEXT, jsonb_build_object('disabled', disabled));

  -- Pour l'instant on ne fait rien car la colonne n'existe pas encore
  -- On pourrait ajouter admin_disabled_reminders à profiles
  RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Fonction pour exporter les données utilisateur (GDPR)
CREATE OR REPLACE FUNCTION public.admin_export_user_data(p_user_id UUID)
RETURNS JSONB AS $$
DECLARE
  result JSONB;
BEGIN
  IF NOT public.is_admin() THEN
    RAISE EXCEPTION 'Unauthorized';
  END IF;

  SELECT jsonb_build_object(
    'profile', (SELECT row_to_json(p) FROM profiles p WHERE p.id = p_user_id),
    'stats', (SELECT row_to_json(s) FROM user_stats s WHERE s.user_id = p_user_id),
    'module_progress', (SELECT jsonb_agg(row_to_json(mp)) FROM user_module_progress mp WHERE mp.user_id = p_user_id),
    'section_progress', (SELECT jsonb_agg(row_to_json(sp)) FROM user_section_progress sp WHERE sp.user_id = p_user_id),
    'badges', (SELECT jsonb_agg(row_to_json(ub)) FROM user_badges ub WHERE ub.user_id = p_user_id),
    'techniques', (SELECT jsonb_agg(row_to_json(ut)) FROM user_techniques ut WHERE ut.user_id = p_user_id)
  ) INTO result;

  -- Log l'action
  INSERT INTO public.admin_activity_log (admin_id, action, target_type, target_id)
  VALUES (auth.uid(), 'export_user_data', 'user', p_user_id::TEXT);

  RETURN result;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ============================================
-- 3. VUES ADMIN
-- ============================================

-- Vue admin_user_list
DROP VIEW IF EXISTS public.admin_user_list;

CREATE OR REPLACE VIEW public.admin_user_list AS
SELECT
  u.id as user_id,
  u.email,
  COALESCE(p.full_name, u.raw_user_meta_data->>'full_name', u.raw_user_meta_data->>'name') as display_name,
  u.created_at as registered_at,
  u.last_sign_in_at,
  COALESCE(s.last_activity_at, u.last_sign_in_at) as last_activity,
  COALESCE(s.current_streak, 0) as current_streak,
  COALESCE(s.total_points, 0) / 100 as total_sessions, -- approximation
  COALESCE(
    (SELECT COUNT(*)::INTEGER FROM user_module_progress ump WHERE ump.user_id = u.id AND ump.status = 'in_progress'),
    0
  ) as modules_in_progress_count,
  COALESCE(
    (SELECT ARRAY_AGG(module_id) FROM user_module_progress ump WHERE ump.user_id = u.id AND ump.status = 'completed'),
    ARRAY[]::INTEGER[]
  ) as modules_completed,
  true as email_reminders_enabled,
  false as admin_disabled_reminders,
  COALESCE(p.locale, 'fr') as preferred_language,
  a.role as admin_role,
  COALESCE(s.techniques_collected, 0) as collectibles_count,
  COALESCE(s.total_points, 0) as total_points,
  false as is_banned
FROM auth.users u
LEFT JOIN public.profiles p ON u.id = p.id
LEFT JOIN public.user_stats s ON u.id = s.user_id
LEFT JOIN public.admin_users a ON u.id = a.user_id
ORDER BY u.created_at DESC;

-- Vue admin_preregistration_stats
DROP VIEW IF EXISTS public.admin_preregistration_stats;

CREATE OR REPLACE VIEW public.admin_preregistration_stats AS
SELECT
  COALESCE(locale, 'unknown') as locale,
  COUNT(*) as total,
  COUNT(*) FILTER (WHERE converted_at IS NOT NULL) as converted,
  COUNT(*) FILTER (WHERE converted_at IS NULL) as pending,
  MIN(created_at)::DATE as first_registration,
  MAX(created_at)::DATE as last_registration
FROM public.pre_registrations
GROUP BY locale
ORDER BY total DESC;

-- Vue admin_daily_signups
DROP VIEW IF EXISTS public.admin_daily_signups;

CREATE OR REPLACE VIEW public.admin_daily_signups AS
SELECT
  DATE(created_at) as date,
  COUNT(*) as signups
FROM auth.users
WHERE created_at >= NOW() - INTERVAL '90 days'
GROUP BY DATE(created_at)
ORDER BY date DESC;

-- Vue admin_daily_active
DROP VIEW IF EXISTS public.admin_daily_active;

CREATE OR REPLACE VIEW public.admin_daily_active AS
SELECT
  DATE(last_sign_in_at) as date,
  COUNT(DISTINCT id) as active_users
FROM auth.users
WHERE last_sign_in_at >= NOW() - INTERVAL '90 days'
  AND last_sign_in_at IS NOT NULL
GROUP BY DATE(last_sign_in_at)
ORDER BY date DESC;

-- Vue admin_module_stats (statistiques par module)
DROP VIEW IF EXISTS public.admin_module_stats;

CREATE OR REPLACE VIEW public.admin_module_stats AS
SELECT
  module_id,
  COUNT(*) FILTER (WHERE status IN ('in_progress', 'completed')) as total_started,
  COUNT(*) FILTER (WHERE status = 'completed') as total_completed,
  CASE
    WHEN COUNT(*) FILTER (WHERE status IN ('in_progress', 'completed')) > 0
    THEN ROUND((COUNT(*) FILTER (WHERE status = 'completed')::NUMERIC / COUNT(*) FILTER (WHERE status IN ('in_progress', 'completed'))) * 100)
    ELSE 0
  END as completion_rate,
  0 as avg_sections_completed
FROM public.user_module_progress
GROUP BY module_id
ORDER BY module_id;

-- ============================================
-- 4. PERMISSIONS SUR LES VUES
-- ============================================

-- Grant select sur les vues pour les utilisateurs authentifiés
-- (les RLS policies filtrent l'accès)
GRANT SELECT ON public.admin_user_list TO authenticated;
GRANT SELECT ON public.admin_preregistration_stats TO authenticated;
GRANT SELECT ON public.admin_daily_signups TO authenticated;
GRANT SELECT ON public.admin_daily_active TO authenticated;
GRANT SELECT ON public.admin_module_stats TO authenticated;

-- ============================================
-- 5. VERIFICATION
-- ============================================

SELECT 'Admin configuration complete!' as status;

-- =============================================
-- CRÉER LES VUES ADMIN MANQUANTES
-- Exécute ce SQL dans Supabase SQL Editor
-- =============================================

-- 0. S'assurer que la table pre_registrations a toutes les colonnes
ALTER TABLE public.pre_registrations ADD COLUMN IF NOT EXISTS converted_at TIMESTAMPTZ;

-- 1. Vue admin_user_list (liste des utilisateurs avec vraies stats)
DROP VIEW IF EXISTS public.admin_user_list;

CREATE OR REPLACE VIEW public.admin_user_list AS
SELECT
  u.id as user_id,
  u.email,
  COALESCE(p.full_name, u.raw_user_meta_data->>'full_name', u.raw_user_meta_data->>'name') as display_name,
  u.created_at as registered_at,
  u.last_sign_in_at,
  COALESCE(s.last_activity_at, u.last_sign_in_at) as last_activity,
  COALESCE(s.current_streak, 0) as current_streak,
  0 as total_sessions,
  COALESCE(
    (SELECT COUNT(*) FROM user_module_progress ump WHERE ump.user_id = u.id AND ump.status = 'in_progress'),
    0
  )::INTEGER as modules_in_progress_count,
  COALESCE(
    (SELECT ARRAY_AGG(module_id) FROM user_module_progress ump WHERE ump.user_id = u.id AND ump.status = 'completed'),
    ARRAY[]::INTEGER[]
  ) as modules_completed,
  true as email_reminders_enabled,
  false as admin_disabled_reminders,
  COALESCE(p.locale, 'fr') as preferred_language,
  a.role as admin_role,
  COALESCE(s.techniques_collected, 0) as collectibles_count,
  COALESCE(s.total_points, 0) as total_points,
  false as is_banned
FROM auth.users u
LEFT JOIN public.profiles p ON u.id = p.id
LEFT JOIN public.user_stats s ON u.id = s.user_id
LEFT JOIN public.admin_users a ON u.id = a.user_id
ORDER BY u.created_at DESC;

-- 2. Vue admin_preregistration_stats
DROP VIEW IF EXISTS public.admin_preregistration_stats;

CREATE OR REPLACE VIEW public.admin_preregistration_stats AS
SELECT
  COALESCE(locale, 'unknown') as locale,
  COUNT(*) as total,
  COUNT(*) FILTER (WHERE converted_at IS NOT NULL) as converted,
  COUNT(*) FILTER (WHERE converted_at IS NULL) as pending,
  MIN(created_at)::DATE as first_registration,
  MAX(created_at)::DATE as last_registration
FROM public.pre_registrations
GROUP BY locale
ORDER BY total DESC;

-- 3. Vue admin_daily_signups
DROP VIEW IF EXISTS public.admin_daily_signups;

CREATE OR REPLACE VIEW public.admin_daily_signups AS
SELECT
  DATE(created_at) as date,
  COUNT(*) as signups
FROM auth.users
WHERE created_at >= NOW() - INTERVAL '90 days'
GROUP BY DATE(created_at)
ORDER BY date DESC;

-- 4. Vue admin_daily_active
DROP VIEW IF EXISTS public.admin_daily_active;

CREATE OR REPLACE VIEW public.admin_daily_active AS
SELECT
  DATE(last_sign_in_at) as date,
  COUNT(DISTINCT id) as active_users
FROM auth.users
WHERE last_sign_in_at >= NOW() - INTERVAL '90 days'
  AND last_sign_in_at IS NOT NULL
GROUP BY DATE(last_sign_in_at)
ORDER BY date DESC;

-- Vérification
SELECT 'Views created with real user stats!' as status;

-- Create section_comments table for admin comments on sections
-- These comments are visible in the admin mind-map and on the module pages (admin only)

CREATE TABLE IF NOT EXISTS section_comments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  section_id TEXT NOT NULL,           -- ID de la section (ex: "introduction-a-la-desinformation")
  module_id INTEGER NOT NULL,         -- ID du module (1-6)
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  content TEXT NOT NULL,              -- Contenu Markdown
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Index pour recherche rapide par section
CREATE INDEX IF NOT EXISTS idx_section_comments_section ON section_comments(section_id);
CREATE INDEX IF NOT EXISTS idx_section_comments_module ON section_comments(module_id);
CREATE INDEX IF NOT EXISTS idx_section_comments_user ON section_comments(user_id);

-- Enable Row Level Security
ALTER TABLE section_comments ENABLE ROW LEVEL SECURITY;

-- Drop existing policies if they exist (for idempotency)
DROP POLICY IF EXISTS "Admins can read all comments" ON section_comments;
DROP POLICY IF EXISTS "Admins can insert comments" ON section_comments;
DROP POLICY IF EXISTS "Admins can update own comments" ON section_comments;
DROP POLICY IF EXISTS "Admins can delete own comments" ON section_comments;

-- RLS policies (admin only)
-- Admins can read all comments
CREATE POLICY "Admins can read all comments" ON section_comments
  FOR SELECT USING (
    EXISTS (SELECT 1 FROM admin_users WHERE user_id = auth.uid())
  );

-- Admins can insert comments
CREATE POLICY "Admins can insert comments" ON section_comments
  FOR INSERT WITH CHECK (
    EXISTS (SELECT 1 FROM admin_users WHERE user_id = auth.uid())
    AND user_id = auth.uid()
  );

-- Admins can update their own comments
CREATE POLICY "Admins can update own comments" ON section_comments
  FOR UPDATE USING (
    user_id = auth.uid()
    AND EXISTS (SELECT 1 FROM admin_users WHERE user_id = auth.uid())
  );

-- Admins can delete their own comments
CREATE POLICY "Admins can delete own comments" ON section_comments
  FOR DELETE USING (
    user_id = auth.uid()
    AND EXISTS (SELECT 1 FROM admin_users WHERE user_id = auth.uid())
  );

-- Function to automatically update updated_at timestamp
CREATE OR REPLACE FUNCTION update_section_comments_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to auto-update updated_at
DROP TRIGGER IF EXISTS section_comments_updated_at ON section_comments;
CREATE TRIGGER section_comments_updated_at
  BEFORE UPDATE ON section_comments
  FOR EACH ROW
  EXECUTE FUNCTION update_section_comments_updated_at();

-- Comments on table and columns
COMMENT ON TABLE section_comments IS 'Admin comments on module sections for internal review and notes';
COMMENT ON COLUMN section_comments.section_id IS 'Slug/ID of the section (e.g., "introduction-a-la-desinformation")';
COMMENT ON COLUMN section_comments.module_id IS 'Module ID (1-6 for main modules, 7+ for optional)';
COMMENT ON COLUMN section_comments.content IS 'Markdown formatted comment content';