<?php
$pdo = null;
$host = "localhost";
$user = "imlayfzp_user";
$pass = "Keyuser01#@";
$bd = "imlayfzp_gestion_commerciale";

function connect()
{
    global $pdo, $host, $user, $pass, $bd;
    try {
        $pdo = new PDO("mysql:host=$host;dbname=$bd", $user, $pass);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $e) {
        die("Error!: Cannot connect to database $bd<br/>Error!: " . $e->getMessage() . "<br/>");
    }
}

function disconnect()
{
    global $pdo;
    $pdo = null;
}


function metodGet($query, $params = array())
{
    try {
        connect();
        global $pdo;
        $statement = $pdo->prepare($query);
        $statement->setFetchMode(PDO::FETCH_ASSOC);
        $statement->execute($params);
        return $statement;
    } catch (Exception $e) {
        die("Error: " . $e->getMessage());
    }
}

function metodPost($query, $queryAutoIncrement, $params, $tablename)
{
    try {
        connect();
        global $pdo;
        $statement = $pdo->prepare($query);
        $statement->execute($params);

        $idAutoIncrement = metodGet($queryAutoIncrement)->fetch(PDO::FETCH_ASSOC);
        $result = array_merge($idAutoIncrement, $_POST);

        $user_cre = $_POST['user_cre'];
        logMovement($user_cre, 'INSERT', $tablename);

        $statement->closeCursor();
        disconnect();
        return $result;
    } catch (Exception $e) {
        die("Error POST: " . $e->getMessage());
    }
}

function metodPut($query, $params, $tablename)
{
    try {
        connect();
        global $pdo;
        $statement = $pdo->prepare($query);
        $success = $statement->execute($params);

        $user_upd = $_POST['user_upd'] ?? 'system';
        logMovement($user_upd, 'UPDATE', $tablename);

        $statement->closeCursor();
        disconnect();

        return $success;
    } catch (Exception $e) {
        die("Error: " . $e->getMessage());
    }
}

function metodDelete($query, $params, $tablename)
{
    try {
        connect();
        global $pdo;
        $statement = $pdo->prepare($query);
        $statement->execute($params);

        $user_del = $_POST['user_del'] ?? 'system';
        logMovement($user_del, 'DELETE', $tablename);

        $statement->closeCursor();
        disconnect();
        return true;
    } catch (Exception $e) {
        die("Error: " . $e->getMessage());
    }
}

function logMovement($user, $type, $tablename) {
    try {
        connect();
        global $pdo;
        
        // Ensure user is never null
        $user = $user ?: 'SYSTEM';
        
        $query = "INSERT INTO mouvement_historique (user, type, date_cre, date_upd, tablename) 
                  VALUES (:user, :type, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, :tablename)";
        $params = array(
            ':user' => $user,
            ':type' => $type,
            ':tablename' => $tablename
        );
        $statement = $pdo->prepare($query);
        $statement->execute($params);
        $statement->closeCursor();
        disconnect();
    } catch (Exception $e) {
        disconnect();
        throw new Exception("Error in logMovement: " . $e->getMessage());
    }
}

