<?php
/**
* SeekQuarry/Yioop --
* Open Source Pure PHP Search Engine, Crawler, and Indexer
*
* Copyright (C) 2009 - 2026 Chris Pollett chris@pollett.org
*
* LICENSE:
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program. If not, see <https://www.gnu.org/licenses/>.
*
* END LICENSE
*
* @author Chris Pollett chris@pollett.org
* @license https://www.gnu.org/licenses/ GPL3
* @link https://www.seekquarry.com/
* @copyright 2009 - 2026
* @filesource
*/
namespace seekquarry\yioop\models;
use seekquarry\yioop\configs as C;
/**
* Persists scheduled-send mail messages for the Mail activity. Each
* row holds the full RFC822 MIME bytes the user composed, the SMTP
* envelope (from + recipient list), the account it should be sent
* through, and the scheduled wall-clock time. A dispatcher (run from
* page-load lazily plus an optional cron job hitting
* MachineController::scheduledMailDispatch) picks up due rows,
* delivers them via SmtpClient, and deletes the row on success or
* marks it failed with the error message.
*
* The MIME bytes column is sized at TEXT (effectively unbounded for
* the supported DBs) since attachments can push a single message
* past a few megabytes. The envelope is stored separately rather
* than re-parsed from headers at dispatch time so a malformed
* header set cannot mask a recipient.
*
* All public methods are user-scoped: the caller passes $user_id and
* the model refuses to surface rows belonging to any other user.
* This is the access-control boundary; controllers should not
* bypass it. Cron-time dispatch uses an unfiltered listDue() which
* is the one method that crosses the user boundary; callers of
* listDue() must therefore be daemon code, never user-facing
* request handlers.
*
* @author Chris Pollett
*/
class MailScheduledModel extends Model
{
/**
* Status value for a message that hasn't been attempted yet.
*/
const STATUS_PENDING = 'pending';
/**
* Status value for a message currently being delivered. Marked
* transiently by the dispatcher so a second concurrent
* dispatcher pass won't re-pick the same row. Reset to pending
* (or deleted) before the dispatcher returns.
*/
const STATUS_SENDING = 'sending';
/**
* Status value for a message whose last delivery attempt
* failed. LAST_ERROR carries the reason; ATTEMPT_COUNT carries
* the number of tries. The dispatcher will retry failed rows
* up to MAIL_SCHEDULED_MAX_ATTEMPTS times with backoff.
*/
const STATUS_FAILED = 'failed';
/**
* Returns the pending scheduled messages for a user, ordered by
* scheduled-for time (soonest first). Failed messages are
* included so the Scheduled folder UI can show them with an
* error indicator and let the user cancel or reschedule. Sent
* messages have been deleted from this table so they never
* appear here. BODY_TEXT and BODY_HTML are NOT included; use
* getMessage to retrieve.
*
* @param int $user_id Yioop USER_ID whose scheduled messages
* to list
* @param int $account_id optional filter to one account, or 0
* for all accounts owned by the user
* @return array list of row arrays
*/
public function getMessagesForUser($user_id, $account_id = 0)
{
$db = $this->db;
$user_id = (int) $user_id;
$account_id = (int) $account_id;
$where = "USER_ID = ?";
$args = [$user_id];
if ($account_id > 0) {
$where .= " AND ACCOUNT_ID = ?";
$args[] = $account_id;
}
$sql = "SELECT ID, ACCOUNT_ID, SCHEDULED_AT, CREATED_AT, " .
"STATUS, LAST_ERROR, ATTEMPT_COUNT, SUBJECT, " .
"TO_LIST, CC_LIST, BCC_LIST FROM MAIL_SCHEDULED " .
"WHERE $where ORDER BY SCHEDULED_AT ASC";
$result = $db->execute($sql, $args);
$rows = [];
if ($result) {
while ($row = $db->fetchArray($result)) {
$rows[] = $row;
}
}
return $rows;
}
/**
* Returns one scheduled message including the body fields and
* threading headers. Scoped to $user_id so a controller cannot
* accidentally surface another user's draft.
*
* @param int $id row id
* @param int $user_id Yioop USER_ID who must own the row
* @return array|null row array with full composer payload, or
* null if not found / not owned by user
*/
public function getMessage($id, $user_id)
{
$db = $this->db;
$sql = "SELECT * FROM MAIL_SCHEDULED " .
"WHERE ID = ? AND USER_ID = ?";
$result = $db->execute($sql, [(int) $id, (int) $user_id]);
if (!$result) {
return null;
}
return $db->fetchArray($result) ?: null;
}
/**
* Returns scheduled messages whose SCHEDULED_AT has arrived and
* which haven't yet exceeded the retry cap. Used by the
* dispatcher; intentionally NOT user-scoped (the dispatcher
* runs on behalf of every user). Callers must not pass the
* returned rows to user-facing rendering without re-checking
* USER_ID against the requesting session.
*
* @param int $now current unix timestamp (passed in so tests
* can advance time deterministically)
* @param int $max_attempts retries beyond this count are
* skipped; the row stays in the failed state until the
* user cancels or reschedules
* @param int $limit maximum rows to return per call so a
* single dispatcher pass doesn't hold the DB lock too
* long on a backlog
* @return array list of row arrays with full composer payload
*/
public function listDue($now, $max_attempts, $limit = 50)
{
$db = $this->db;
$sql = "SELECT * FROM MAIL_SCHEDULED " .
"WHERE SCHEDULED_AT <= ? " .
"AND STATUS IN ('" . self::STATUS_PENDING . "', '" .
self::STATUS_FAILED . "') " .
"AND ATTEMPT_COUNT < ? " .
"ORDER BY SCHEDULED_AT ASC " .
"LIMIT " . ((int) $limit);
$result = $db->execute($sql,
[(int) $now, (int) $max_attempts]);
$rows = [];
if ($result) {
while ($row = $db->fetchArray($result)) {
$rows[] = $row;
}
}
return $rows;
}
/**
* Inserts a new scheduled-send message. Returns the new row id
* so the caller can record it in flash messages or session
* state. STATUS starts as 'pending', ATTEMPT_COUNT at 0,
* LAST_ERROR is null.
*
* @param int $user_id owner
* @param int $account_id mail account to send through
* @param int $scheduled_at unix timestamp; when to send
* @param array $composer payload from the compose form with
* keys: subject, to_list, cc_list, bcc_list, body_text,
* body_html, in_reply_to, references
* @return int|false the new row id, or false on failure
*/
public function add($user_id, $account_id, $scheduled_at,
$composer)
{
$db = $this->db;
$now = time();
$sql = "INSERT INTO MAIL_SCHEDULED (USER_ID, ACCOUNT_ID, " .
"SCHEDULED_AT, CREATED_AT, STATUS, LAST_ERROR, " .
"ATTEMPT_COUNT, SUBJECT, TO_LIST, CC_LIST, BCC_LIST, " .
"BODY_TEXT, BODY_HTML, IN_REPLY_TO, " .
"REFERENCES_HEADER) VALUES (?, ?, ?, ?, ?, NULL, 0, " .
"?, ?, ?, ?, ?, ?, ?, ?)";
$ok = $db->execute($sql, [(int) $user_id,
(int) $account_id, (int) $scheduled_at, $now,
self::STATUS_PENDING,
(string) ($composer['subject'] ?? ''),
(string) ($composer['to_list'] ?? ''),
(string) ($composer['cc_list'] ?? ''),
(string) ($composer['bcc_list'] ?? ''),
(string) ($composer['body_text'] ?? ''),
(string) ($composer['body_html'] ?? ''),
(string) ($composer['in_reply_to'] ?? ''),
(string) ($composer['references'] ?? '')]);
if (!$ok) {
return false;
}
return $db->insertID();
}
/**
* Removes a scheduled message. Scoped to $user_id so the
* Cancel button in the Scheduled folder cannot reach across
* accounts.
*
* @param int $id row id to delete
* @param int $user_id Yioop USER_ID who must own the row
* @return bool true if a row was deleted, false otherwise
*/
public function deleteMessage($id, $user_id)
{
$db = $this->db;
$sql = "DELETE FROM MAIL_SCHEDULED WHERE ID = ? AND " .
"USER_ID = ?";
return (bool) $db->execute($sql,
[(int) $id, (int) $user_id]);
}
/**
* Removes a scheduled message after successful delivery. Used
* by the dispatcher; not user-scoped because the dispatcher
* has already verified ownership by virtue of being given the
* row from listDue. Separated from deleteMessage so the
* intent at the call site is clear and so any future audit
* trail can be wired only on the dispatcher path.
*
* @param int $id row id to delete
* @return bool true if the row was deleted
*/
public function deleteAfterSend($id)
{
$db = $this->db;
$sql = "DELETE FROM MAIL_SCHEDULED WHERE ID = ?";
return (bool) $db->execute($sql, [(int) $id]);
}
/**
* Records a delivery failure: increments ATTEMPT_COUNT, sets
* STATUS to failed, and stores the error message. Used by
* the dispatcher when SMTP send raises an error.
*
* @param int $id row id to mark
* @param string $error error message from the failed attempt;
* truncated to a reasonable column width
* @return bool true if the row was updated
*/
public function markFailed($id, $error)
{
$db = $this->db;
$clip = substr((string) $error, 0, 500);
$sql = "UPDATE MAIL_SCHEDULED SET STATUS = ?, " .
"LAST_ERROR = ?, ATTEMPT_COUNT = ATTEMPT_COUNT + 1 " .
"WHERE ID = ?";
return (bool) $db->execute($sql,
[self::STATUS_FAILED, $clip, (int) $id]);
}
/**
* Transitions a row to the sending state so a second
* concurrent dispatcher invocation cannot pick the same row.
* Returns true only if the row was actually transitioned
* (i.e. it was still pending or failed when we tried), so
* callers can use the result as an "I own this row now" lock.
*
* @param int $id row id to claim
* @return bool true if claim succeeded; false if another
* worker got there first
*/
public function claim($id)
{
$db = $this->db;
$sql = "UPDATE MAIL_SCHEDULED SET STATUS = ? WHERE ID = ? " .
"AND STATUS IN (?, ?)";
$result = $db->execute($sql, [self::STATUS_SENDING,
(int) $id, self::STATUS_PENDING,
self::STATUS_FAILED]);
if (!$result) {
return false;
}
$verify = $db->execute("SELECT STATUS FROM MAIL_SCHEDULED " .
"WHERE ID = ?", [(int) $id]);
if (!$verify) {
return false;
}
$row = $db->fetchArray($verify);
return $row && $row['STATUS'] === self::STATUS_SENDING;
}
/**
* Returns a row to the pending state if a sending attempt was
* abandoned without resolution (e.g. the dispatcher process
* died mid-send). Symmetric with claim(); callers should
* invoke this in a finally block around the send so a dropped
* connection doesn't leave a row stuck in sending forever.
*
* @param int $id row id to release
* @return bool true if released
*/
public function release($id)
{
$db = $this->db;
$sql = "UPDATE MAIL_SCHEDULED SET STATUS = ? WHERE ID = ? " .
"AND STATUS = ?";
return (bool) $db->execute($sql,
[self::STATUS_PENDING, (int) $id,
self::STATUS_SENDING]);
}
}
(c) 2026 Seekquarry - Open Source Search Engine Software