-- ╔════════════════════════════════════════════════════════════════════════════╗
-- ║  FIX FRAIS STRIPE - CORRECTION COMPLÈTE DU SYSTÈME DE PAIEMENT             ║
-- ║  Date: 15 Janvier 2026                                                      ║
-- ║  Exécuter sur phpMyAdmin - BASE DE DONNÉES PRODUCTION                       ║
-- ║                                                                              ║
-- ║  TABLES CONCERNÉES:                                                          ║
-- ║  - escrow_transactions (Services, UrgentSales, Rentals, Food)               ║
-- ║  - food_orders                                                               ║
-- ║  - urgent_sale_purchases                                                     ║
-- ║  - equipment_rental_requests                                                 ║
-- ╚════════════════════════════════════════════════════════════════════════════╝

-- ══════════════════════════════════════════════════════════════════════════════
-- PROBLÈME IDENTIFIÉ:
-- Quand commission = 0%, le code transférait total_amount au prestataire
-- alors que l'admin n'a reçu que (total_amount - frais_stripe) de Stripe.
-- EXEMPLE: FoodOrder #82 - Admin reçoit 5.67€, transfère 6€, perd 0.33€
--
-- SOLUTION:
-- prestataire_amount = total_amount - stripe_fees - commission
-- Même si commission = 0%, les frais Stripe doivent TOUJOURS être déduits.
-- ══════════════════════════════════════════════════════════════════════════════

-- ═══════════════════════════════════════════════════════════════════════════════
-- FORMULE FRAIS STRIPE (EU): 1.4% + 0.25€
-- stripe_fees = ROUND(total_amount * 0.014 + 0.25, 2)
-- ═══════════════════════════════════════════════════════════════════════════════

SET SQL_SAFE_UPDATES = 0;

-- ══════════════════════════════════════════════════════════════════════════════
-- ÉTAPE 1: AJOUTER LES COLONNES stripe_fees (SI ABSENTES)
-- ══════════════════════════════════════════════════════════════════════════════
-- NOTE: Exécuter chaque ALTER TABLE séparément. Si la colonne existe déjà,
-- MySQL affichera "Duplicate column name" - ignorez cette erreur et continuez.

-- 1.1 Pour escrow_transactions
ALTER TABLE `escrow_transactions` 
ADD COLUMN `stripe_fees` DECIMAL(10,2) DEFAULT 0.00 AFTER `commission_amount`;

-- 1.2 Pour food_orders
ALTER TABLE `food_orders` 
ADD COLUMN `stripe_fees` DECIMAL(10,2) DEFAULT 0.00 AFTER `platform_fee`;

-- 1.3 Pour urgent_sale_purchases (ignorer si erreur "Table doesn't exist")
ALTER TABLE `urgent_sale_purchases` 
ADD COLUMN `stripe_fees` DECIMAL(10,2) DEFAULT 0.00;

-- 1.4 Pour equipment_rental_requests (ignorer si erreur "Table doesn't exist")
ALTER TABLE `equipment_rental_requests` 
ADD COLUMN `stripe_fees` DECIMAL(10,2) DEFAULT 0.00;

-- ══════════════════════════════════════════════════════════════════════════════
-- ÉTAPE 2: CORRIGER LES ESCROW_TRANSACTIONS (TOUTES LES TRANSACTIONS)
-- ══════════════════════════════════════════════════════════════════════════════

-- 2.1 Calculer les frais Stripe pour chaque escrow
UPDATE `escrow_transactions` 
SET `stripe_fees` = ROUND(`total_amount` * 0.014 + 0.25, 2)
WHERE (`stripe_fees` = 0 OR `stripe_fees` IS NULL)
  AND `stripe_payment_intent_id` IS NOT NULL;

-- 2.2 Recalculer prestataire_amount pour les escrows non finalisés
-- FORMULE: total - stripe_fees - commission
UPDATE `escrow_transactions`
SET `prestataire_amount` = GREATEST(0, ROUND(`total_amount` - COALESCE(`stripe_fees`, 0) - COALESCE(`commission_amount`, 0), 2)),
    `remaining_amount` = GREATEST(0, ROUND(`total_amount` - COALESCE(`stripe_fees`, 0) - COALESCE(`commission_amount`, 0), 2))
WHERE `status` IN ('pending', 'held', 'partial');

