public function createSmsCampaign($campaignName, $startDate, $startTime, $endDate, $endTime, $ownerId){
    $dbAccess = $this->getMysqlConnection();
    $inserted = 0;

    if (!$dbAccess['dbConnection']) {
        $logDebug = "Echec createSmsCampaign()  " . $dbAccess['errorMessage'] . "  " . $dbAccess['errorCode'];
        $this->svaLog->writeSystemLog($logDebug);
        die("IMPOSSIBLE DE SE CONNECTER A LA BASE DE DONNEES");
    } else {
        $sqlQuery = "
            INSERT INTO master_campaigns_tab (
                campaign_id_n,
                creation_date_d,
                campaign_name_v,
                internal_code_v,
                start_date_d,
                start_time_d,
                end_date_d,
                end_time_d,
                owner_id_n,
                priority_n,
                sender_id_n,
                message_v,
                appid,
                module_id_n,
                char_field_1,
                char_field_2,
                char_field_3,
                char_field_4,
                char_field_5,
                char_field_6,
                char_field_7,
                char_field_8,
                char_field_9,
                char_field_10,
                status_n
            ) VALUES (
                NULL,
                NOW(),
                '$campaignName',
                NULL,
                '$startDate',
                '$startTime',
                '$endDate',
                '$endTime',
                '$ownerId',
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL, NULL, NULL, NULL, NULL, NULL,2, NULL, NULL, NULL,
                -1
            )";

        $queryResult = mysql_query($sqlQuery, $dbAccess['dbConnection']);

        if ($queryResult) {
            $inserted = mysql_insert_id(); // retourne l'ID si succès
        }

        $this->closeMysqlConnection($dbAccess['dbConnection']);
    }

    return $inserted; 
}


public function updateSmsCampaign($campaignId, $campaignName, $startDate, $startTime, $endDate, $endTime)
{
    $dbAccess = $this->connectMysqlDatabase();

    if (!$dbAccess['dbConnection']) {
        $logDebug = "Échec updateSmsCampaign() : " . $dbAccess['errorMessage'];
        $this->svaLog->writeSystemLog($logDebug);
        return false;
    }

    $sql = "
    UPDATE master_campaigns_tab
    SET campaign_name_v = '" . mysql_real_escape_string($campaignName) . "',
        start_date_d = '" . mysql_real_escape_string($startDate) . "',
        start_time_d = '" . mysql_real_escape_string($startTime) . "',
        end_date_d = '" . mysql_real_escape_string($endDate) . "',
        end_time_d = '" . mysql_real_escape_string($endTime) . "'
    WHERE campaign_id_n = " . intval($campaignId);
     

    $result = mysql_query($sql, $dbAccess['dbConnection']);

    if (!$result) {
        $logDebug = "Erreur SQL updateSmsCampaign : " . mysql_error();
        $this->svaLog->writeSystemLog($logDebug);
    }

    $this->closeMysqlConnection($dbAccess['dbConnection']);

    return $result ? true : false;
}

public function deleteAllCampaignPhones($campaignId)
{
    $dbAccess = $this->connectMysqlDatabase();

    if (!$dbAccess['dbConnection']) {
        $logDebug = "Échec deleteAllCampaignPhones() : " . $dbAccess['errorMessage'];
        $this->svaLog->writeSystemLog($logDebug);
        return false;
    }

    $sql = "DELETE FROM queues_processor_tab WHERE campaign_id_n = " . intval($campaignId);

    $result = mysql_query($sql, $dbAccess['dbConnection']);

    if (!$result) {
        $logDebug = "Erreur SQL deleteAllCampaignPhones : " . mysql_error();
        $this->svaLog->writeSystemLog($logDebug);
    }

    $this->closeMysqlConnection($dbAccess['dbConnection']);

    return $result ? true : false;
}



public function getSCCSmsDetails($ownerId) {
    $dbAccess = $this->connectMysqlDatabase();
    $result = array();

    if (!$dbAccess['dbConnection']) {
        return $result;
    }

    $sql = "SELECT 
                campaign_id_n,
                creation_date_d,
                campaign_name_v,
                status_n,
                CASE 
                    WHEN status_n = -1 THEN 'Initialisée'
                    WHEN status_n = 6 THEN 'DEMARREE'
                    WHEN status_n = 3 THEN 'Clôturée'
                    WHEN status_n = 4 THEN 'PAUSE'
                    ELSE 'Inconnu'
                END AS description_v
            FROM master_campaigns_tab
            WHERE char_field_7 = 2"; 

    if ($ownerId > 1) {
        $sql .= " AND owner_id_n = '$ownerId'";
    }

    $sql .= " ORDER BY creation_date_d DESC";

    $queryResult = mysql_query($sql, $dbAccess['dbConnection']);

    while ($row = mysql_fetch_assoc($queryResult)) {
        $result[] = $row;
    }

    $this->closeMysqlConnection($dbAccess['dbConnection']);
    return $result;
}