function updatePaymentStatus($id_vente) {
    try {

        $query = "SELECT type_paiement, montant_total FROM ventes WHERE id_vente = :id_vente";
        $params = array(':id_vente' => $id_vente);
        $result = metodGet($query, $params)->fetch(PDO::FETCH_ASSOC);
        
        if (!$result) {
            throw new Exception("Sale not found");
        }

        $type_paiement = $result['type_paiement'];
        $montant_total = $result['montant_total'];
        $new_status = 'En attente'; // Default status

        if ($type_paiement === 'Cash') {

            $query = "SELECT COALESCE(SUM(montant_paye), 0) as total_paye, 
                             COUNT(*) as payment_count 
                     FROM paiements_clients_cash 
                     WHERE id_vente = :id_vente";
            $params = array(':id_vente' => $id_vente);
            $result = metodGet($query, $params)->fetch(PDO::FETCH_ASSOC);
            
            if ($result['payment_count'] > 0) {
                if ($result['total_paye'] >= $montant_total) {
                    $new_status = 'Payé';
                } else {
                    $new_status = 'Partiel';
                }
            }
        } 
        elseif ($type_paiement === 'Tranche') {

            $query = "SELECT id_paiement_tranche, nombre_tranches 
                     FROM paiements_clients_tranches 
                     WHERE id_vente = :id_vente";
            $params = array(':id_vente' => $id_vente);
            $result = metodGet($query, $params)->fetch(PDO::FETCH_ASSOC);
            
            if ($result) {
                // Check payment status of installments
                $query = "SELECT COUNT(*) as total_tranches,
                         SUM(CASE WHEN statut_reglements = 'Payé' THEN 1 ELSE 0 END) as tranches_payees
                         FROM reglements_tranches_clients 
                         WHERE id_paiement_tranche = :id_paiement_tranche";
                $params = array(':id_paiement_tranche' => $result['id_paiement_tranche']);
                $payment_result = metodGet($query, $params)->fetch(PDO::FETCH_ASSOC);
                
                if ($payment_result['tranches_payees'] > 0) {
                    if ($payment_result['tranches_payees'] == $result['nombre_tranches']) {
                        $new_status = 'Payé';
                        // Update paiements_clients_tranches status to 'Terminé'
                        $update_query = "UPDATE paiements_clients_tranches 
                                       SET statut_tranches = 'Terminé',
                                           user_upd = 'system'
                                       WHERE id_paiement_tranche = :id_paiement_tranche";
                        $update_params = array(':id_paiement_tranche' => $result['id_paiement_tranche']);
                        metodPut($update_query, $update_params, 'paiements_clients_tranches');
                    } else {
                        $new_status = 'Partiel';
                    }
                }
            }
        }

        // Update ventes table with the new status
        $query = "UPDATE ventes SET statut_paiement = :statut_paiement, user_upd = 'system' WHERE id_vente = :id_vente";
        $params = array(
            ':id_vente' => $id_vente,
            ':statut_paiement' => $new_status
        );
        metodPut($query, $params, 'ventes');

        return $new_status;
    } catch (Exception $e) {
        throw new Exception("Error updating payment status: " . $e->getMessage());
    }
}

function handlePaymentStatusChange($operation, $id_vente, $payment_type = null) {
    try {
        $status = updatePaymentStatus($id_vente);
        
        // Get current sale information
        $query = "SELECT statut_paiement, type_paiement, montant_total 
                 FROM ventes 
                 WHERE id_vente = :id_vente";
        $params = array(':id_vente' => $id_vente);
        $sale_info = metodGet($query, $params)->fetch(PDO::FETCH_ASSOC);
        
        if (!$sale_info) {
            throw new Exception("Sale not found");
        }

        // Get payment progress information
        $payment_info = array(
            'amount_paid' => 0,
            'remaining_amount' => 0,
            'payment_progress' => 0
        );

        if ($sale_info['type_paiement'] === 'Cash') {
            $query = "SELECT COALESCE(SUM(montant_paye), 0) as total_paid 
                     FROM paiements_clients_cash 
                     WHERE id_vente = :id_vente";
            $params = array(':id_vente' => $id_vente);
            $result = metodGet($query, $params)->fetch(PDO::FETCH_ASSOC);
            
            $payment_info['amount_paid'] = floatval($result['total_paid']);
        } 
        elseif ($sale_info['type_paiement'] === 'Tranche') {
            $query = "SELECT COALESCE(SUM(CASE WHEN statut_reglements= 'Payé' THEN montant ELSE 0 END), 0) as total_paid 
                     FROM reglements_tranches_clients rtc
                     JOIN paiements_clients_tranches pct 
                     ON rtc.id_paiement_tranche = pct.id_paiement_tranche
                     WHERE pct.id_vente = :id_vente";
            $params = array(':id_vente' => $id_vente);
            $result = metodGet($query, $params)->fetch(PDO::FETCH_ASSOC);
            
            $payment_info['amount_paid'] = floatval($result['total_paid']);
        }

        $payment_info['remaining_amount'] = $sale_info['montant_total'] - $payment_info['amount_paid'];
        $payment_info['payment_progress'] = ($payment_info['amount_paid'] / $sale_info['montant_total']) * 100;

        return array(
            'success' => true,
            'operation' => $operation,
            'status' => $status,
            'current_payment_status' => $sale_info['statut_paiement'],
            'payment_type' => $sale_info['type_paiement'],
            'payment_details' => $payment_info,
            'message' => getStatusMessage($operation, $status)
        );

    } catch (Exception $e) {
        return array(
            'success' => false,
            'operation' => $operation,
            'error' => $e->getMessage()
        );
    }
}

