Twilio SMS Script

<?php
/**
 * SMS Reminder Script
 * Optimized, refactored, and moderately commented
 */

$Lifetime = 86400; // 24 hours
ini_set("session.cookie_lifetime", $Lifetime);
ini_set("session.gc_maxlifetime", $Lifetime);

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

// -----------------------------
// Helper: JSON response
// -----------------------------
function echo_error($txt, $status)
{
    $class = $status === "OK" ? "alert-success" : "alert-warning";
    $msg = "<div class='alert alert-dismissible $class'><i class='fas fa-star'></i> $txt</div>";
    echo json_encode([
        "status" => $status,
        "data" => $msg
    ]);
    exit;
}

// -----------------------------
// Validate session user
// -----------------------------
if (!isset($_SESSION['invoice_user'])) {
    echo_error("Invalid User.", "ERROR");
}

// -----------------------------
// Dependencies
// -----------------------------
require_once '../db.php';
require_once '../vendor/autoload.php';
use Twilio\Rest\Client;

// -----------------------------
// Execution settings
// -----------------------------
set_time_limit(0);
ignore_user_abort(true);
ob_implicit_flush(true);
ob_end_flush();

// -----------------------------
// Regex for email (unused currently)
// -----------------------------
$email_regex = "/^[_a-zA-Z0-9_]+(\.[_a-zA-Z0-9-]+)*@[a-zA-Z0-9-]+(\.[a-zA-Z0-9-]+)*(\.[a-zA-Z]{2,4})$/";

// -----------------------------
// Database query for pending reminders
// -----------------------------
$check_query = <<<SQL
SELECT 
    TXT.LeadhistId AS invoiceid, 
    TXT.Id AS trow_id, 
    L.firstname, 
    L.lastname, 
    L.zip, 
    REPLACE(L.phonenumber, '-', '') AS phonenumber, 
    LH.invoiceamt, 
    CONVERT(VARCHAR(15), LH.OrderDate, 110) AS orderdate,
    TXT.lookupchecked
FROM Campaigns.dbo.TXT_Hist TXT
INNER JOIN Campaigns.dbo.Leadhist LH ON TXT.leadhistid = LH.leadhistid
INNER JOIN Campaigns.dbo.Leads L ON LH.leadid = L.leadid
WHERE TXT.OptOut = 0
  AND Txt.DateSent <= DATEADD(day,-14,CONVERT(VARCHAR(10), GETDATE(), 101))
  AND Reminderqueue = 1
  AND LH.PaymentAmt = 0
  AND TxtSent = 1
  AND (ReminderSent IS NULL OR ReminderSent = 0)
  AND TXT.InvalidNumber != 1
  AND LH.InvoiceProjectId IN (100,101,106)
SQL;

// -----------------------------
// Connect DB and fetch rows
// -----------------------------
$d = new DBRead();
$conn = $d->connect();
$do_check = sqlsrv_query($conn, $check_query);

if (!$do_check) {
    $error = print_r(sqlsrv_errors(), true);
    logError("DB Error: $error", 'error');
    echo_error($error, "ERROR");
}

if (!sqlsrv_has_rows($do_check)) {
    echo_error("No records.", "ERROR");
}

// -----------------------------
// Collect rows into array
// -----------------------------
$data_rows = [];
while ($row = sqlsrv_fetch_array($do_check, SQLSRV_FETCH_ASSOC)) {
    $data_rows[] = [
        "phonenumber"    => $row['phonenumber'],
        "invoiceid"      => $row['invoiceid'],
        "firstname"      => $row['firstname'],
        "lastname"       => $row['lastname'],
        "zip"            => $row['zip'],
        "invoiceamt"     => $row['invoiceamt'],
        "date"           => $row['orderdate'],
        "lookupchecked"  => $row['lookupchecked'],
        "table_row_id"   => $row['trow_id'],
    ];
}

