<?php
require_once 'database.php';
require_once 'encrypt.php';
require_once 'vendor/autoload.php';

use PhpMqtt\Client\MqttClient;
use PhpMqtt\Client\ConnectionSettings;

header('Content-Type: application/json; charset=utf-8');

// --- Sécurité HTTPS obligatoire ---
if (empty($_SERVER['HTTPS']) || $_SERVER['HTTPS'] !== 'on') {
    http_response_code(403);
    echo json_encode(['error' => 'Connexion non sécurisée. HTTPS requis.']);
    exit;
}

// --- Vérification de la signature HMAC ---
$authorization = getallheaders()['Authorization'] ?? null;
$signature     = getallheaders()['X-Signature'] ?? null;
$cleSecrete    = "LeSeigneurJesusEstDieuEtMoisonfils@17";

if (!$authorization || !$signature) {
    http_response_code(400);
    echo json_encode(['error' => 'En-têtes manquants']);
    exit;
}

$signatureAttendue = strtoupper(hash_hmac('sha256', $authorization, $cleSecrete));
if (!hash_equals($signatureAttendue, strtoupper($signature))) {
    http_response_code(403);
    echo json_encode(['error' => 'Signature invalide']);
    exit;
}

// --- Liste blanche des tables autorisées ---
$allowedTables = [
    'Categories', 'Produits', 'EntreSorties', 'Bon_Mouvements', 'Emplacements', 'ENTREPRISES', 'Points', 'PROTO',
    'Clients', 'ProduitsVendus', 'Ventes', 'Profils', 'CAISSES', 'Reductions', 'DuPartenaires', 'SMSModeles',
    'PaiementsCredits', 'DatesExpiration', 'Services', 'ServicesPris', 'ContactsSave', 'FondDeCaisse', 'ActionsFond',
    'Fournisseur', 'ActionsStock', 'OUVERTFERME', 'AGENCES_SD', 'Depots', 'ZTRANSACTIONS', 'AgentCommercial',
    'RevenuCommercial', 'CallBack', 'ZConnexion', 'DataSynchro', 'TABLES', 'UniteDeVente', 'TarifUnites', 'ImagesZB',
    'Cuissons', 'Tarifications', 'Necessaires', 'ArticlesDeposes', 'BENRESTES', 'Comptes', 'GroupeSalaire',
    'Paiements', 'AvancesSalaire', 'Paies', 'Adherents', 'Abonnementsservices', 'Servicesabonnement', 'EnvoiSMS'
];

$inputJson = file_get_contents('php://input');
$dataArray = json_decode($inputJson, true);

if (!$dataArray) {
    http_response_code(400);
    echo json_encode(['error' => 'Données JSON invalides']);
    exit;
}

$results = [];

function publishToMqtt($topic, $data) {
    global $mqtt_server, $mqtt_port, $mqtt_username, $mqtt_password, $mqtt_client_id;

    $mqtt = new MqttClient($mqtt_server, $mqtt_port, $mqtt_client_id);

    $connectionSettings = (new ConnectionSettings)
        ->setUsername($mqtt_username)
        ->setPassword($mqtt_password)
        ->setUseTls(true);

    $mqtt->connect($connectionSettings, true);
    $mqtt->publish($topic, $data, 2);
    $mqtt->disconnect();
}

function getBooleanColumns($pdo, $tableName) {
    $booleanColumns = [];
    $stmt = $pdo->query("SHOW COLUMNS FROM `$tableName`");
    $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($columns as $column) {
        if (strpos($column['Type'], 'tinyint(1)') !== false || strpos(strtolower($column['Type']), 'bool') !== false) {
            $booleanColumns[] = $column['Field'];
        }
    }
    return $booleanColumns;
}

function convertBooleansToIntegers(&$data, $booleanFields) {
    foreach ($booleanFields as $field) {
        if (isset($data[$field])) {
            $data[$field] = $data[$field] ? 1 : 0;
        }
    }
}

function escapeSqlIdentifier($identifier) {
    return '`' . str_replace('`', '', $identifier) . '`';
}

foreach ($dataArray as $data) {
    try {
        $tableName = $data['FichierConcerne'] ?? null;
        if (!$tableName || !in_array($tableName, $allowedTables)) {
            $results[] = ['status' => 'error', 'message' => "Table non autorisée ou manquante : $tableName"];
            continue;
        }

        if (!isset($data['Data'])) {
            $results[] = ['status' => 'error', 'message' => 'Rubrique "Data" manquante'];
            continue;
        }

        $dataToProcess = json_decode($data['Data'], true);
        if (!$dataToProcess) {
            $results[] = ['status' => 'error', 'message' => 'Données "Data" invalides'];
            continue;
        }

        $result = processGenericData($tableName, $dataToProcess);
        $results[] = $result;

        if (isset($result['status']) && $result['status'] === 'success') {
            $identreprises = $dataToProcess['IDENTREPRISES'] ?? null;
            if ($identreprises) {
                publishToMqtt($identreprises, json_encode($data));
            }
        }

    } catch (Exception $e) {
        $results[] = ['status' => 'error', 'message' => 'Erreur lors du traitement : ' . $e->getMessage()];
        file_put_contents('error.log', date('Y-m-d H:i:s') . " - Erreur: " . $e->getMessage() . "\n", FILE_APPEND);
    }
}

if (in_array(['status' => 'error'], $results)) {
    http_response_code(400);
    echo json_encode(['results' => $results], JSON_UNESCAPED_UNICODE);
} else {
    http_response_code(200);
    echo json_encode(['message' => 'Traitement terminé avec succès'], JSON_UNESCAPED_UNICODE);
}

