<?php
/**
 * EMAIL
 * Author: Aarti Ojha
 */

date_default_timezone_set('Asia/Kolkata');

use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

require_once dirname(__DIR__, 3) . '/config/app_paths.php';
require_once BIS_CAMPAIGN_ROOT . '/vendor/autoload.php';
include_once BIS_CAMPAIGN_ROOT . "/config/web_mysqlconnect.php";

// Master DB
$masterdb = 'CampaignTracker';
global $configdbhost, $configdbuser, $configdbpass;

// DB connection
$link = mysqli_connect($configdbhost, $configdbuser, $configdbpass);
if (!$link) {
    die('Failed to connect to CampaignTracker database.');
}

// Get tenant DBs
$query = "SELECT related_database_name FROM $masterdb.companies";
$stmts = mysqli_prepare($link, $query);
mysqli_stmt_execute($stmts);
$results = mysqli_stmt_get_result($stmts);

if (mysqli_num_rows($results) > 0) {
    while ($company = $results->fetch_assoc()) {
        $childdb = $company['related_database_name'];
        echo "############### Company Database Name: " . $childdb . "<br/>";
        Email_incoming_data($childdb, $link);
    }
} else {
    echo "No company databases found.<br/><br/>";
    die();
}

function Email_incoming_data($childdb, $link) {

    // SMTP config
    $sql_cdr = "SELECT * FROM $childdb.tbl_smtp_connection WHERE status=1 LIMIT 1";
    $query = mysqli_query($link, $sql_cdr);
    $config = mysqli_fetch_array($query);

    if (!$config) {
        echo "❌ SMTP config not found for $childdb <br/>";
        return;
    }

    $v_username = $config['v_username'];
    $v_password = $config['v_password'];
    $v_server   = $config['v_server'];
    $i_tls      = $config['i_tls'];
    $i_port     = $config['i_port'];
    $ATTACHMENT_PATH = BIS_CAMPAIGN_WEB_ROOT . "/";

    // ✅ Pending emails with LIMIT
    $qry = "SELECT EMAIL_ID, v_toemail, v_subject, v_body, add_bcc, i_RetryCount, V_rule 
            FROM $childdb.web_email_information_out 
            WHERE email_type = 'OUT' 
            AND I_Status = '1' 
            AND expiry_flag='0'
            AND (
                  (schedule_flag = '1' AND scheduling_date <= NOW()) 
                  OR schedule_flag IS NULL 
                  OR schedule_flag = '0'
            ) 
            AND v_toemail NOT IN ('rep-57@zra.org.zm', 'rep-71@zra.org.zm')
            LIMIT 20";

    echo $qry . "<br/><br/>";

    $res = mysqli_query($link, $qry);

    if (!$res) {
        echo "Error in query [$qry].<br/><br/>";
        return;
    }

    if (mysqli_num_rows($res) == 0) {
        echo "No more emails to send.<br/><br/>";
        return;
    }

    // Process emails (NO LOOP HERE)
    while ($row = mysqli_fetch_assoc($res)) {

        $MailID   = $row['EMAIL_ID'];
        echo "Processing Mail ID: $MailID <br/>";

        $toAddr   = $row['v_toemail'];
        $subject  = $row['v_subject'];
        $V_Body   = $row['v_body'];
        $add_bcc  = $row['add_bcc'];
        $fileNames= $row['V_rule'];

        // ✅ Retry safe
        $iRetry = (int)$row['i_RetryCount'];
        $iRetry++;

        // PHPMailer setup
        $mail = new PHPMailer;
        $mail->isSMTP();

        if ($i_tls == "1") {
            $mail->SMTPAuth   = true;
            $mail->SMTPSecure = "tls";
        }

        $mail->SMTPDebug = 0;
        $mail->SMTPOptions = array(
            'ssl' => array(
                'verify_peer' => false,
                'verify_peer_name' => false,
                'allow_self_signed' => true
            )
        );

        $mail->Host     = $v_server;
        $mail->Port     = $i_port;
        $mail->Username = $v_username;
        $mail->Password = $v_password;
        $mail->setFrom($v_username, $v_username);

        $mail->addAddress($toAddr);

        if (!empty($add_bcc)) {
            $mail->addBCC($add_bcc);
        }

        $mail->isHTML(true);
        $mail->Subject = $subject;
        $mail->Body    = $V_Body;

        // Attachment
        if (!empty($fileNames)) {
            $filePath = $ATTACHMENT_PATH . str_replace("../", "", $fileNames);
            if (file_exists($filePath)) {
                $mail->addAttachment($filePath);
            }
        }

        // Send mail
        if ($mail->send()) {

            echo "✅ Mail sent to: $toAddr [ID= $MailID]<br/>";

            $qryUpdate = "UPDATE $childdb.web_email_information_out 
                          SET I_Status = 2,
                              i_RetryCount = $iRetry,
                              v_LastError='Success',
                              d_RetryTime=NOW()
                          WHERE EMAIL_ID = $MailID";

            mysqli_query($link, $qryUpdate);

        } else {

            echo "❌ Mail not sent. Error: " . $mail->ErrorInfo . "<br/>";

            $error = addslashes($mail->ErrorInfo);
            $iStatus = ($iRetry > 3) ? 3 : 1;

            $qryUpdate = "UPDATE $childdb.web_email_information_out 
                          SET I_Status = $iStatus,
                              i_RetryCount = $iRetry,
                              v_LastError='$error',
                              d_RetryTime=NOW()
                          WHERE EMAIL_ID = $MailID";

            mysqli_query($link, $qryUpdate);
        }
    }

    mysqli_free_result($res);
}

/**
 * CC mail function (unchanged)
 */
function get_cc_mail_id() {
    global $link, $db;
    $query = $link->query("SELECT cc_mail_id FROM $db.tbl_connection");
    $row = $query->fetch_assoc();
    return $row['cc_mail_id'] ?? null;
}
?>