espace-paie-odentas/migrations/add_salary_transfer_payslips_table.sql

-- Migration: Ajout du système de sélection manuelle de paies pour les virements
-- Date: 2025-11-28
-- Description: Permet de créer des virements pour des paies spécifiques au lieu d'une période complète

-- 1. Ajouter le champ selection_mode à salary_transfers
ALTER TABLE salary_transfers 
ADD COLUMN IF NOT EXISTS selection_mode TEXT DEFAULT 'period' 
CHECK (selection_mode IN ('period', 'manual'));

-- Commentaire pour documenter le champ
COMMENT ON COLUMN salary_transfers.selection_mode IS 
'Mode de sélection des paies: "period" (toutes les paies du mois) ou "manual" (paies sélectionnées manuellement)';

-- 2. Créer la table de liaison salary_transfer_payslips
CREATE TABLE IF NOT EXISTS salary_transfer_payslips (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  salary_transfer_id UUID NOT NULL REFERENCES salary_transfers(id) ON DELETE CASCADE,
  payslip_id UUID NOT NULL REFERENCES payslips(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  
  -- Empêcher les doublons
  UNIQUE(salary_transfer_id, payslip_id)
);

-- Index pour améliorer les performances
CREATE INDEX IF NOT EXISTS idx_salary_transfer_payslips_transfer_id 
ON salary_transfer_payslips(salary_transfer_id);

CREATE INDEX IF NOT EXISTS idx_salary_transfer_payslips_payslip_id 
ON salary_transfer_payslips(payslip_id);

-- Commentaire pour documenter la table
COMMENT ON TABLE salary_transfer_payslips IS 
'Table de liaison entre les virements de salaires et les paies spécifiques (mode manual uniquement)';

-- 3. Row Level Security (RLS) pour salary_transfer_payslips
ALTER TABLE salary_transfer_payslips ENABLE ROW LEVEL SECURITY;

-- Policy pour les staff users (lecture/écriture complète)
CREATE POLICY "Staff users can manage salary transfer payslips"
ON salary_transfer_payslips
FOR ALL
USING (
  auth.uid() IN (
    SELECT user_id FROM staff_users WHERE is_staff = true
  )
)
WITH CHECK (
  auth.uid() IN (
    SELECT user_id FROM staff_users WHERE is_staff = true
  )
);

-- 4. Activer Realtime pour salary_transfer_payslips
ALTER PUBLICATION supabase_realtime ADD TABLE salary_transfer_payslips;

-- 5. Fonction helper pour récupérer les paies d'un virement (utile pour les requêtes)
CREATE OR REPLACE FUNCTION get_salary_transfer_payslips(transfer_id UUID)
RETURNS TABLE (
  payslip_id UUID,
  contract_id UUID,
  employee_name TEXT,
  net_amount NUMERIC,
  net_after_withholding NUMERIC,
  period_month DATE
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    p.id,
    p.contract_id,
    COALESCE(
      c.employee_name,
      CONCAT(s.prenom, ' ', s.nom)
    ) as employee_name,
    p.net_amount,
    p.net_after_withholding,
    p.period_month
  FROM salary_transfer_payslips stp
  JOIN payslips p ON p.id = stp.payslip_id
  LEFT JOIN cddu_contracts c ON c.id = p.contract_id
  LEFT JOIN salaries s ON s.salarie = c.employee_id
  WHERE stp.salary_transfer_id = transfer_id
  ORDER BY employee_name;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 6. Vue pour faciliter les requêtes sur les virements avec leurs paies
CREATE OR REPLACE VIEW v_salary_transfers_with_payslips AS
SELECT 
  st.id as transfer_id,
  st.org_id,
  st.period_month,
  st.period_label,
  st.selection_mode,
  st.mode,
  st.deadline,
  st.num_appel,
  st.total_net,
  st.callsheet_url,
  st.notification_sent,
  st.notification_ok,
  st.salaires_payes,
  o.name as organization_name,
  CASE 
    WHEN st.selection_mode = 'manual' THEN (
      SELECT COUNT(*) 
      FROM salary_transfer_payslips 
      WHERE salary_transfer_id = st.id
    )
    ELSE (
      SELECT COUNT(*) 
      FROM payslips 
      WHERE organization_id = st.org_id 
      AND period_month = st.period_month
    )
  END as payslips_count,
  CASE 
    WHEN st.selection_mode = 'manual' THEN (
      SELECT COALESCE(SUM(net_after_withholding), 0)
      FROM payslips p
      JOIN salary_transfer_payslips stp ON stp.payslip_id = p.id
      WHERE stp.salary_transfer_id = st.id
    )
    ELSE (
      SELECT COALESCE(SUM(net_after_withholding), 0)
      FROM payslips 
      WHERE organization_id = st.org_id 
      AND period_month = st.period_month
    )
  END as calculated_total_net
FROM salary_transfers st
LEFT JOIN organizations o ON o.id = st.org_id;

-- Commentaire pour documenter la vue
COMMENT ON VIEW v_salary_transfers_with_payslips IS 
'Vue enrichie des virements avec le nombre de paies et le total calculé selon le mode de sélection';

-- 7. Vérifications post-migration
DO $$
BEGIN
  -- Vérifier que la colonne selection_mode existe
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name = 'salary_transfers' 
    AND column_name = 'selection_mode'
  ) THEN
    RAISE EXCEPTION 'Colonne selection_mode non créée';
  END IF;

  -- Vérifier que la table salary_transfer_payslips existe
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.tables 
    WHERE table_name = 'salary_transfer_payslips'
  ) THEN
    RAISE EXCEPTION 'Table salary_transfer_payslips non créée';
  END IF;

  RAISE NOTICE 'Migration réussie: salary_transfer_payslips table et selection_mode ajoutés';
END $$;