function processGenericData($tableName, $data) {
    global $pdo;

    try {
        $stmt = $pdo->query("DESCRIBE `$tableName`");
        $tableColumns = $stmt->fetchAll(PDO::FETCH_COLUMN);
        $filteredData = array_intersect_key($data, array_flip($tableColumns));
        $booleanFields = getBooleanColumns($pdo, $tableName);
        convertBooleansToIntegers($filteredData, $booleanFields);

        $idField = 'ID' . $tableName;
        $idValue = $filteredData[$idField] ?? null;

        if (!$idValue) {
            return ["status" => "error", "message" => "Identifiant unique manquant dans les données pour la table $tableName (Nom de l'identifiant: $idField)"];
        }

        // Échappement des identifiants pour les requêtes SQL
        $escapedTable = escapeSqlIdentifier($tableName);
        $escapedIdField = escapeSqlIdentifier($idField);

        $stmt = $pdo->prepare("SELECT * FROM $escapedTable WHERE $escapedIdField = ?");
        $stmt->execute([$idValue]);
        $existingRecord = $stmt->fetch(PDO::FETCH_ASSOC);

        $insertionOuModificationEffectuee = false;

        if (!$existingRecord) {
            // Construction sécurisée de la requête INSERT
            $escapedColumns = array_map('escapeSqlIdentifier', array_keys($filteredData));
            $columnsList = implode(', ', $escapedColumns);
            $placeholders = implode(', ', array_fill(0, count($filteredData), '?'));
            $sql = "INSERT INTO $escapedTable ($columnsList) VALUES ($placeholders)";

            $stmt = $pdo->prepare($sql);
            if ($stmt->execute(array_values($filteredData))) {
                $insertionOuModificationEffectuee = true;
            } else {
                $errorInfo = $stmt->errorInfo();
                file_put_contents('error.log', date('Y-m-d H:i:s') . " - Erreur INSERT: " . $errorInfo[2] . "\n", FILE_APPEND);
                return ["status" => "error", "message" => "Erreur lors de l'insertion dans la table $tableName"];
            }
        } else {
            if ($filteredData['ASynchroniser'] === "Deleted") {
                $sql = "DELETE FROM $escapedTable WHERE $escapedIdField = ?";
                $stmt = $pdo->prepare($sql);
                if ($stmt->execute([$idValue])) {
                    return ["status" => "success", "message" => "Suppression réussie dans la table $tableName"];
                } else {
                    $errorInfo = $stmt->errorInfo();
                    file_put_contents('error.log', date('Y-m-d H:i:s') . " - Erreur DELETE: " . $errorInfo[2] . "\n", FILE_APPEND);
                    return ["status" => "error", "message" => "Erreur lors de la suppression dans la table $tableName"];
                }
            } else {
                $timestampJson = strtotime($filteredData['DateHeureModif']);
                $timestampTable = strtotime($existingRecord['DateHeureModif']);
                if ($timestampJson <= $timestampTable) {
                    return ['status' => 'skipped', 'message' => "Aucune modification nécessaire pour la table $tableName"];
                }

                $updateColumns = [];
                $updateParams = [];
                foreach ($filteredData as $key => $value) {
                    if ($key !== $idField && $key !== 'DateHeureCree') {
                        $escapedKey = escapeSqlIdentifier($key);
                        $updateColumns[] = "$escapedKey = ?";
                        $updateParams[] = $value;
                    }
                }
                $updateParams[] = $idValue;

                $updateSql = "UPDATE $escapedTable SET " . implode(', ', $updateColumns) . " WHERE $escapedIdField = ?";
                $stmt = $pdo->prepare($updateSql);

                if ($stmt->execute($updateParams)) {
                    $insertionOuModificationEffectuee = true;
                } else {
                    $errorInfo = $stmt->errorInfo();
                    file_put_contents('error.log', date('Y-m-d H:i:s') . " - Erreur UPDATE: " . $errorInfo[2] . "\n", FILE_APPEND);
                    return ["status" => "error", "message" => "Erreur lors de la mise à jour dans la table $tableName"];
                }
            }
        }

        if ($insertionOuModificationEffectuee) {
            // Échappement pour DataSynchro
            $escapedDSColumns = array_map('escapeSqlIdentifier', [
                'IDFichier', 'FichierConcerne', 'Data', 'IDENTREPRISES', 
                'IDPoints', 'DateHeureCree', 'DateHeureModif', 'AdMac', 
                'ASynchroniser', 'IDDataSynchro'
            ]);
            
            $pdo->prepare("DELETE FROM " . escapeSqlIdentifier('DataSynchro') . 
                         " WHERE " . escapeSqlIdentifier('IDFichier') . " = ?")->execute([$idValue]);

            $stmt = $pdo->prepare("INSERT INTO " . escapeSqlIdentifier('DataSynchro') . 
                                 " (" . implode(', ', $escapedDSColumns) . ") 
                                  VALUES (?, ?, ?, ?, ?, NOW(), NOW(), ?, ?, UUID())");
            $idPoints = $filteredData['IDPoints'] ?? "00000000-0000-0000-0000-000000000000";
            $stmt->execute([
                $idValue, 
                $tableName, 
                json_encode($filteredData, JSON_UNESCAPED_UNICODE), 
                $filteredData['IDENTREPRISES'], 
                $idPoints, 
                $filteredData['AdMac'], 
                $filteredData['ASynchroniser']
            ]);

            return ["status" => "success", "message" => "Traitement terminé pour la table $tableName"];
        }

        return ["status" => "skipped", "message" => "Aucune modification nécessaire pour la table $tableName"];

    } catch (PDOException $e) {
        file_put_contents('error.log', date('Y-m-d H:i:s') . " - Erreur PDO: " . $e->getMessage() . "\n", FILE_APPEND);
        return ["status" => "error", "message" => "Erreur base de données: " . $e->getMessage()];
    }
}
?>