<?php
include_once 'bd/myData.php';


function updateInstallmentPayments($id_vente, $new_total) {
    try {
        // First check if there's an installment payment record
        $checkQuery = "SELECT pct.*, v.type_paiement 
                      FROM paiements_clients_tranches pct
                      JOIN ventes v ON v.id_vente = pct.id_vente 
                      WHERE pct.id_vente = :id_vente";
        $result = metodGet($checkQuery, [':id_vente' => $id_vente]);
        $payment = $result->fetch(PDO::FETCH_ASSOC);
        
        if (!$payment) {
            return null; // No installment payment found
        }

        if ($payment['type_paiement'] !== 'Tranche') {
            return null; // Not an installment payment
        }

        // Get sum of paid installments
        $paidQuery = "SELECT 
                        COALESCE(SUM(montant), 0) as total_paid,
                        COUNT(*) as total_installments,
                        SUM(CASE WHEN statut_reglements = 'Payé' THEN 1 ELSE 0 END) as paid_installments
                     FROM reglements_tranches_clients
                     WHERE id_paiement_tranche = :id_paiement_tranche
                     AND statut_reglements != 'Annulé'";
        $paidResult = metodGet($paidQuery, [':id_paiement_tranche' => $payment['id_paiement_tranche']]);
        $paidData = $paidResult->fetch(PDO::FETCH_ASSOC);
        
        $totalPaid = floatval($paidData['total_paid']);
        $totalInstallments = intval($paidData['total_installments']);
        $paidInstallments = intval($paidData['paid_installments']);

        // Calculate if refund is needed
        $refundNeeded = $totalPaid > $new_total;
        $refundAmount = $refundNeeded ? $totalPaid - $new_total : 0;

        // Begin transaction
        $pdo = getConnection();
        $pdo->beginTransaction();

        try {
            // Update the total amount in paiements_clients_tranches
            $updateQuery = "UPDATE paiements_clients_tranches 
                          SET montant_total = :new_total,
                              montant_par_tranche = :montant_par_tranche,
                              date_upd = CURRENT_TIMESTAMP,
                              user_upd = :user_upd
                          WHERE id_paiement_tranche = :id_paiement_tranche";

            $remainingInstallments = $totalInstallments - $paidInstallments;
            $montantParTranche = $remainingInstallments > 0 ? 
                               ($new_total - $totalPaid) / $remainingInstallments : 
                               0;

            metodPut($updateQuery, [
                ':new_total' => $new_total,
                ':montant_par_tranche' => $montantParTranche,
                ':user_upd' => 'SYSTEM',
                ':id_paiement_tranche' => $payment['id_paiement_tranche']
            ], 'paiements_clients_tranches');

            // If there's a significant change in the total amount
            if (abs($payment['montant_total'] - $new_total) > 0.01) {
                recalculateInstallments($payment['id_paiement_tranche'], $new_total, $totalPaid);
                updatePaymentStatuses($payment['id_paiement_tranche'], $new_total);
            }

            $pdo->commit();

            return [
                'refund_needed' => $refundNeeded,
                'refund_amount' => $refundAmount,
                'old_total' => $payment['montant_total'],
                'new_total' => $new_total,
                'id_paiement_tranche' => $payment['id_paiement_tranche'],
                'total_paid' => $totalPaid,
                'remaining_amount' => $new_total - $totalPaid,
                'paid_installments' => $paidInstallments,
                'total_installments' => $totalInstallments,
                'montant_par_tranche' => $montantParTranche
            ];

        } catch (Exception $e) {
            $pdo->rollBack();
            throw $e;
        }
    } catch (Exception $e) {
        error_log("Error in updateInstallmentPayments: " . $e->getMessage());
        throw new Exception("Error updating installment payments: " . $e->getMessage());
    }
}

/**
 * Recalculates remaining installments based on new total
 */