function updatePurchasePaymentStatus($id_achat) {
    try {
        $query = "SELECT type_paiement, montant_total FROM achats WHERE id_achat = :id_achat";
        $params = array(':id_achat' => $id_achat);
        $result = metodGet($query, $params)->fetch(PDO::FETCH_ASSOC);

        if (!$result) {
            throw new Exception("Purchase not found");
        }

        $type_paiement = $result['type_paiement'];
        $montant_total = $result['montant_total'];
        $new_status = 'En attente'; // Default status

        if ($type_paiement === 'Cash') {
            $query = "SELECT COALESCE(SUM(montant_paye), 0) as total_paye,
                             COUNT(*) as payment_count
                     FROM paiements_fournisseurs_cash
                     WHERE id_achat = :id_achat";
            $params = array(':id_achat' => $id_achat);
            $result = metodGet($query, $params)->fetch(PDO::FETCH_ASSOC);

            if ($result['payment_count'] > 0) {
                if ($result['total_paye'] >= $montant_total) {
                    $new_status = 'Payé';
                } else {
                    $new_status = 'Partiel';
                }
            }
        }
        elseif ($type_paiement === 'Tranche') {
            $query = "SELECT id_paiement_tranche, nombre_tranches
                     FROM paiements_fournisseurs_tranches
                     WHERE id_achat = :id_achat";
            $params = array(':id_achat' => $id_achat);
            $result = metodGet($query, $params)->fetch(PDO::FETCH_ASSOC);

            if ($result) {
                // Check payment status of installments
                $query = "SELECT COUNT(*) as total_tranches,
                         SUM(CASE WHEN statut_reglements = 'Payé' THEN 1 ELSE 0 END) as tranches_payees
                         FROM reglements_tranches_fournisseurs
                         WHERE id_paiement_tranche = :id_paiement_tranche";
                $params = array(':id_paiement_tranche' => $result['id_paiement_tranche']);
                $payment_result = metodGet($query, $params)->fetch(PDO::FETCH_ASSOC);

                if ($payment_result['tranches_payees'] > 0) {
                    if ($payment_result['tranches_payees'] == $result['nombre_tranches']) {
                        $new_status = 'Payé';
                        // Update paiements_fournisseurs_tranches status to 'Terminé'
                        $update_query = "UPDATE paiements_fournisseurs_tranches
                                       SET statut_tranches = 'Terminé',
                                           user_upd = 'system'
                                       WHERE id_paiement_tranche = :id_paiement_tranche";
                        $update_params = array(':id_paiement_tranche' => $result['id_paiement_tranche']);
                        metodPut($update_query, $update_params, 'paiements_fournisseurs_tranches');
                    } else {
                        $new_status = 'Partiel';
                    }
                }
            }
        }

        // Update achats table with the new status
        $query = "UPDATE achats SET statut_paiement = :statut_paiement, user_upd = 'system' WHERE id_achat = :id_achat";
        $params = array(
            ':id_achat' => $id_achat,
            ':statut_paiement' => $new_status
        );
        metodPut($query, $params, 'achats');

        return $new_status;
    } catch (Exception $e) {
        throw new Exception("Error updating purchase payment status: " . $e->getMessage());
    }
}

