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

-- Migration: Création de la table referrals pour gérer le programme de parrainage

CREATE TABLE IF NOT EXISTS referrals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Parrain (client existant)
  referrer_org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  referrer_user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  
  -- Filleul (prospect/nouveau client)
  referee_name VARCHAR(255) NOT NULL, -- Nom du filleul
  referee_email VARCHAR(255) NOT NULL, -- Email du filleul
  referee_org_id UUID REFERENCES organizations(id) ON DELETE SET NULL, -- Org créée si validation
  
  -- Statut du parrainage
  status VARCHAR(50) NOT NULL DEFAULT 'pending', -- pending, email_sent, signed, validated, cancelled
  
  -- Message personnalisé du parrain
  personal_message TEXT,
  
  -- Montants des réductions
  referrer_credit_amount DECIMAL(10, 2) DEFAULT 30.00, -- Crédit du parrain en € HT
  referee_discount_amount DECIMAL(10, 2) DEFAULT 20.00, -- Réduction du filleul en € HT
  
  -- Tracking
  email_sent_at TIMESTAMPTZ, -- Date d'envoi de l'email de parrainage
  contract_signed_at TIMESTAMPTZ, -- Date de signature du contrat par le filleul
  invoice_paid_at TIMESTAMPTZ, -- Date de paiement de la facture d'ouverture
  validated_at TIMESTAMPTZ, -- Date de validation du parrainage
  credit_applied_at TIMESTAMPTZ, -- Date d'application du crédit sur la facture du parrain
  
  -- Métadonnées
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL
);

-- Index pour performances
CREATE INDEX IF NOT EXISTS idx_referrals_referrer_org ON referrals(referrer_org_id);
CREATE INDEX IF NOT EXISTS idx_referrals_referrer_user ON referrals(referrer_user_id);
CREATE INDEX IF NOT EXISTS idx_referrals_referee_email ON referrals(referee_email);
CREATE INDEX IF NOT EXISTS idx_referrals_status ON referrals(status);
CREATE INDEX IF NOT EXISTS idx_referrals_created_at ON referrals(created_at DESC);

-- Trigger pour mettre à jour updated_at
CREATE OR REPLACE FUNCTION update_referrals_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_referrals_updated_at
  BEFORE UPDATE ON referrals
  FOR EACH ROW
  EXECUTE FUNCTION update_referrals_updated_at();

-- RLS: Les clients peuvent voir leurs propres parrainages
ALTER TABLE referrals ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Les clients voient leurs parrainages"
  ON referrals
  FOR SELECT
  USING (
    referrer_org_id IN (
      SELECT org_id 
      FROM organization_members 
      WHERE user_id = auth.uid() 
      AND revoked = false
    )
  );

CREATE POLICY "Les clients créent leurs parrainages"
  ON referrals
  FOR INSERT
  WITH CHECK (
    referrer_org_id IN (
      SELECT org_id 
      FROM organization_members 
      WHERE user_id = auth.uid() 
      AND revoked = false
    )
    AND referrer_user_id = auth.uid()
  );

-- RLS: Le staff peut tout gérer
CREATE POLICY "Staff peut tout gérer sur les parrainages"
  ON referrals
  FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM staff_users
      WHERE staff_users.user_id = auth.uid()
      AND staff_users.is_staff = true
    )
  );

-- Commentaires
COMMENT ON TABLE referrals IS 'Programme de parrainage Odentas';
COMMENT ON COLUMN referrals.status IS 'Statut: pending (créé), email_sent (email envoyé), signed (contrat signé), validated (facture payée), cancelled (annulé)';
COMMENT ON COLUMN referrals.referrer_credit_amount IS 'Montant du crédit accordé au parrain (30€ HT par défaut)';
COMMENT ON COLUMN referrals.referee_discount_amount IS 'Montant de la réduction pour le filleul (20€ HT par défaut)';