function recalculateInstallments($id_paiement_tranche, $new_total, $total_paid = null) {
    try {
        // If total_paid wasn't provided, calculate it
        if ($total_paid === null) {
            $paidQuery = "SELECT COALESCE(SUM(montant), 0) as total_paid
                         FROM reglements_tranches_clients
                         WHERE id_paiement_tranche = :id_paiement_tranche
                         AND statut_reglements = 'Payé'";
            $paidResult = metodGet($paidQuery, [':id_paiement_tranche' => $id_paiement_tranche]);
            $total_paid = $paidResult->fetch(PDO::FETCH_ASSOC)['total_paid'];
        }

        // Get all unpaid installments
        $query = "SELECT * 
                 FROM reglements_tranches_clients 
                 WHERE id_paiement_tranche = :id_paiement_tranche 
                 AND statut_reglements IN ('En attente', 'En retard')
                 ORDER BY numero_tranche";
        $result = metodGet($query, [':id_paiement_tranche' => $id_paiement_tranche]);
        $unpaidInstallments = $result->fetchAll(PDO::FETCH_ASSOC);

        // Calculate remaining amount to be distributed
        $remainingAmount = $new_total - $total_paid;
        
        if ($remainingAmount <= 0) {
            // Cancel all unpaid installments
            $updateQuery = "UPDATE reglements_tranches_clients 
                          SET statut_reglements = 'Annulé',
                              montant = 0,
                              commentaire = CONCAT(COALESCE(commentaire, ''), 
                                                ' - Annulé suite à modification du montant total le ', 
                                                CURRENT_TIMESTAMP),
                              user_upd = 'SYSTEM',
                              date_upd = CURRENT_TIMESTAMP
                          WHERE id_paiement_tranche = :id_paiement_tranche 
                          AND statut_reglements IN ('En attente', 'En retard')";
            metodPut($updateQuery, [':id_paiement_tranche' => $id_paiement_tranche], 
                    'reglements_tranches_clients');
            
            // Update parent payment record status
            updatePaymentStatuses($id_paiement_tranche, $new_total);
            return;
        }

        // Distribute remaining amount among unpaid installments
        $countUnpaid = count($unpaidInstallments);
        if ($countUnpaid > 0) {
            $amountPerInstallment = $remainingAmount / $countUnpaid;
            
            foreach ($unpaidInstallments as $installment) {
                $updateQuery = "UPDATE reglements_tranches_clients 
                              SET montant = :montant,
                                  user_upd = 'SYSTEM',
                                  date_upd = CURRENT_TIMESTAMP,
                                  commentaire = CONCAT(COALESCE(commentaire, ''), 
                                                    ' - Montant recalculé le ', 
                                                    CURRENT_TIMESTAMP)
                              WHERE id_reglement = :id_reglement";
                metodPut($updateQuery, [
                    ':montant' => $amountPerInstallment,
                    ':id_reglement' => $installment['id_reglement']
                ], 'reglements_tranches_clients');
            }
        }

        // Update parent payment record status
        updatePaymentStatuses($id_paiement_tranche, $new_total);
    } catch (Exception $e) {
        error_log("Error in recalculateInstallments: " . $e->getMessage());
        throw new Exception("Error recalculating installments: " . $e->getMessage());
    }
}

/**
 * Updates payment statuses across related tables
 */