function handlePurchasePaymentStatusChange($operation, $id_achat, $payment_type = null) {
    try {
        $status = updatePurchasePaymentStatus($id_achat);

        // Get current purchase information
        $query = "SELECT statut_paiement, type_paiement, montant_total
                 FROM achats
                 WHERE id_achat = :id_achat";
        $params = array(':id_achat' => $id_achat);
        $purchase_info = metodGet($query, $params)->fetch(PDO::FETCH_ASSOC);

        if (!$purchase_info) {
            throw new Exception("Purchase not found");
        }

        // Get payment progress information
        $payment_info = array(
            'amount_paid' => 0,
            'remaining_amount' => 0,
            'payment_progress' => 0
        );

        if ($purchase_info['type_paiement'] === 'Cash') {
            $query = "SELECT COALESCE(SUM(montant_paye), 0) as total_paid
                     FROM paiements_fournisseurs_cash
                     WHERE id_achat = :id_achat";
            $params = array(':id_achat' => $id_achat);
            $result = metodGet($query, $params)->fetch(PDO::FETCH_ASSOC);

            $payment_info['amount_paid'] = floatval($result['total_paid']);
        }
        elseif ($purchase_info['type_paiement'] === 'Tranche') {
            $query = "SELECT COALESCE(SUM(CASE WHEN statut_reglements= 'Payé' THEN montant ELSE 0 END), 0) as total_paid
                     FROM reglements_tranches_fournisseurs rtf
                     JOIN paiements_fournisseurs_tranches pft
                     ON rtf.id_paiement_tranche = pft.id_paiement_tranche
                     WHERE pft.id_achat = :id_achat";
            $params = array(':id_achat' => $id_achat);
            $result = metodGet($query, $params)->fetch(PDO::FETCH_ASSOC);

            $payment_info['amount_paid'] = floatval($result['total_paid']);
        }

        $payment_info['remaining_amount'] = $purchase_info['montant_total'] - $payment_info['amount_paid'];
        $payment_info['payment_progress'] = ($payment_info['amount_paid'] / $purchase_info['montant_total']) * 100;

        return array(
            'success' => true,
            'operation' => $operation,
            'status' => $status,
            'current_payment_status' => $purchase_info['statut_paiement'],
            'payment_type' => $purchase_info['type_paiement'],
            'payment_details' => $payment_info,
            'message' => getStatusMessage($operation, $status)
        );

    } catch (Exception $e) {
        return array(
            'success' => false,
            'operation' => $operation,
            'error' => $e->getMessage()
        );
    }
}

function getStatusMessage($operation, $status) {
    $messages = array(
        'DELETE' => 'Payment record deleted successfully. ',
        'UPDATE' => 'Payment record updated successfully. ',
        'CREATE' => 'Payment record created successfully. '
    );

    $status_message = $messages[$operation] ?? 'Operation completed. ';
    
    if ($status === 'Payé') {
        $status_message .= 'Sale is now fully paid.';
    } elseif ($status === 'En attente') {
        $status_message .= 'Sale is now awaiting payment.';
    } elseif ($status === 'Partiel') {
        $status_message .= 'Sale is partially paid.';
    }

    return $status_message;
}

