espace-paie-odentas/supabase/migrations/20251031_create_bulk_email_drafts.sql

-- Migration: Création de la table pour les brouillons d'emails groupés
-- Date: 2025-10-31

-- Créer la table bulk_email_drafts
CREATE TABLE IF NOT EXISTS bulk_email_drafts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  
  -- Identité de l'auteur (staff uniquement)
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  
  -- Métadonnées du brouillon
  draft_name TEXT NOT NULL,
  
  -- Contenu de l'email
  subject TEXT NOT NULL,
  email_title TEXT NOT NULL,
  email_greeting TEXT NOT NULL,
  email_intro TEXT NOT NULL,
  content_title TEXT NOT NULL,
  content_body TEXT NOT NULL,
  
  -- Options
  show_alert BOOLEAN DEFAULT false,
  alert_title TEXT,
  alert_text TEXT,
  show_button BOOLEAN DEFAULT false,
  button_text TEXT,
  button_url TEXT,
  closing_message TEXT NOT NULL,
  
  -- Filtres de destinataires (optionnels, pour mémoriser la sélection)
  filters JSONB DEFAULT '{}'::jsonb
);

-- Créer des index
CREATE INDEX IF NOT EXISTS idx_bulk_email_drafts_user_id ON bulk_email_drafts(user_id);
CREATE INDEX IF NOT EXISTS idx_bulk_email_drafts_created_at ON bulk_email_drafts(created_at DESC);

-- Activer RLS (Row Level Security)
ALTER TABLE bulk_email_drafts ENABLE ROW LEVEL SECURITY;

-- Politique : Les utilisateurs staff peuvent voir uniquement leurs propres brouillons
DROP POLICY IF EXISTS "Staff users can view their own drafts" ON bulk_email_drafts;
CREATE POLICY "Staff users can view their own drafts"
  ON bulk_email_drafts
  FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM staff_users
      WHERE staff_users.user_id = auth.uid()
      AND staff_users.is_staff = true
    )
    AND user_id = auth.uid()
  );

-- Politique : Les utilisateurs staff peuvent créer leurs propres brouillons
DROP POLICY IF EXISTS "Staff users can create their own drafts" ON bulk_email_drafts;
CREATE POLICY "Staff users can create their own drafts"
  ON bulk_email_drafts
  FOR INSERT
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM staff_users
      WHERE staff_users.user_id = auth.uid()
      AND staff_users.is_staff = true
    )
    AND user_id = auth.uid()
  );

-- Politique : Les utilisateurs staff peuvent modifier leurs propres brouillons
DROP POLICY IF EXISTS "Staff users can update their own drafts" ON bulk_email_drafts;
CREATE POLICY "Staff users can update their own drafts"
  ON bulk_email_drafts
  FOR UPDATE
  USING (
    EXISTS (
      SELECT 1 FROM staff_users
      WHERE staff_users.user_id = auth.uid()
      AND staff_users.is_staff = true
    )
    AND user_id = auth.uid()
  );

-- Politique : Les utilisateurs staff peuvent supprimer leurs propres brouillons
DROP POLICY IF EXISTS "Staff users can delete their own drafts" ON bulk_email_drafts;
CREATE POLICY "Staff users can delete their own drafts"
  ON bulk_email_drafts
  FOR DELETE
  USING (
    EXISTS (
      SELECT 1 FROM staff_users
      WHERE staff_users.user_id = auth.uid()
      AND staff_users.is_staff = true
    )
    AND user_id = auth.uid()
  );

-- Fonction pour mettre à jour automatiquement updated_at
-- On vérifie d'abord si elle existe pour éviter les conflits
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_proc WHERE proname = 'update_bulk_email_drafts_updated_at'
  ) THEN
    CREATE FUNCTION update_bulk_email_drafts_updated_at()
    RETURNS TRIGGER AS $func$
    BEGIN
      NEW.updated_at = now();
      RETURN NEW;
    END;
    $func$ LANGUAGE plpgsql;
  END IF;
END $$;

-- Trigger pour mettre à jour updated_at automatiquement
DROP TRIGGER IF EXISTS bulk_email_drafts_updated_at_trigger ON bulk_email_drafts;
CREATE TRIGGER bulk_email_drafts_updated_at_trigger
  BEFORE UPDATE ON bulk_email_drafts
  FOR EACH ROW
  EXECUTE FUNCTION update_bulk_email_drafts_updated_at();

-- Commentaires pour la documentation
COMMENT ON TABLE bulk_email_drafts IS 'Table pour stocker les brouillons d''emails groupés créés par le staff';
COMMENT ON COLUMN bulk_email_drafts.user_id IS 'ID de l''utilisateur staff qui a créé le brouillon';
COMMENT ON COLUMN bulk_email_drafts.draft_name IS 'Nom du brouillon pour identification';
COMMENT ON COLUMN bulk_email_drafts.filters IS 'Filtres de destinataires au format JSON (roleFilter, orgFilter, searchTerm)';