function updatePaymentStatuses($id_paiement_tranche, $new_total) {
    try {
        // Get current payment and sale info
        $query = "SELECT pct.*, v.id_vente 
                 FROM paiements_clients_tranches pct
                 JOIN ventes v ON v.id_vente = pct.id_vente
                 WHERE pct.id_paiement_tranche = :id_paiement_tranche";
        $result = metodGet($query, [':id_paiement_tranche' => $id_paiement_tranche]);
        $payment = $result->fetch(PDO::FETCH_ASSOC);

        if (!$payment) {
            throw new Exception("Payment not found");
        }

        // Calculate payment summary
        $summaryQuery = "SELECT 
                          COUNT(*) as total_tranches,
                          SUM(CASE WHEN statut_reglements = 'Payé' THEN 1 ELSE 0 END) as tranches_payees,
                          SUM(CASE WHEN statut_reglements = 'Payé' THEN montant ELSE 0 END) as montant_paye,
                          SUM(CASE WHEN date_echeance < CURRENT_DATE 
                                   AND statut_reglements = 'En attente' 
                              THEN 1 ELSE 0 END) as tranches_en_retard
                        FROM reglements_tranches_clients
                        WHERE id_paiement_tranche = :id_paiement_tranche
                        AND statut_reglements != 'Annulé'";
        $summaryResult = metodGet($summaryQuery, [':id_paiement_tranche' => $id_paiement_tranche]);
        $summary = $summaryResult->fetch(PDO::FETCH_ASSOC);

        // Update late payment status
        if ($summary['tranches_en_retard'] > 0) {
            $updateLateQuery = "UPDATE reglements_tranches_clients
                              SET statut_reglements = 'En retard'
                              WHERE id_paiement_tranche = :id_paiement_tranche
                              AND date_echeance < CURRENT_DATE
                              AND statut_reglements = 'En attente'";
            metodPut($updateLateQuery, 
                    [':id_paiement_tranche' => $id_paiement_tranche],
                    'reglements_tranches_clients');
        }

        // Determine new statuses
        $allPaid = abs($summary['montant_paye'] - $new_total) < 0.01;
        $partiallyPaid = $summary['montant_paye'] > 0;
        
        $newPaiementStatus = $allPaid ? 'Terminé' : 'En cours';
        $newVenteStatus = $allPaid ? 'Payé' : ($partiallyPaid ? 'Partiel' : 'En attente');

        // Update paiements_clients_tranches status
        $updatePaiementQuery = "UPDATE paiements_clients_tranches
                              SET statut_tranches = :statut_tranches,
                                  date_upd = CURRENT_TIMESTAMP,
                                  user_upd = 'SYSTEM'
                              WHERE id_paiement_tranche = :id_paiement_tranche";
        metodPut($updatePaiementQuery, [
            ':id_paiement_tranche' => $id_paiement_tranche,
            ':statut_tranches' => $newPaiementStatus
        ], 'paiements_clients_tranches');

        // Update ventes status
        $updateVenteQuery = "UPDATE ventes
                           SET statut_paiement = :statut_paiement,
                               date_upd = CURRENT_TIMESTAMP,
                               user_upd = 'SYSTEM'
                           WHERE id_vente = :id_vente";
        metodPut($updateVenteQuery, [
            ':id_vente' => $payment['id_vente'],
            ':statut_paiement' => $newVenteStatus
        ], 'ventes');

        return [
            'status' => 'success',
            'paiement_status' => $newPaiementStatus,
            'vente_status' => $newVenteStatus,
            'summary' => $summary
        ];

    } catch (Exception $e) {
        error_log("Error in updatePaymentStatuses: " . $e->getMessage());
        throw new Exception("Error updating payment statuses: " . $e->getMessage());
    }
}

/**
 * Creates a new installment payment plan
 */
