espace-paie-odentas/supabase/migrations/001_create_email_logs.sql

-- Migration Supabase pour la table email_logs détaillée
-- À exécuter dans l'éditeur SQL de Supabase

-- Créer un type enum pour les statuts d'email (si n'existe pas déjà)
DO $$ BEGIN
  CREATE TYPE email_status AS ENUM (
    'pending',
    'sending', 
    'sent',
    'delivered',
    'bounce',
    'complaint',
    'failed',
    'bounced',
    'complained'
  );
EXCEPTION
  WHEN duplicate_object THEN null;
END $$;

-- Créer un type enum pour les types d'email (si n'existe pas déjà)
DO $$ BEGIN
  CREATE TYPE email_type AS ENUM (
    'contract_signature',
    'password_reset',
    'account_activation',
    'invoice_generated',
    'system_notification',
    'bulk_communication',
    'contract-created',
    'contract-updated', 
    'contract-cancelled',
    'employee-created',
    'invitation',
    'auto-declaration-invitation',
    'invoice',
    'signature-request',
    'signature-request-employer',
    'signature-request-employee',
    'notification',
    'access-updated',
    'access-revoked',
    'password-created',
    'password-changed',
    'twofa-enabled',
    'twofa-disabled',
    'bulk-email',
    'other'
  );
EXCEPTION
  WHEN duplicate_object THEN null;
END $$;