-- 2.3 Corriger les escrows déjà released qui avaient commission = 0 (BUG principal)
-- Ces transactions ont transféré trop d'argent au prestataire
UPDATE `escrow_transactions`
SET `stripe_fees` = ROUND(`total_amount` * 0.014 + 0.25, 2)
WHERE `status` = 'released'
  AND `commission_amount` = 0
  AND (`stripe_fees` = 0 OR `stripe_fees` IS NULL);

-- ══════════════════════════════════════════════════════════════════════════════
-- ÉTAPE 3: CORRIGER LES FOOD_ORDERS
-- ══════════════════════════════════════════════════════════════════════════════

-- 3.1 Calculer les frais Stripe (basé sur subtotal = prix des plats)
UPDATE `food_orders`
SET `stripe_fees` = ROUND(`subtotal` * 0.014 + 0.25, 2)
WHERE (`stripe_fees` = 0 OR `stripe_fees` IS NULL)
  AND `payment_method` NOT IN ('cash', 'especes')
  AND `stripe_payment_intent_id` IS NOT NULL;

-- 3.2 Recalculer prestataire_payout pour les commandes non complétées
-- FORMULE: subtotal - stripe_fees - platform_fee
UPDATE `food_orders`
SET `prestataire_payout` = GREATEST(0, ROUND(`subtotal` - COALESCE(`stripe_fees`, 0) - COALESCE(`platform_fee`, 0), 2))
WHERE `status` NOT IN ('completed', 'cancelled')
  AND `payment_method` NOT IN ('cash', 'especes');

-- 3.3 Pour les commandes cash, stripe_fees = 0
UPDATE `food_orders`
SET `stripe_fees` = 0
WHERE `payment_method` IN ('cash', 'especes');

-- ══════════════════════════════════════════════════════════════════════════════
-- ÉTAPE 4: CORRIGER LES URGENT_SALE_PURCHASES (Ventes Flash)
-- ══════════════════════════════════════════════════════════════════════════════

UPDATE `urgent_sale_purchases`
SET `stripe_fees` = ROUND(`final_price` * 0.014 + 0.25, 2)
WHERE (`stripe_fees` = 0 OR `stripe_fees` IS NULL)
  AND `payment_method` NOT IN ('cash', 'especes');

-- ══════════════════════════════════════════════════════════════════════════════
-- ÉTAPE 5: CORRIGER LES EQUIPMENT_RENTAL_REQUESTS (Locations)
-- ══════════════════════════════════════════════════════════════════════════════

UPDATE `equipment_rental_requests`
SET `stripe_fees` = ROUND(`total_price` * 0.014 + 0.25, 2)
WHERE (`stripe_fees` = 0 OR `stripe_fees` IS NULL)
  AND `payment_status` != 'pending';

-- ══════════════════════════════════════════════════════════════════════════════
-- ÉTAPE 6: VÉRIFICATIONS
-- ══════════════════════════════════════════════════════════════════════════════

-- 6.1 Vérifier les escrow_transactions
SELECT 
    'ESCROW TRANSACTIONS' AS table_name,
    COUNT(*) AS total,
    SUM(CASE WHEN stripe_fees > 0 THEN 1 ELSE 0 END) AS with_stripe_fees,
    SUM(CASE WHEN commission_amount = 0 AND stripe_fees > 0 THEN 1 ELSE 0 END) AS zero_commission_with_fees
FROM escrow_transactions;

-- 6.2 Détail escrow (10 derniers)
SELECT 
    id,
    SUBSTRING(escrowable_type, -20) AS type,
    total_amount,
    stripe_fees,
    commission_amount,
    prestataire_amount,
    ROUND(total_amount - stripe_fees - commission_amount, 2) AS calcul_verifie,
    status
FROM escrow_transactions
ORDER BY id DESC
LIMIT 10;

-- 6.3 Vérifier les food_orders
SELECT 
    'FOOD ORDERS' AS table_name,
    COUNT(*) AS total,
    SUM(CASE WHEN stripe_fees > 0 THEN 1 ELSE 0 END) AS with_stripe_fees,
    SUM(CASE WHEN payment_method = 'cash' THEN 1 ELSE 0 END) AS cash_orders
FROM food_orders;

-- 6.4 Détail food_orders (10 derniers paiements online)
SELECT 
    id,
    order_number,
    subtotal,
    stripe_fees,
    platform_fee,
    prestataire_payout,
    ROUND(subtotal - COALESCE(stripe_fees, 0) - COALESCE(platform_fee, 0), 2) AS calcul_verifie,
    status,
    payment_method