function createInstallmentPlan($paymentData) {
    try {
        $pdo = getConnection();
        $pdo->beginTransaction();

        try {
            // Generate unique payment number if not provided
            if (!isset($paymentData['numero_paiement'])) {
                $paymentData['numero_paiement'] = generatePaymentNumber(
                    $paymentData['categorie_code']
                );
            }

            // Insert paiements_clients_tranches record
            $insertPaymentQuery = "INSERT INTO paiements_clients_tranches(
                id_vente, categorie_code, numero_paiement, nombre_tranches,
                montant_total, montant_par_tranche, date_debut, periodicite_jours,
                statut_tranches, user_cre
            ) VALUES (
                :id_vente, :categorie_code, :numero_paiement, :nombre_tranches,
                :montant_total, :montant_par_tranche, :date_debut, :periodicite_jours,
                'En cours', :user_cre
            )";

            $montantParTranche = $paymentData['montant_total'] / $paymentData['nombre_tranches'];

            $paymentParams = [
                ':id_vente' => $paymentData['id_vente'],
                ':categorie_code' => $paymentData['categorie_code'],
                ':numero_paiement' => $paymentData['numero_paiement'],
                ':nombre_tranches' => $paymentData['nombre_tranches'],
':montant_total' => $paymentData['montant_total'],
                ':montant_par_tranche' => $montantParTranche,
                ':date_debut' => $paymentData['date_debut'],
                ':periodicite_jours' => $paymentData['periodicite_jours'],
                ':user_cre' => $paymentData['user_cre']
            ];

            $id_paiement_tranche = metodPost(
                $insertPaymentQuery,
                "SELECT MAX(id_paiement_tranche) as id FROM paiements_clients_tranches",
                $paymentParams,
                'paiements_clients_tranches'
            )['id'];

            // Create individual installments
            $installments = [];
            $currentDate = new DateTime($paymentData['date_debut']);

            for ($i = 1; $i <= $paymentData['nombre_tranches']; $i++) {
                $installmentQuery = "INSERT INTO reglements_tranches_clients(
                    id_paiement_tranche, numero_tranche, date_echeance,
                    montant, statut_reglements, user_cre
                ) VALUES (
                    :id_paiement_tranche, :numero_tranche, :date_echeance,
                    :montant, 'En attente', :user_cre
                )";

                $installmentParams = [
                    ':id_paiement_tranche' => $id_paiement_tranche,
                    ':numero_tranche' => $i,
                    ':date_echeance' => $currentDate->format('Y-m-d'),
                    ':montant' => $montantParTranche,
                    ':user_cre' => $paymentData['user_cre']
                ];

                $id_reglement = metodPost(
                    $installmentQuery,
                    "SELECT MAX(id_reglement) as id FROM reglements_tranches_clients",
                    $installmentParams,
                    'reglements_tranches_clients'
                )['id'];

                $installments[] = [
                    'id_reglement' => $id_reglement,
                    'numero_tranche' => $i,
                    'date_echeance' => $currentDate->format('Y-m-d'),
                    'montant' => $montantParTranche
                ];

                // Add periodicite_jours to current date for next installment
                $currentDate->modify("+{$paymentData['periodicite_jours']} days");
            }

            // Update vente status
            $updateVenteQuery = "UPDATE ventes 
                               SET type_paiement = 'Tranche',
                                   statut_paiement = 'En attente',
                                   date_upd = CURRENT_TIMESTAMP,
                                   user_upd = :user_upd
                               WHERE id_vente = :id_vente";
            
            metodPut($updateVenteQuery, [
                ':id_vente' => $paymentData['id_vente'],
                ':user_upd' => $paymentData['user_cre']
            ], 'ventes');

            $pdo->commit();

            return [
                'status' => 'success',
                'id_paiement_tranche' => $id_paiement_tranche,
                'numero_paiement' => $paymentData['numero_paiement'],
                'montant_par_tranche' => $montantParTranche,
                'installments' => $installments
            ];

        } catch (Exception $e) {
            $pdo->rollBack();
            throw $e;
        }
    } catch (Exception $e) {
        error_log("Error in createInstallmentPlan: " . $e->getMessage());
        throw new Exception("Error creating installment plan: " . $e->getMessage());
    }
}

/**
 * Generates a unique payment reference number
 */
function generatePaymentNumber($categorie_code) {
    try {
        $prefix = 'PC'; // PC for Paiement Client
        $query = "SELECT COALESCE(MAX(CAST(SUBSTRING(numero_paiement, 3) AS UNSIGNED)), 0) as max_num
                 FROM paiements_clients_tranches
                 WHERE numero_paiement LIKE :prefix";
        $result = metodGet($query, [':prefix' => $prefix . '%']);
        $maxNum = $result->fetch(PDO::FETCH_ASSOC)['max_num'];
        
        return $prefix . str_pad($maxNum + 1, 6, '0', STR_PAD_LEFT);
    } catch (Exception $e) {
        error_log("Error generating payment number: " . $e->getMessage());
        throw new Exception("Error generating payment number");
    }
}

/**
 * Validates if a payment amount change is possible
 */