function reference_generator($categorie_code, $date = null) {
    try {
        error_log("Starting reference generation for categorie_code: $categorie_code");
        
        connect();
        global $pdo;

        $date = $date ? new DateTime($date) : new DateTime();

        // First check in BSGCOD
        $bsgcod_query = "SELECT CODNUM FROM bsgcod WHERE Code = :categorie_code";
        $bsgcod_stmt = $pdo->prepare($bsgcod_query);
        $bsgcod_stmt->execute([':categorie_code' => $categorie_code]);
        $bsgcod_result = $bsgcod_stmt->fetch(PDO::FETCH_ASSOC);

        if (!$bsgcod_result) {
            throw new Exception("Invalid categorie_code");
        }

        $codnum = $bsgcod_result['CODNUM'];
        error_log("Found CODNUM: $codnum");

        // Get ACODNUM configuration and company info
        $acodnum_query = "SELECT a.*, s.description as societe_description 
                         FROM acodnum a 
                         LEFT JOIN societes s ON s.Code = 1 
                         WHERE a.CODNUM = :codnum";
        $acodnum_stmt = $pdo->prepare($acodnum_query);
        $acodnum_stmt->execute([':codnum' => $codnum]);
        $acodnum_result = $acodnum_stmt->fetch(PDO::FETCH_ASSOC);

        if (!$acodnum_result) {
            throw new Exception("No acodnum configuration found");
        }

        error_log("acodnum configuration: " . print_r($acodnum_result, true));

        // Extract configuration values
        $formule = $acodnum_result['Formule'];
        $val = $acodnum_result['VAL'];
        $typ = $acodnum_result['TYP'];
        $societe_description = $acodnum_result['societe_description'];
        $nivraz = $acodnum_result['NIVRAZ'];

        // Increment VAL
        $new_val = ($val ?? 0) + 1;

        // Get period value from AVALNUM based on reset level
        if ($nivraz === "Mensuel") {
            $current_period = $date->format('Y-m-01');
            $avalnum_query = "SELECT Value, PERIODE FROM avalnum 
                            WHERE CODNUM = :codnum 
                            AND DATE_FORMAT(PERIODE, '%Y-%m') = :yearmonth
                            ORDER BY PERIODE DESC, Code DESC LIMIT 1";
            $params = [
                ':codnum' => $codnum,
                ':yearmonth' => $date->format('Y-m')
            ];
        } elseif ($nivraz === "Annuel") {
            $avalnum_query = "SELECT Value, PERIODE FROM avalnum 
                            WHERE CODNUM = :codnum 
                            AND YEAR(PERIODE) = :year 
                            ORDER BY PERIODE DESC, Code DESC LIMIT 1";
            $params = [
                ':codnum' => $codnum,
                ':year' => $date->format('Y')
            ];
        } else {
            $avalnum_query = "SELECT Value, PERIODE FROM avalnum 
                            WHERE CODNUM = :codnum 
                            ORDER BY PERIODE DESC, Code DESC LIMIT 1";
            $params = [':codnum' => $codnum];
        }

        $avalnum_stmt = $pdo->prepare($avalnum_query);
        $avalnum_stmt->execute($params);
        $avalnum_result = $avalnum_stmt->fetch(PDO::FETCH_ASSOC);

        // Handle counter based on period
        $monthly_count = 1;
        if ($avalnum_result) {
            $last_periode = new DateTime($avalnum_result['PERIODE']);
            
            if ($nivraz === "Mensuel") {
                if ($date->format('Y-m') === $last_periode->format('Y-m')) {
                    $monthly_count = $avalnum_result['Value'] + 1;
                }
            } elseif ($nivraz === "Annuel") {
                if ($date->format('Y') === $last_periode->format('Y')) {
                    $monthly_count = $avalnum_result['Value'] + 1;
                }
            } else {
                $monthly_count = $avalnum_result['Value'] + 1;
            }
        }

        // Generate reference code
        if ($typ === "Numerique") {
            $code_reference = str_pad(min($new_val, 99999), 6, '0', STR_PAD_LEFT);
        } else {
            $components = [];
            
            // Get length configurations with defaults
            $year_length = !is_null($acodnum_result['POSTYP1']) && is_numeric($acodnum_result['POSTYP1']) ? 
                          intval($acodnum_result['POSTYP1']) : 4;
            $month_length = !is_null($acodnum_result['POSTYP2']) && is_numeric($acodnum_result['POSTYP2']) ? 
                           intval($acodnum_result['POSTYP2']) : 2;
            $day_length = !is_null($acodnum_result['POSTYP3']) && is_numeric($acodnum_result['POSTYP3']) ? 
                         intval($acodnum_result['POSTYP3']) : 2;
            $sequence_length = !is_null($acodnum_result['POSTYP5']) && is_numeric($acodnum_result['POSTYP5']) ? 
                             intval($acodnum_result['POSTYP5']) : 6;

            // Build reference components
            for ($i = 0; $i <= 4; $i++) {
                $poscte = isset($acodnum_result["POSCTE" . ($i ?: '')]) ? 
                         $acodnum_result["POSCTE" . ($i ?: '')] : null;

                if (!is_null($poscte)) {
                    switch ($poscte) {
                        case 'Année':
                            $components[$i] = substr($date->format('Y'), -$year_length);
                            break;
                        case 'Mois':
                            $components[$i] = substr($date->format('m'), -$month_length);
                            break;
                        case 'Jour':
                            $components[$i] = substr($date->format('d'), -$day_length);
                            break;
                        case 'Constante':
                            $components[$i] = $formule;
                            break;
                        case 'Société':
                            if (!is_null($societe_description)) {
                                $societe_length = !is_null($acodnum_result['POSTYP4']) && 
                                                is_numeric($acodnum_result['POSTYP4']) ? 
                                                intval($acodnum_result['POSTYP4']) : 
                                                strlen($societe_description);
                                $components[$i] = substr($societe_description, 0, $societe_length);
                            }
                            break;
                        case 'Séquence':
                            $components[$i] = str_pad($new_val, $sequence_length, '0', STR_PAD_LEFT);
                            break;
                    }
                }
            }

            $code_reference = implode('', $components);
        }

        if (empty($code_reference)) {
            throw new Exception("Failed to generate reference code");
        }

        // Update ACODNUM with new value
        $update_acodnum = "UPDATE acodnum 
                          SET VAL = :new_val, 
                              LNG = :lng, 
                              LastResetDate = :date 
                          WHERE CODNUM = :codnum";
        $update_stmt = $pdo->prepare($update_acodnum);
        $update_stmt->execute([
            ':new_val' => $new_val,
            ':lng' => strlen($code_reference),
            ':date' => $date->format('Y-m-d H:i:s'),
            ':codnum' => $codnum
        ]);

        // Update AVALNUM
        if ($nivraz === "Mensuel") {
            $avalnum_upsert = "INSERT INTO avalnum (
                                CODNUM, PERIODE, SITE, Value, 
                                user_cre, date_cre
                              ) VALUES (
                                :codnum, :periode, :site, :val,
                                'system', CURRENT_TIMESTAMP
                              ) ON DUPLICATE KEY UPDATE 
                                Value = :val,
                                date_upd = CURRENT_TIMESTAMP,
                                user_upd = 'system'";
            
            $avalnum_stmt = $pdo->prepare($avalnum_upsert);
            $avalnum_stmt->execute([
                ':codnum' => $codnum,
                ':periode' => $date->format('Y-m-01'),
                ':site' => $societe_description,
                ':val' => $monthly_count
            ]);
        } else {
            if ($avalnum_result) {
                update_avalnum($codnum, $societe_description, $monthly_count, $date->format('Y-m-d'));
            } else {
                insert_avalnum($codnum, $societe_description, $monthly_count, $date->format('Y-m-d'));
            }
        }

        disconnect();
        return $code_reference;

    } catch (Exception $e) {
        error_log("Error in reference_generator: " . $e->getMessage());
        error_log("Stack trace: " . $e->getTraceAsString());
        disconnect();
        throw new Exception("Reference generation failed: " . $e->getMessage());
    }
}