FROM food_orders
WHERE payment_method NOT IN ('cash', 'especes')
ORDER BY id DESC
LIMIT 10;

SET SQL_SAFE_UPDATES = 1;

-- ══════════════════════════════════════════════════════════════════════════════
-- DOCUMENTATION: COMMISSIONS EN PRODUCTION (valeurs réelles de cache)
-- ══════════════════════════════════════════════════════════════════════════════

/*
╔════════════════════════════════════════════════════════════════════════════════╗
║                    SYSTÈME DE PAIEMENT CORRIGÉ                                 ║
╠════════════════════════════════════════════════════════════════════════════════╣
║                                                                                ║
║  CLIENT PAIE: 100€                                                             ║
║       ↓                                                                        ║
║  STRIPE PRÉLÈVE: 1.65€ (1.4% + 0.25€)                                         ║
║       ↓                                                                        ║
║  ADMIN REÇOIT SUR STRIPE: 98.35€                                              ║
║       ↓                                                                        ║
║  ┌────────────────────────────────────────────────────────────────────────┐   ║
║  │ Répartition (ex: Services avec 10% commission):                        │   ║
║  │                                                                        │   ║
║  │   • Admin garde     = 10€ (commission)                                 │   ║
║  │   • Presta reçoit   = 98.35€ - 10€ = 88.35€                           │   ║
║  │                                                                        │   ║
║  │ Le prestataire paie les frais Stripe (déduits de son payout)          │   ║
║  └────────────────────────────────────────────────────────────────────────┘   ║
║                                                                                ║
║  FORMULE UNIVERSELLE:                                                          ║
║  ┌─────────────────────────────────────────────────────────────────────────┐  ║
║  │  prestataire_receives = total - stripe_fees - commission                │  ║
║  │  admin_keeps = commission                                               │  ║
║  └─────────────────────────────────────────────────────────────────────────┘  ║
║                                                                                ║
╠════════════════════════════════════════════════════════════════════════════════╣
║                        COMMISSIONS EN PRODUCTION                               ║
║                   (valeurs réelles de la table cache)                          ║
╠════════════════════════════════════════════════════════════════════════════════╣
║                                                                                ║
║  Type             │ Commission │ Stripe    │ Sur 100€ client paie:             ║
║  ─────────────────┼────────────┼───────────┼───────────────────────────────────║
║  Services         │    10%     │   1.65€   │ Presta = 100 - 1.65 - 10 = 88.35€ ║
║  Locations        │     0%     │   1.65€   │ Presta = 100 - 1.65 - 0 = 98.35€  ║
║  Ventes Flash     │     0%     │   1.65€   │ Presta = 100 - 1.65 - 0 = 98.35€  ║
║  Food             │     2%     │   1.65€   │ Presta = 100 - 1.65 - 2 = 96.35€  ║
║  Livreur          │     0%     │   ~0.30€  │ Sur 3€ livraison = 3 - 0.29 = 2.71€║
║                                                                                ║
╠════════════════════════════════════════════════════════════════════════════════╣
║                        EXEMPLE FOOD ORDER #82                                  ║
╠════════════════════════════════════════════════════════════════════════════════╣
║                                                                                ║
║  AVANT (BUG):                                                                  ║
║  • Client paie: 6€                                                             ║
║  • Stripe prélève: 0.33€ → Admin reçoit 5.67€                                 ║
║  • Commission food (2%): 0.12€                                                 ║
║  • Code transfert: 6€ - 0.12€ = 5.88€ au presta (ERREUR!)                     ║
║  • Admin perd: 5.88€ - 5.67€ = 0.21€ ❌                                        ║
║                                                                                ║
║  APRÈS (CORRIGÉ):                                                              ║
║  • Client paie: 6€                                                             ║
║  • Stripe prélève: 0.33€ → Admin reçoit 5.67€                                 ║
║  • Commission food (2%): 0.12€                                                 ║
║  • Code transfert: 6€ - 0.33€ - 0.12€ = 5.55€ au presta ✓                     ║
║  • Admin garde: 0.12€ (sa commission) ✓                                        ║
║                                                                                ║
╚════════════════════════════════════════════════════════════════════════════════╝
*/