function validatePaymentChange($id_paiement_tranche, $new_total) {
    try {
        // Get current payment info
        $query = "SELECT * FROM paiements_clients_tranches 
                 WHERE id_paiement_tranche = :id_paiement_tranche";
        $result = metodGet($query, [':id_paiement_tranche' => $id_paiement_tranche]);
        $payment = $result->fetch(PDO::FETCH_ASSOC);

        if (!$payment) {
            return [
                'valid' => false,
                'message' => 'Payment not found'
            ];
        }

        // Get paid amounts
        $paidQuery = "SELECT COALESCE(SUM(montant), 0) as total_paid
                     FROM reglements_tranches_clients
                     WHERE id_paiement_tranche = :id_paiement_tranche
                     AND statut_reglements = 'Payé'";
        $paidResult = metodGet($paidQuery, [':id_paiement_tranche' => $id_paiement_tranche]);
        $totalPaid = $paidResult->fetch(PDO::FETCH_ASSOC)['total_paid'];

        if ($totalPaid > $new_total) {
            return [
                'valid' => false,
                'message' => 'Le nouveau montant total est inférieur aux paiements déjà effectués',
                'details' => [
                    'current_paid' => $totalPaid,
                    'new_total' => $new_total,
                    'difference' => $totalPaid - $new_total
                ]
            ];
        }

        return [
            'valid' => true,
            'message' => 'Modification possible',
            'details' => [
                'current_total' => $payment['montant_total'],
                'new_total' => $new_total,
                'total_paid' => $totalPaid,
                'remaining_unpaid' => $new_total - $totalPaid
            ]
        ];
    } catch (Exception $e) {
        error_log("Error in validatePaymentChange: " . $e->getMessage());
        throw new Exception("Error validating payment change: " . $e->getMessage());
    }
}

/**
 * Gets payment plan details with all installments
 */
function getPaymentPlanDetails($id_paiement_tranche) {
    try {
        // Get main payment info
        $query = "SELECT 
                    pct.*,
                    v.numero_vente,
                    v.date_vente,
                    c.nom,
                    c.prenom,
                    bsg.Libelle as categorie_libelle
                 FROM paiements_clients_tranches pct
                 JOIN ventes v ON pct.id_vente = v.id_vente
                 JOIN client c ON v.id_client = c.id_client
                 LEFT JOIN bsgcod bsg ON pct.categorie_code = bsg.Code
                 WHERE pct.id_paiement_tranche = :id_paiement_tranche";

        $result = metodGet($query, [':id_paiement_tranche' => $id_paiement_tranche]);
        $planInfo = $result->fetch(PDO::FETCH_ASSOC);

        if (!$planInfo) {
            throw new Exception("Plan de paiement non trouvé");
        }

        // Get installments details
        $installmentsQuery = "SELECT *
                            FROM reglements_tranches_clients
                            WHERE id_paiement_tranche = :id_paiement_tranche
                            ORDER BY numero_tranche";
        
        $installmentsResult = metodGet($installmentsQuery, [
            ':id_paiement_tranche' => $id_paiement_tranche
        ]);
        $installments = $installmentsResult->fetchAll(PDO::FETCH_ASSOC);

        // Calculate summary
        $summary = [
            'montant_total' => $planInfo['montant_total'],
            'montant_paye' => array_sum(array_map(function($i) {
                return $i['statut_reglements'] === 'Payé' ? $i['montant'] : 0;
            }, $installments)),
            'total_tranches' => count($installments),
            'tranches_payees' => count(array_filter($installments, function($i) {
                return $i['statut_reglements'] === 'Payé';
            })),
            'tranches_en_retard' => count(array_filter($installments, function($i) {
                return $i['statut_reglements'] === 'En retard';
            }))
        ];

        $summary['montant_restant'] = $summary['montant_total'] - $summary['montant_paye'];
        $summary['pourcentage_paye'] = ($summary['montant_total'] > 0) ? 
            ($summary['montant_paye'] / $summary['montant_total'] * 100) : 0;

        return [
            'plan_info' => $planInfo,
            'installments' => $installments,
            'summary' => $summary
        ];

    } catch (Exception $e) {
        error_log("Error in getPaymentPlanDetails: " . $e->getMessage());
        throw new Exception("Error getting payment plan details: " . $e->getMessage());
    }
}