public function updateSmsCampaignStatus($campaignId, $newStatus)
{
    $dbAccess = $this->connectMysqlDatabase();

    if (!$dbAccess['dbConnection']) {
        $logDebug = "Échec updateSmsCampaignStatus() - " . $dbAccess['errorMessage'] . " | " . $dbAccess['errorCode'];
        $this->svaLog->writeSystemLog($logDebug);
        die("Impossible de se connecter à la base de données");
    }

    $campaignId = mysql_real_escape_string($campaignId);
    $newStatus = mysql_real_escape_string($newStatus);

    $logDebug = "updateSmsCampaignStatus() : campaign_id=$campaignId => status=$newStatus";
    $this->svaLog->writeSystemLog($logDebug);

    $sqlQuery = "
        UPDATE master_campaigns_tab
        SET status_n = '$newStatus'
        WHERE campaign_id_n = '$campaignId'
    ";

    $sqlResult = mysql_query($sqlQuery, $dbAccess['dbConnection']);

    $this->closeMysqlConnection($dbAccess['dbConnection']);

    return ($sqlResult !== false);
}

public function getSCCSmsCampaignById($campaignId)
{
    $dbAccess = $this->connectMysqlDatabase();

    if (!$dbAccess['dbConnection']) {
        $logDebug = "Échec getSCCSmsCampaignById() - " . $dbAccess['errorMessage'] . " | " . $dbAccess['errorCode'];
        $this->svaLog->writeSystemLog($logDebug);
        die("Impossible de se connecter à la base de données");
    }

    $sqlQuery = "
        SELECT 
            campaign_id_n,
            campaign_name_v,
            DATE_FORMAT(start_date_d, '%Y-%m-%d') AS start_date,
            DATE_FORMAT(end_date_d, '%Y-%m-%d') AS end_date,
            TIME_FORMAT(start_time_d, '%H:%i:%s') AS start_time,
            TIME_FORMAT(end_time_d, '%H:%i:%s') AS end_time,
            owner_id_n
        FROM master_campaigns_tab
        WHERE campaign_id_n = '" . mysql_real_escape_string($campaignId) . "'
    ";

    $sqlResult = mysql_query($sqlQuery, $dbAccess['dbConnection']);
    $this->closeMysqlConnection($dbAccess['dbConnection']);

    return $this->mysqlRessourceJson($sqlResult);
}

public function deleteSmsCampaign($campaignId) {
    $dbAccess = $this->connectMysqlDatabase();
    
    if (!$dbAccess['dbConnection']) {
        $logDebug = "Échec deleteSmsCampaign() : " . $dbAccess['errorMessage'];
        $this->svaLog->writeSystemLog($logDebug);
        return false;
    }
    
    // D'abord supprimer les numéros associés
    $this->deleteAllCampaignPhones($campaignId);
    
    // Puis supprimer la campagne
    $sql = "DELETE FROM master_campaigns_tab 
            WHERE campaign_id_n = " . intval($campaignId) . "
            AND char_field_7 = 2"; // Vérification que c'est une campagne SMS
    
    $result = mysql_query($sql, $dbAccess['dbConnection']);
    
    if (!$result) {
        $logDebug = "Erreur SQL deleteSmsCampaign : " . mysql_error();
        $this->svaLog->writeSystemLog($logDebug);
    }
    
    $this->closeMysqlConnection($dbAccess['dbConnection']);
    
    return $result ? true : false;
}

public function deleteSmsCampaign($campaignId) {
    $dbAccess = $this->connectMysqlDatabase();
    
    if (!$dbAccess['dbConnection']) {
        $this->svaLog->writeSystemLog("Connexion DB impossible");
        return false;
    }

    
    $success = true;
    
    // 1. D'abord supprimer les numéros associés
    $sqlPhones = "DELETE FROM queues_processor_tab 
                 WHERE campaign_id_n = " . intval($campaignId);
    
    if (!mysql_query($sqlPhones, $dbAccess['dbConnection'])) {
        $this->svaLog->writeSystemLog("Erreur suppression numéros: " . mysql_error());
        $success = false;
    }
    
    // 2. Puis supprimer la campagne elle-même
    if ($success) {
        $sqlCampaign = "DELETE FROM master_campaigns_tab 
                       WHERE campaign_id_n = " . intval($campaignId) . "
                       AND char_field_7 = 2";
        
        if (!mysql_query($sqlCampaign, $dbAccess['dbConnection'])) {
            $this->svaLog->writeSystemLog("Erreur suppression campagne: " . mysql_error());
            $success = false;
        }
    }
    
    $this->closeMysqlConnection($dbAccess['dbConnection']);
    return $success;
}