function update_avalnum($codnum, $site, $val, $periode) {
    try {
        global $pdo;
        
        $update_query = "UPDATE avalnum SET Value = :val, date_upd = CURRENT_TIMESTAMP, user_upd = 'system' 
                         WHERE CODNUM = :codnum AND YEAR(PERIODE) = YEAR(:periode) AND SITE = :site";
        $update_stmt = $pdo->prepare($update_query);
        $update_stmt->execute([
            ':val' => $val,
            ':codnum' => $codnum,
            ':periode' => $periode,
            ':site' => $site
        ]);
        
        // If no rows were updated, insert a new record
        if ($update_stmt->rowCount() == 0) {
            insert_avalnum($codnum, $site, $val, $periode);
        }
    } catch (Exception $e) {
        error_log("Error updating avalnum: " . $e->getMessage());
        throw new Exception("Error updating avalnum. Please check the server logs for more details.");
    }
}

function insert_avalnum($codnum, $site, $val, $periode) {
    try {
        global $pdo;
        
        $insert_query = "INSERT INTO avalnum (CODNUM, PERIODE, SITE, Value, user_cre, date_cre) 
                         VALUES (:codnum, :periode, :site, :val, 'system', CURRENT_TIMESTAMP)";
        $insert_stmt = $pdo->prepare($insert_query);
        $insert_stmt->execute([
            ':codnum' => $codnum,
            ':periode' => $periode,
            ':site' => $site,
            ':val' => $val
        ]);
    } catch (Exception $e) {
        error_log("Error inserting into avalnum: " . $e->getMessage());
        throw new Exception("Error inserting into avalnum. Please check the server logs for more details.");
    }
}
?>