/**
 * Processes a payment for a specific installment
 */
function processInstallmentPayment($paymentData) {
    try {
        $pdo = getConnection();
        $pdo->beginTransaction();

        try {
            // Validate payment data
            $validationResult = validatePaymentProcessing($paymentData);
            if (!$validationResult['valid']) {
                throw new Exception($validationResult['message']);
            }

            // Update the specific installment
            $updateQuery = "UPDATE reglements_tranches_clients
                          SET date_paiement = :date_paiement,
                              mode_paiement = :mode_paiement,
                              reference_paiement = :reference_paiement,
                              statut_reglements = 'Payé',
                              commentaire = :commentaire,
                              user_upd = :user_upd,
                              date_upd = CURRENT_TIMESTAMP
                          WHERE id_reglement = :id_reglement";

            $params = [
                ':id_reglement' => $paymentData['id_reglement'],
                ':date_paiement' => $paymentData['date_paiement'],
                ':mode_paiement' => $paymentData['mode_paiement'],
                ':reference_paiement' => $paymentData['reference_paiement'],
                ':commentaire' => $paymentData['commentaire'] ?? null,
                ':user_upd' => $paymentData['user_upd']
            ];

            metodPut($updateQuery, $params, 'reglements_tranches_clients');

            // Get payment plan info for status update
            $planQuery = "SELECT rtc.id_paiement_tranche, pct.montant_total
                         FROM reglements_tranches_clients rtc
                         JOIN paiements_clients_tranches pct ON rtc.id_paiement_tranche = pct.id_paiement_tranche
                         WHERE rtc.id_reglement = :id_reglement";
            $result = metodGet($planQuery, [':id_reglement' => $paymentData['id_reglement']]);
            $planInfo = $result->fetch(PDO::FETCH_ASSOC);

            // Update payment statuses
            updatePaymentStatuses($planInfo['id_paiement_tranche'], $planInfo['montant_total']);

            $pdo->commit();

            return [
                'status' => 'success',
                'message' => 'Paiement traité avec succès',
                'payment_info' => [
                    'id_paiement_tranche' => $planInfo['id_paiement_tranche'],
                    'montant' => $paymentData['montant'],
                    'date_paiement' => $paymentData['date_paiement']
                ]
            ];

        } catch (Exception $e) {
            $pdo->rollBack();
            throw $e;
        }
    } catch (Exception $e) {
        error_log("Error in processInstallmentPayment: " . $e->getMessage());
        throw new Exception("Error processing payment: " . $e->getMessage());
    }
}

/**
 * Validates payment processing data
 */
function validatePaymentProcessing($paymentData) {
    try {
        // Check if installment exists and is unpaid
        $query = "SELECT rtc.*, pct.montant_total
                 FROM reglements_tranches_clients rtc
                 JOIN paiements_clients_tranches pct ON rtc.id_paiement_tranche = pct.id_paiement_tranche
                 WHERE rtc.id_reglement = :id_reglement";
        
        $result = metodGet($query, [':id_reglement' => $paymentData['id_reglement']]);
        $installment = $result->fetch(PDO::FETCH_ASSOC);

        if (!$installment) {
            return [
                'valid' => false,
                'message' => 'Tranche de paiement non trouvée'
            ];
        }

        if ($installment['statut_reglements'] === 'Payé') {
            return [
                'valid' => false,
                'message' => 'Cette tranche a déjà été payée'
            ];
        }

        if ($installment['statut_reglements'] === 'Annulé') {
            return [
                'valid' => false,
                'message' => 'Cette tranche a été annulée'
            ];
        }

        return [
            'valid' => true,
            'installment_info' => $installment
        ];

    } catch (Exception $e) {
        error_log("Error in validatePaymentProcessing: " . $e->getMessage());
        throw new Exception("Error validating payment: " . $e->getMessage());
    }
}
?>