// -----------------------------
// Report number of records
// -----------------------------
$total_rows = count($data_rows);
$realtime = date("j/m/Y H:i:s");
echo json_encode([
    "status" => "OK",
    "type_r" => "header",
    "data" => "[$realtime] <center><h2>$total_rows Records Returned</h2></center><br><br>",
    "log" => "yes"
]);
sleep(1);

// -----------------------------
// Twilio client init
// -----------------------------
$client = new Client(ACCOUNT_SID, AUTH_TOKEN);

$voip = $landline = 0;

// -----------------------------
// Process each row
// -----------------------------
foreach ($data_rows as $row) {
    $phonenumber = $row['phonenumber'];
    $invoiceid   = $row['invoiceid'];
    $firstname   = $row['firstname'];
    $lastname    = $row['lastname'];
    $zip         = $row['zip'];
    $invoiceamt  = $row['invoiceamt'];
    $date        = $row['date'];
    $lookupchecked = $row['lookupchecked'];
    $table_row_id = $row['table_row_id'];

    // Skip already checked numbers
    if ($lookupchecked) continue;

    $invalidNumber = 0;

    // -----------------------------
    // Twilio Lookup
    // -----------------------------
    try {
        $lookup = $client->lookups->v1->phoneNumbers($phonenumber)->fetch(["type" => ["carrier"]]);
        $type = strtolower(trim($lookup->carrier['type'] ?? 'unknown'));

        if ($type !== 'mobile') {
            $invalidNumber = 1;
            if ($type === 'landline') $landline++;
            if ($type === 'voip') $voip++;
        }

    } catch (\Twilio\Exceptions\TwilioException $e) {
        logError("Twilio lookup error: ".$e->getMessage(), 'twilio');
        $invalidNumber = 1;
    }

    // Update DB for lookup
    $sql = "UPDATE Campaigns.dbo.TXT_Hist SET InvalidNumber = $invalidNumber, lookupChecked = 1 WHERE [Id] = '$table_row_id'";
    $stmt = sqlsrv_query($conn, $sql);
    if (!$stmt) {
        logError("SQL Error: ".print_r(sqlsrv_errors(), true)." - Query: $sql", 'error');
    }
    sleep(1);

    if ($invalidNumber) continue; // skip non-mobile numbers

    // -----------------------------
    // Prepare SMS
    // -----------------------------
    $user_url = "https://breastcancerpayments.com/campaign.php?invoiceId=".base64_encode($invoiceid)."&zipcode=".base64_encode($zip);
    $sms_message = "Dear $firstname $lastname,\nOn $date you made a \$$invoiceamt pledge to the American Breast Cancer Support Association.\nWe still have not received your donation. Click the link below to pay now:\n$user_url\nText STOP to Opt Out";

    $callback_url = CALLBACK_URL . "&invoiceid=$invoiceid&src=reminder";

    // -----------------------------
    // Send SMS
    // -----------------------------
    try {
        $message = $client->messages->create($phonenumber, [
            'from' => TWILIO_NUMBER,
            'body' => $sms_message,
            'statusCallback' => $callback_url
        ]);

        // Log success
        $logData = date("Y-m-d H:i:s") . " - $phonenumber\n";
        file_put_contents("reminderlog.txt", $logData, FILE_APPEND);

        // Mark reminder as sent
        $sql = "UPDATE Campaigns.dbo.TXT_Hist SET ReminderSent = 1, ReminderDateSent = '".date("Y-m-d H:i:s")."' WHERE [Id] = '$table_row_id'";
        sqlsrv_query($conn, $sql);

    } catch (\Twilio\Exceptions\RestException $e) {
        logError("Error sending SMS: ".$e->getMessage(), 'twilio');
    }

    sleep(1); // prevent rate limit issues
}

// -----------------------------
// Final report
// -----------------------------
echo_error("Landline: $landline - VOIP: $voip", "OK");
?>