-- Créer la table email_logs
CREATE TABLE IF NOT EXISTS email_logs (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  
  -- Informations temporelles
  created_at timestamptz DEFAULT now() NOT NULL,
  sent_at timestamptz,
  delivered_at timestamptz,
  
  -- Informations utilisateur
  sender_user_id uuid REFERENCES auth.users(id),
  sender_name text,
  sender_email text NOT NULL,
  
  -- Destinataires
  recipient_email text NOT NULL,
  cc_emails text[], -- Array pour les emails en copie
  bcc_emails text[], -- Array pour les emails en copie cachée
  
  -- Contenu email
  subject text NOT NULL,
  html_content text,
  text_content text,
  template_name text,
  template_data jsonb,
  
  -- Métadonnées
  email_type email_type NOT NULL,
  email_status email_status DEFAULT 'pending' NOT NULL,
  
  -- Données SES
  ses_message_id text, -- ID retourné par SES
  ses_region text,
  ses_configuration_set text,
  ses_source_arn text,
  ses_return_path text,
  
  -- Informations techniques
  user_agent text,
  ip_address inet,
  organization_id uuid,
  contract_id uuid,
  ticket_id uuid,
  
  -- Métriques et événements
  bounce_reason text,
  complaint_reason text,
  failure_reason text,
  delivery_delay interval,
  
  -- Métadonnées additionnelles
  tags jsonb DEFAULT '{}', -- Tags personnalisés pour le filtrage
  context jsonb DEFAULT '{}', -- Contexte supplémentaire
  
  -- Index pour performance
  CONSTRAINT email_logs_sender_email_check CHECK (sender_email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
  CONSTRAINT email_logs_recipient_email_check CHECK (recipient_email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

-- Créer les index pour optimiser les requêtes
CREATE INDEX IF NOT EXISTS idx_email_logs_created_at ON email_logs(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_email_logs_sender_email ON email_logs(sender_email);
CREATE INDEX IF NOT EXISTS idx_email_logs_recipient_email ON email_logs(recipient_email);
CREATE INDEX IF NOT EXISTS idx_email_logs_email_type ON email_logs(email_type);
CREATE INDEX IF NOT EXISTS idx_email_logs_email_status ON email_logs(email_status);
CREATE INDEX IF NOT EXISTS idx_email_logs_organization_id ON email_logs(organization_id);
CREATE INDEX IF NOT EXISTS idx_email_logs_ses_message_id ON email_logs(ses_message_id);
CREATE INDEX IF NOT EXISTS idx_email_logs_subject ON email_logs USING gin(to_tsvector('french', subject));

-- Index composite pour les requêtes fréquentes
CREATE INDEX IF NOT EXISTS idx_email_logs_status_created ON email_logs(email_status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_email_logs_type_created ON email_logs(email_type, created_at DESC);

-- Politique RLS (Row Level Security)
ALTER TABLE email_logs ENABLE ROW LEVEL SECURITY;

-- Supprimer les politiques existantes si elles existent
DROP POLICY IF EXISTS "Staff can view all email logs" ON email_logs;
DROP POLICY IF EXISTS "Users can view their own email logs" ON email_logs;
DROP POLICY IF EXISTS "System can insert email logs" ON email_logs;
DROP POLICY IF EXISTS "System can update email logs" ON email_logs;

-- Politique pour les utilisateurs staff
CREATE POLICY "Staff can view all email logs" ON email_logs
  FOR SELECT 
  USING (
    EXISTS (
      SELECT 1 FROM staff_users 
      WHERE staff_users.user_id = auth.uid() 
      AND staff_users.is_staff = true
    )
  );

-- Politique pour les utilisateurs normaux (leurs propres emails uniquement)
CREATE POLICY "Users can view their own email logs" ON email_logs
  FOR SELECT 
  USING (
    sender_user_id = auth.uid()
  );

-- Politique d'insertion pour le système
CREATE POLICY "System can insert email logs" ON email_logs
  FOR INSERT 
  WITH CHECK (true); -- Autorise toutes les insertions (géré par service role)

-- Politique de mise à jour pour le système
CREATE POLICY "System can update email logs" ON email_logs
  FOR UPDATE 
  USING (true) 
  WITH CHECK (true); -- Autorise toutes les mises à jour (géré par service role)

-- Fonction pour mettre à jour automatiquement les statuts SES
CREATE OR REPLACE FUNCTION update_email_status_from_ses()
RETURNS trigger AS $$
BEGIN
  -- Auto-remplir sent_at si le statut devient 'sent'
  IF NEW.email_status = 'sent' AND OLD.email_status != 'sent' THEN
    NEW.sent_at = COALESCE(NEW.sent_at, now());
  END IF;
  
  -- Auto-remplir delivered_at si le statut devient 'delivered'
  IF NEW.email_status = 'delivered' AND OLD.email_status != 'delivered' THEN
    NEW.delivered_at = COALESCE(NEW.delivered_at, now());
  END IF;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Créer le trigger
CREATE TRIGGER trigger_update_email_status 
  BEFORE UPDATE ON email_logs
  FOR EACH ROW 
  EXECUTE FUNCTION update_email_status_from_ses();

-- Vue pour les statistiques rapides
CREATE OR REPLACE VIEW email_stats_summary AS
SELECT 
  email_type,
  email_status,
  DATE(created_at) as date,
  COUNT(*) as count,
  COUNT(CASE WHEN email_status = 'sent' THEN 1 END) as sent_count,
  COUNT(CASE WHEN email_status = 'delivered' THEN 1 END) as delivered_count,
  COUNT(CASE WHEN email_status = 'bounce' THEN 1 END) as bounce_count,
  COUNT(CASE WHEN email_status = 'complaint' THEN 1 END) as complaint_count,
  COUNT(CASE WHEN email_status = 'failed' THEN 1 END) as failed_count,
  AVG(EXTRACT(EPOCH FROM (sent_at - created_at))) as avg_send_delay_seconds
FROM email_logs 
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY email_type, email_status, DATE(created_at)
ORDER BY date DESC, email_type;

-- Vue pour les emails récents avec détails
CREATE OR REPLACE VIEW recent_emails AS
SELECT 
  id,
  created_at,
  sent_at,
  sender_email,
  recipient_email,
  cc_emails,
  subject,
  email_type,
  email_status,
  ses_message_id,
  organization_id,
  failure_reason,
  bounce_reason,
  complaint_reason
FROM email_logs 
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY created_at DESC
LIMIT 1000;

-- Commentaires pour documentation
COMMENT ON TABLE email_logs IS 'Table de logs détaillée pour tous les emails envoyés par l''Espace Paie';
COMMENT ON COLUMN email_logs.ses_message_id IS 'ID unique retourné par AWS SES lors de l''envoi';
COMMENT ON COLUMN email_logs.template_data IS 'Données JSON utilisées pour remplir le template d''email';
COMMENT ON COLUMN email_logs.tags IS 'Tags personnalisés pour le filtrage et la catégorisation';
COMMENT ON COLUMN email_logs.context IS 'Contexte additionnel (URL, user agent, etc.)';
COMMENT ON COLUMN email_logs.delivery_delay IS 'Délai entre l''envoi et la livraison';

-- Accorder les permissions nécessaires
GRANT SELECT, INSERT, UPDATE ON email_logs TO authenticated;
GRANT SELECT ON email_stats_summary TO authenticated;
GRANT SELECT ON recent_emails TO authenticated;