mirror of
https://github.com/sky-systems/sky_phone.git
synced 2026-08-29 04:01:32 +00:00
DerEchteAlec
1043 lines
34 KiB
Lua
1043 lines
34 KiB
Lua
Bridge.Database.AfterMigration("sky_phone", function()
|
|
|
|
local MAX_SAFE_INTEGER = 9007199254740991
|
|
local DELETE_FOLDERS = {
|
|
drafts = true,
|
|
inbox = true,
|
|
sent = true,
|
|
trash = true,
|
|
}
|
|
|
|
local function trim(value)
|
|
if type(value) ~= "string" then
|
|
return nil
|
|
end
|
|
|
|
return value:match("^%s*(.-)%s*$")
|
|
end
|
|
|
|
local function validate_payload(source, operation, value)
|
|
if type(value) == "table" then
|
|
return value
|
|
end
|
|
|
|
Bridge.Debug(
|
|
"warn",
|
|
"[sky_phone] Invalid mail payload for %s from source %s.",
|
|
operation,
|
|
tostring(source)
|
|
)
|
|
return nil
|
|
end
|
|
|
|
local function text_length(value)
|
|
if type(value) ~= "string" then
|
|
return nil
|
|
end
|
|
|
|
return utf8.len(value)
|
|
end
|
|
|
|
local function normalize_email(value)
|
|
local email = trim(value)
|
|
if not email then
|
|
return nil
|
|
end
|
|
|
|
email = email:lower()
|
|
local local_part = email
|
|
if email:find("@", 1, true) then
|
|
local_part = email:match("^([^@]+)@" .. Config.Mail.Domain:gsub("%.", "%%.") .. "$")
|
|
end
|
|
|
|
if not local_part
|
|
or #local_part < Config.Mail.LocalPartMinLength
|
|
or #local_part > Config.Mail.LocalPartMaxLength
|
|
or not local_part:match("^[a-z0-9][a-z0-9._-]*[a-z0-9]$")
|
|
or local_part:find("..", 1, true)
|
|
then
|
|
return nil
|
|
end
|
|
|
|
return local_part .. "@" .. Config.Mail.Domain
|
|
end
|
|
|
|
local function validate_text(value, maximum)
|
|
local length = text_length(value)
|
|
return length and length <= maximum
|
|
end
|
|
|
|
local function normalize_mailbox_name(value)
|
|
local name = trim(value)
|
|
local length = text_length(name)
|
|
if not length or length < 1 or length > Config.Mail.MailboxNameMaxLength or name:find("%c") then
|
|
return nil
|
|
end
|
|
|
|
return name
|
|
end
|
|
|
|
local function normalize_numeric_id(value)
|
|
local id = tonumber(value)
|
|
if not id or id ~= id or id < 1 or id > MAX_SAFE_INTEGER or id ~= math.floor(id) then
|
|
return nil
|
|
end
|
|
|
|
return id
|
|
end
|
|
|
|
local function mailbox_id_from_folder(folder)
|
|
if type(folder) ~= "string" then
|
|
return nil
|
|
end
|
|
|
|
local value = folder:match("^mailbox:(%d+)$")
|
|
return value and normalize_numeric_id(value) or nil
|
|
end
|
|
|
|
local function normalize_list_filters(value)
|
|
if value == nil then
|
|
return {
|
|
address = "all",
|
|
direction = "all",
|
|
read = "all",
|
|
today = false,
|
|
}
|
|
end
|
|
if type(value) ~= "table"
|
|
or (value.address ~= "all" and value.address ~= "from-me" and value.address ~= "to-me")
|
|
or (value.direction ~= "all" and value.direction ~= "inbox" and value.direction ~= "sent")
|
|
or (value.read ~= "all" and value.read ~= "read" and value.read ~= "unread")
|
|
or type(value.today) ~= "boolean"
|
|
then
|
|
return nil
|
|
end
|
|
|
|
return {
|
|
address = value.address,
|
|
direction = value.direction,
|
|
read = value.read,
|
|
today = value.today,
|
|
}
|
|
end
|
|
|
|
local function normalize_recipients(values, strict)
|
|
if type(values) ~= "table" or #values > Config.Mail.MaxRecipients then
|
|
return nil
|
|
end
|
|
|
|
local recipients = {}
|
|
local seen = {}
|
|
for _, value in ipairs(values) do
|
|
local recipient
|
|
if strict then
|
|
recipient = normalize_email(value)
|
|
else
|
|
recipient = trim(value)
|
|
if recipient and #recipient > 64 then
|
|
recipient = nil
|
|
end
|
|
end
|
|
|
|
if not recipient then
|
|
return nil
|
|
end
|
|
|
|
local key = recipient:lower()
|
|
if key ~= "" and not seen[key] then
|
|
seen[key] = true
|
|
recipients[#recipients + 1] = recipient
|
|
end
|
|
end
|
|
|
|
return recipients
|
|
end
|
|
|
|
local function normalize_delete_ids(folder, values)
|
|
if type(values) ~= "table" then
|
|
return nil
|
|
end
|
|
|
|
local input_count = 0
|
|
for key in pairs(values) do
|
|
if type(key) ~= "number"
|
|
or key < 1
|
|
or key > Config.Mail.DeleteBatchSize
|
|
or key ~= math.floor(key)
|
|
then
|
|
return nil
|
|
end
|
|
input_count = input_count + 1
|
|
end
|
|
if input_count == 0 or input_count > Config.Mail.DeleteBatchSize then
|
|
return nil
|
|
end
|
|
|
|
local ids = {}
|
|
local seen = {}
|
|
for index = 1, input_count do
|
|
local value = values[index]
|
|
if value == nil then
|
|
return nil
|
|
end
|
|
|
|
local id
|
|
if folder == "drafts" then
|
|
if type(value) ~= "string"
|
|
or not value:match(
|
|
"^%x%x%x%x%x%x%x%x%-%x%x%x%x%-%x%x%x%x%-%x%x%x%x%-%x%x%x%x%x%x%x%x%x%x%x%x$"
|
|
)
|
|
then
|
|
return nil
|
|
end
|
|
id = value:lower()
|
|
else
|
|
if type(value) ~= "number"
|
|
or value ~= value
|
|
or value < 1
|
|
or value > MAX_SAFE_INTEGER
|
|
or value ~= math.floor(value)
|
|
then
|
|
return nil
|
|
end
|
|
id = value
|
|
end
|
|
|
|
if not seen[id] then
|
|
seen[id] = true
|
|
ids[#ids + 1] = id
|
|
end
|
|
end
|
|
|
|
return ids
|
|
end
|
|
|
|
local function make_placeholders(count)
|
|
local placeholders = {}
|
|
for index = 1, count do
|
|
placeholders[index] = "?"
|
|
end
|
|
return table.concat(placeholders, ", ")
|
|
end
|
|
|
|
local function append_values(target, values)
|
|
for index = 1, #values do
|
|
target[#target + 1] = values[index]
|
|
end
|
|
end
|
|
|
|
local function require_session(source)
|
|
return SkyPhone.RequireAccount(source)
|
|
end
|
|
|
|
local function new_database_id()
|
|
local rows = Bridge.Database.Query("SELECT UUID() AS id", {})
|
|
if not rows[1] or type(rows[1].id) ~= "string" then
|
|
error("[sky_phone] Database did not generate a mail id.")
|
|
end
|
|
|
|
return rows[1].id
|
|
end
|
|
|
|
local function notify_account(account_id, event_name, data)
|
|
SkyPhone.NotifyAccount(account_id, event_name, data)
|
|
end
|
|
|
|
local function get_counts(account_id)
|
|
local rows = Bridge.Database.Query([[
|
|
SELECT
|
|
SUM(CASE WHEN `folder` = 'inbox' AND `mailbox_id` IS NULL AND `trashed_at` IS NULL AND `read_at` IS NULL THEN 1 ELSE 0 END) AS unread,
|
|
SUM(CASE WHEN `folder` = 'inbox' AND `mailbox_id` IS NULL AND `trashed_at` IS NULL THEN 1 ELSE 0 END) AS inbox,
|
|
SUM(CASE WHEN `folder` = 'sent' AND `mailbox_id` IS NULL AND `trashed_at` IS NULL THEN 1 ELSE 0 END) AS sent,
|
|
SUM(CASE WHEN `trashed_at` IS NOT NULL THEN 1 ELSE 0 END) AS trash
|
|
FROM `sky_phone_mail_entries`
|
|
WHERE `account_id` = ?
|
|
]], { account_id })
|
|
local drafts = Bridge.Database.Query(
|
|
"SELECT COUNT(*) AS count FROM `sky_phone_mail_drafts` WHERE `account_id` = ?",
|
|
{ account_id }
|
|
)
|
|
local counts = rows[1] or {}
|
|
|
|
return {
|
|
unread = tonumber(counts.unread) or 0,
|
|
inbox = tonumber(counts.inbox) or 0,
|
|
sent = tonumber(counts.sent) or 0,
|
|
trash = tonumber(counts.trash) or 0,
|
|
drafts = tonumber(drafts[1] and drafts[1].count) or 0,
|
|
}
|
|
end
|
|
|
|
local function get_mailboxes(account_id)
|
|
local rows = Bridge.Database.Query([[
|
|
SELECT mailbox.`id`, mailbox.`name`, mailbox.`sort_order`, COUNT(entry.`id`) AS `count`
|
|
FROM `sky_phone_mailboxes` mailbox
|
|
LEFT JOIN `sky_phone_mail_entries` entry
|
|
ON entry.`mailbox_id` = mailbox.`id`
|
|
AND entry.`account_id` = mailbox.`account_id`
|
|
AND entry.`trashed_at` IS NULL
|
|
WHERE mailbox.`account_id` = ?
|
|
GROUP BY mailbox.`id`, mailbox.`name`, mailbox.`sort_order`
|
|
ORDER BY mailbox.`sort_order` ASC, mailbox.`id` ASC
|
|
]], { account_id })
|
|
|
|
for index = 1, #rows do
|
|
rows[index].id = tonumber(rows[index].id)
|
|
rows[index].sort_order = tonumber(rows[index].sort_order) or 0
|
|
rows[index].count = tonumber(rows[index].count) or 0
|
|
end
|
|
|
|
return rows
|
|
end
|
|
|
|
local function find_owned_mailbox(account_id, mailbox_id)
|
|
local rows = Bridge.Database.Query([[
|
|
SELECT `id`, `name`, `sort_order`
|
|
FROM `sky_phone_mailboxes`
|
|
WHERE `id` = ? AND `account_id` = ?
|
|
LIMIT 1
|
|
]], { mailbox_id, account_id })
|
|
return rows[1]
|
|
end
|
|
|
|
local function broadcast_mailbox_changed(account_id, counts)
|
|
notify_account(account_id, "sky_phone:mail:changed", {
|
|
counts = counts or get_counts(account_id),
|
|
})
|
|
end
|
|
|
|
Bridge.Callbacks.Register("sky_phone:mail:counts", function(source)
|
|
local session, error_response = require_session(source)
|
|
if not session then
|
|
return error_response
|
|
end
|
|
|
|
return { success = true, data = get_counts(session.id) }
|
|
end)
|
|
|
|
Bridge.Callbacks.Register("sky_phone:mail:mailboxes", function(source)
|
|
local session, error_response = require_session(source)
|
|
if not session then
|
|
return error_response
|
|
end
|
|
|
|
return { success = true, data = { mailboxes = get_mailboxes(session.id) } }
|
|
end)
|
|
|
|
Bridge.Callbacks.Register("sky_phone:mail:create-mailbox", function(source, data)
|
|
local session, error_response = require_session(source)
|
|
if not session then
|
|
return error_response
|
|
end
|
|
if not SkyPhone.AllowOperation(source, "mail_manage_mailboxes", Config.Mail.MailboxRequestsPerMinute, 60) then
|
|
return { success = false, error = "rate_limited" }
|
|
end
|
|
|
|
data = validate_payload(source, "create-mailbox", data)
|
|
if not data then
|
|
return { success = false, error = "invalid_request" }
|
|
end
|
|
|
|
local name = normalize_mailbox_name(data.name)
|
|
if not name then
|
|
return { success = false, error = "invalid_mailbox" }
|
|
end
|
|
|
|
local rows = Bridge.Database.Query([[
|
|
SELECT COUNT(*) AS `count`,
|
|
SUM(CASE WHEN LOWER(`name`) = LOWER(?) THEN 1 ELSE 0 END) AS `duplicate_count`,
|
|
COALESCE(MAX(`sort_order`), -1) AS `maximum_sort_order`
|
|
FROM `sky_phone_mailboxes`
|
|
WHERE `account_id` = ?
|
|
]], { name, session.id })
|
|
local summary = rows[1] or {}
|
|
if (tonumber(summary.duplicate_count) or 0) > 0 then
|
|
return { success = false, error = "mailbox_exists" }
|
|
end
|
|
if (tonumber(summary.count) or 0) >= Config.Mail.MaxMailboxes then
|
|
return { success = false, error = "mailbox_limit" }
|
|
end
|
|
|
|
local sort_order = math.min(65535, (tonumber(summary.maximum_sort_order) or -1) + 1)
|
|
Bridge.Database.Query([[
|
|
INSERT INTO `sky_phone_mailboxes` (`account_id`, `name`, `sort_order`)
|
|
VALUES (?, ?, ?)
|
|
]], { session.id, name, sort_order })
|
|
|
|
local created = Bridge.Database.Query([[
|
|
SELECT `id`, `name`, `sort_order`, 0 AS `count`
|
|
FROM `sky_phone_mailboxes`
|
|
WHERE `account_id` = ? AND LOWER(`name`) = LOWER(?)
|
|
LIMIT 1
|
|
]], { session.id, name })
|
|
if not created[1] then
|
|
error("[sky_phone] Created mail mailbox could not be loaded.")
|
|
end
|
|
|
|
created[1].id = tonumber(created[1].id)
|
|
created[1].sort_order = tonumber(created[1].sort_order) or 0
|
|
created[1].count = 0
|
|
broadcast_mailbox_changed(session.id)
|
|
return { success = true, data = created[1] }
|
|
end)
|
|
|
|
Bridge.Callbacks.Register("sky_phone:mail:delete-mailbox", function(source, data)
|
|
local session, error_response = require_session(source)
|
|
if not session then
|
|
return error_response
|
|
end
|
|
if not SkyPhone.AllowOperation(source, "mail_manage_mailboxes", Config.Mail.MailboxRequestsPerMinute, 60) then
|
|
return { success = false, error = "rate_limited" }
|
|
end
|
|
|
|
data = validate_payload(source, "delete-mailbox", data)
|
|
if not data then
|
|
return { success = false, error = "invalid_request" }
|
|
end
|
|
|
|
local mailbox_id = normalize_numeric_id(data.id)
|
|
if not mailbox_id or not find_owned_mailbox(session.id, mailbox_id) then
|
|
return { success = false, error = "mailbox_not_found" }
|
|
end
|
|
|
|
if not Bridge.Database.Transaction({
|
|
{
|
|
query = [[
|
|
UPDATE `sky_phone_mail_entries` SET `mailbox_id` = NULL
|
|
WHERE `account_id` = ? AND `mailbox_id` = ?
|
|
]],
|
|
params = { session.id, mailbox_id },
|
|
},
|
|
{
|
|
query = "DELETE FROM `sky_phone_mailboxes` WHERE `id` = ? AND `account_id` = ?",
|
|
params = { mailbox_id, session.id },
|
|
},
|
|
}) then
|
|
return { success = false, error = "request_failed" }
|
|
end
|
|
|
|
broadcast_mailbox_changed(session.id)
|
|
return { success = true }
|
|
end)
|
|
|
|
Bridge.Callbacks.Register("sky_phone:mail:list", function(source, data)
|
|
local session, error_response = require_session(source)
|
|
if not session then
|
|
return error_response
|
|
end
|
|
|
|
data = validate_payload(source, "list", data)
|
|
if not data then
|
|
return { success = false, error = "invalid_request" }
|
|
end
|
|
|
|
local folder = data.folder
|
|
local custom_mailbox_id = mailbox_id_from_folder(folder)
|
|
if folder ~= "inbox" and folder ~= "sent" and folder ~= "drafts" and folder ~= "trash"
|
|
and not custom_mailbox_id
|
|
then
|
|
return { success = false, error = "invalid_folder" }
|
|
end
|
|
if custom_mailbox_id and not find_owned_mailbox(session.id, custom_mailbox_id) then
|
|
return { success = false, error = "mailbox_not_found" }
|
|
end
|
|
|
|
local search = trim(data.search) or ""
|
|
if not validate_text(search, 120) then
|
|
return { success = false, error = "invalid_search" }
|
|
end
|
|
local offset = math.max(0, math.min(100000, math.floor(tonumber(data.offset) or 0)))
|
|
local limit = Config.Mail.PageSize + 1
|
|
local filters = normalize_list_filters(data.filters)
|
|
if not filters then
|
|
return { success = false, error = "invalid_filter" }
|
|
end
|
|
local rows
|
|
|
|
if folder == "drafts" then
|
|
if filters.address ~= "all" or filters.direction ~= "all" or filters.read ~= "all" or filters.today then
|
|
return { success = false, error = "invalid_filter" }
|
|
end
|
|
local pattern = "%" .. search .. "%"
|
|
rows = Bridge.Database.Query([[
|
|
SELECT `id`, `recipients`, `subject`, LEFT(`body`, 180) AS `preview`, `updated_at` AS `created_at`
|
|
FROM `sky_phone_mail_drafts`
|
|
WHERE `account_id` = ? AND (? = '' OR `subject` LIKE ? OR `body` LIKE ? OR `recipients` LIKE ?)
|
|
ORDER BY `updated_at` DESC
|
|
LIMIT ? OFFSET ?
|
|
]], { session.id, search, pattern, pattern, pattern, limit, offset })
|
|
else
|
|
local conditions
|
|
local values = { session.id }
|
|
if custom_mailbox_id then
|
|
conditions = "e.`mailbox_id` = ? AND e.`trashed_at` IS NULL"
|
|
values[#values + 1] = custom_mailbox_id
|
|
elseif folder == "trash" then
|
|
conditions = "e.`trashed_at` IS NOT NULL"
|
|
else
|
|
conditions = "e.`folder` = ? AND e.`mailbox_id` IS NULL AND e.`trashed_at` IS NULL"
|
|
values[#values + 1] = folder
|
|
end
|
|
|
|
if filters.read == "unread" then
|
|
conditions = conditions .. " AND e.`folder` = 'inbox' AND e.`read_at` IS NULL"
|
|
elseif filters.read == "read" then
|
|
conditions = conditions .. " AND (e.`folder` = 'sent' OR e.`read_at` IS NOT NULL)"
|
|
end
|
|
if filters.direction ~= "all" then
|
|
conditions = conditions .. " AND e.`folder` = ?"
|
|
values[#values + 1] = filters.direction
|
|
end
|
|
if filters.address == "to-me" then
|
|
conditions = conditions .. " AND m.`recipients` LIKE ?"
|
|
values[#values + 1] = "%\"" .. session.email .. "\"%"
|
|
elseif filters.address == "from-me" then
|
|
conditions = conditions .. " AND m.`sender_account_id` = e.`account_id`"
|
|
end
|
|
if filters.today then
|
|
conditions = conditions .. " AND m.`created_at` >= CURRENT_DATE() AND m.`created_at` < CURRENT_DATE() + INTERVAL 1 DAY"
|
|
end
|
|
|
|
local pattern = "%" .. search .. "%"
|
|
values[#values + 1] = search
|
|
values[#values + 1] = pattern
|
|
values[#values + 1] = pattern
|
|
values[#values + 1] = pattern
|
|
values[#values + 1] = pattern
|
|
values[#values + 1] = limit
|
|
values[#values + 1] = offset
|
|
|
|
rows = Bridge.Database.Query(([[
|
|
SELECT e.`id`, e.`folder`, e.`read_at`, e.`trashed_at`, m.`id` AS `message_id`,
|
|
sender.`email` AS `sender`, m.`recipients`, m.`subject`, LEFT(m.`body`, 180) AS `preview`,
|
|
m.`created_at`
|
|
FROM `sky_phone_mail_entries` e
|
|
JOIN `sky_phone_mail_messages` m ON m.`id` = e.`message_id`
|
|
JOIN `sky_phone_accounts` sender ON sender.`id` = m.`sender_account_id`
|
|
WHERE e.`account_id` = ? AND %s
|
|
AND (? = '' OR m.`subject` LIKE ? OR m.`body` LIKE ? OR sender.`email` LIKE ? OR m.`recipients` LIKE ?)
|
|
ORDER BY m.`created_at` DESC, e.`id` DESC
|
|
LIMIT ? OFFSET ?
|
|
]]):format(conditions), values)
|
|
end
|
|
|
|
local has_more = #rows > Config.Mail.PageSize
|
|
if has_more then
|
|
rows[#rows] = nil
|
|
end
|
|
for _, row in ipairs(rows) do
|
|
row.recipients = json.decode(row.recipients) or {}
|
|
row.is_read = row.read_at ~= nil
|
|
row.read_at = nil
|
|
end
|
|
|
|
return {
|
|
success = true,
|
|
data = { items = rows, hasMore = has_more, offset = offset },
|
|
}
|
|
end)
|
|
|
|
Bridge.Callbacks.Register("sky_phone:mail:get", function(source, data)
|
|
local session, error_response = require_session(source)
|
|
if not session then
|
|
return error_response
|
|
end
|
|
|
|
data = validate_payload(source, "get", data)
|
|
if not data then
|
|
return { success = false, error = "invalid_request" }
|
|
end
|
|
|
|
local entry_id = tonumber(data.id)
|
|
if not entry_id then
|
|
return { success = false, error = "invalid_message" }
|
|
end
|
|
|
|
local rows = Bridge.Database.Query([[
|
|
SELECT e.`id`, e.`folder`, e.`read_at`, e.`trashed_at`, m.`id` AS `message_id`,
|
|
sender.`email` AS `sender`, m.`recipients`, m.`subject`, m.`body`, m.`created_at`
|
|
FROM `sky_phone_mail_entries` e
|
|
JOIN `sky_phone_mail_messages` m ON m.`id` = e.`message_id`
|
|
JOIN `sky_phone_accounts` sender ON sender.`id` = m.`sender_account_id`
|
|
WHERE e.`id` = ? AND e.`account_id` = ?
|
|
LIMIT 1
|
|
]], { entry_id, session.id })
|
|
if not rows[1] then
|
|
return { success = false, error = "message_not_found" }
|
|
end
|
|
|
|
if not rows[1].read_at then
|
|
Bridge.Database.Query(
|
|
"UPDATE `sky_phone_mail_entries` SET `read_at` = CURRENT_TIMESTAMP WHERE `id` = ? AND `account_id` = ?",
|
|
{ entry_id, session.id }
|
|
)
|
|
rows[1].read_at = os.date("%Y-%m-%d %H:%M:%S")
|
|
broadcast_mailbox_changed(session.id)
|
|
end
|
|
|
|
rows[1].recipients = json.decode(rows[1].recipients) or {}
|
|
rows[1].is_read = true
|
|
rows[1].read_at = nil
|
|
return { success = true, data = rows[1] }
|
|
end)
|
|
|
|
Bridge.Callbacks.Register("sky_phone:mail:get-draft", function(source, data)
|
|
local session, error_response = require_session(source)
|
|
if not session then
|
|
return error_response
|
|
end
|
|
|
|
data = validate_payload(source, "get-draft", data)
|
|
if not data then
|
|
return { success = false, error = "invalid_request" }
|
|
end
|
|
|
|
local id = data.id
|
|
if type(id) ~= "string" or #id ~= 36 then
|
|
return { success = false, error = "invalid_draft" }
|
|
end
|
|
|
|
local rows = Bridge.Database.Query([[
|
|
SELECT `id`, `recipients`, `subject`, `body`, `created_at`, `updated_at`
|
|
FROM `sky_phone_mail_drafts`
|
|
WHERE `id` = ? AND `account_id` = ?
|
|
LIMIT 1
|
|
]], { id, session.id })
|
|
if not rows[1] then
|
|
return { success = false, error = "draft_not_found" }
|
|
end
|
|
|
|
rows[1].recipients = json.decode(rows[1].recipients) or {}
|
|
return { success = true, data = rows[1] }
|
|
end)
|
|
|
|
Bridge.Callbacks.Register("sky_phone:mail:save-draft", function(source, data)
|
|
local session, error_response = require_session(source)
|
|
if not session then
|
|
return error_response
|
|
end
|
|
|
|
data = validate_payload(source, "save-draft", data)
|
|
if not data then
|
|
return { success = false, error = "invalid_request" }
|
|
end
|
|
|
|
local recipients = normalize_recipients(data.recipients, false)
|
|
local subject = data.subject
|
|
local body = data.body
|
|
if not recipients or not validate_text(subject, Config.Mail.SubjectMaxLength)
|
|
or not validate_text(body, Config.Mail.BodyMaxLength)
|
|
then
|
|
return { success = false, error = "invalid_draft" }
|
|
end
|
|
|
|
local id = data.id
|
|
if id ~= nil and (type(id) ~= "string" or #id ~= 36) then
|
|
return { success = false, error = "invalid_draft" }
|
|
end
|
|
|
|
if id then
|
|
local result = Bridge.Database.Query([[
|
|
UPDATE `sky_phone_mail_drafts`
|
|
SET `recipients` = ?, `subject` = ?, `body` = ?, `updated_at` = CURRENT_TIMESTAMP
|
|
WHERE `id` = ? AND `account_id` = ?
|
|
]], { json.encode(recipients), subject, body, id, session.id })
|
|
if not result or result == 0 or (type(result) == "table" and result.affectedRows == 0) then
|
|
return { success = false, error = "draft_not_found" }
|
|
end
|
|
else
|
|
id = new_database_id()
|
|
Bridge.Database.Query([[
|
|
INSERT INTO `sky_phone_mail_drafts` (`id`, `account_id`, `recipients`, `subject`, `body`)
|
|
VALUES (?, ?, ?, ?, ?)
|
|
]], { id, session.id, json.encode(recipients), subject, body })
|
|
end
|
|
|
|
broadcast_mailbox_changed(session.id)
|
|
return { success = true, data = { id = id } }
|
|
end)
|
|
|
|
Bridge.Callbacks.Register("sky_phone:mail:delete-draft", function(source, data)
|
|
local session, error_response = require_session(source)
|
|
if not session then
|
|
return error_response
|
|
end
|
|
|
|
data = validate_payload(source, "delete-draft", data)
|
|
if not data then
|
|
return { success = false, error = "invalid_request" }
|
|
end
|
|
|
|
local id = data.id
|
|
if type(id) ~= "string" or #id ~= 36 then
|
|
return { success = false, error = "invalid_draft" }
|
|
end
|
|
|
|
Bridge.Database.Query(
|
|
"DELETE FROM `sky_phone_mail_drafts` WHERE `id` = ? AND `account_id` = ?",
|
|
{ id, session.id }
|
|
)
|
|
broadcast_mailbox_changed(session.id)
|
|
return { success = true }
|
|
end)
|
|
|
|
Bridge.Callbacks.Register("sky_phone:mail:send", function(source, data)
|
|
local session, error_response = require_session(source)
|
|
if not session then
|
|
return error_response
|
|
end
|
|
|
|
data = validate_payload(source, "send", data)
|
|
if not data then
|
|
return { success = false, error = "invalid_request" }
|
|
end
|
|
|
|
local recipients = normalize_recipients(data.recipients, true)
|
|
local subject = trim(data.subject)
|
|
local body = data.body
|
|
if not recipients or #recipients == 0
|
|
or not validate_text(subject, Config.Mail.SubjectMaxLength)
|
|
or not validate_text(body, Config.Mail.BodyMaxLength)
|
|
or (subject == "" and trim(body) == "")
|
|
then
|
|
return { success = false, error = "invalid_message" }
|
|
end
|
|
|
|
local placeholders = {}
|
|
for index = 1, #recipients do
|
|
placeholders[index] = "?"
|
|
end
|
|
local recipient_accounts = Bridge.Database.Query(
|
|
("SELECT `id`, `email` FROM `sky_phone_accounts` WHERE `email` IN (%s)"):format(table.concat(placeholders, ", ")),
|
|
recipients
|
|
)
|
|
if #recipient_accounts ~= #recipients then
|
|
return { success = false, error = "recipient_not_found" }
|
|
end
|
|
|
|
local message_id = new_database_id()
|
|
local statements = {
|
|
{
|
|
query = [[
|
|
INSERT INTO `sky_phone_mail_messages`
|
|
(`id`, `sender_account_id`, `recipients`, `subject`, `body`)
|
|
VALUES (?, ?, ?, ?, ?)
|
|
]],
|
|
params = { message_id, session.id, json.encode(recipients), subject, body },
|
|
},
|
|
{
|
|
query = [[
|
|
INSERT INTO `sky_phone_mail_entries` (`message_id`, `account_id`, `folder`, `read_at`)
|
|
VALUES (?, ?, 'sent', CURRENT_TIMESTAMP)
|
|
]],
|
|
params = { message_id, session.id },
|
|
},
|
|
}
|
|
for _, account in ipairs(recipient_accounts) do
|
|
statements[#statements + 1] = {
|
|
query = [[
|
|
INSERT INTO `sky_phone_mail_entries` (`message_id`, `account_id`, `folder`)
|
|
VALUES (?, ?, 'inbox')
|
|
]],
|
|
params = { message_id, account.id },
|
|
}
|
|
end
|
|
if type(data.draftId) == "string" and #data.draftId == 36 then
|
|
statements[#statements + 1] = {
|
|
query = "DELETE FROM `sky_phone_mail_drafts` WHERE `id` = ? AND `account_id` = ?",
|
|
params = { data.draftId, session.id },
|
|
}
|
|
end
|
|
|
|
if not Bridge.Database.Transaction(statements) then
|
|
return { success = false, error = "request_failed" }
|
|
end
|
|
|
|
broadcast_mailbox_changed(session.id)
|
|
for _, account in ipairs(recipient_accounts) do
|
|
local counts = get_counts(account.id)
|
|
SkyPhone.NotifyAccountDevices(account.id, "sky_phone:mail:new", {
|
|
counts = counts,
|
|
sender = session.email,
|
|
subject = subject,
|
|
})
|
|
broadcast_mailbox_changed(account.id, counts)
|
|
end
|
|
|
|
return { success = true, data = { id = message_id } }
|
|
end)
|
|
|
|
Bridge.Callbacks.Register("sky_phone:mail:set-read", function(source, data)
|
|
local session, error_response = require_session(source)
|
|
if not session then
|
|
return error_response
|
|
end
|
|
|
|
data = validate_payload(source, "set-read", data)
|
|
if not data then
|
|
return { success = false, error = "invalid_request" }
|
|
end
|
|
|
|
local id = tonumber(data.id)
|
|
if not id or type(data.read) ~= "boolean" then
|
|
return { success = false, error = "invalid_message" }
|
|
end
|
|
|
|
Bridge.Database.Query(
|
|
("UPDATE `sky_phone_mail_entries` SET `read_at` = %s WHERE `id` = ? AND `account_id` = ?")
|
|
:format(data.read and "CURRENT_TIMESTAMP" or "NULL"),
|
|
{ id, session.id }
|
|
)
|
|
broadcast_mailbox_changed(session.id)
|
|
return { success = true }
|
|
end)
|
|
|
|
Bridge.Callbacks.Register("sky_phone:mail:move", function(source, data)
|
|
local session, error_response = require_session(source)
|
|
if not session then
|
|
return error_response
|
|
end
|
|
if not SkyPhone.AllowOperation(source, "mail_move", Config.Mail.MailboxRequestsPerMinute * 3, 60) then
|
|
return { success = false, error = "rate_limited" }
|
|
end
|
|
|
|
data = validate_payload(source, "move", data)
|
|
if not data then
|
|
return { success = false, error = "invalid_request" }
|
|
end
|
|
|
|
local entry_id = normalize_numeric_id(data.id)
|
|
local mailbox_id
|
|
if data.mailboxId ~= 0 then
|
|
mailbox_id = normalize_numeric_id(data.mailboxId)
|
|
end
|
|
if not entry_id then
|
|
return { success = false, error = "message_not_found" }
|
|
end
|
|
if data.mailboxId ~= 0 and (not mailbox_id or not find_owned_mailbox(session.id, mailbox_id)) then
|
|
return { success = false, error = "mailbox_not_found" }
|
|
end
|
|
|
|
local entries = Bridge.Database.Query([[
|
|
SELECT `id`
|
|
FROM `sky_phone_mail_entries`
|
|
WHERE `id` = ? AND `account_id` = ? AND `trashed_at` IS NULL
|
|
LIMIT 1
|
|
]], { entry_id, session.id })
|
|
if not entries[1] then
|
|
return { success = false, error = "message_not_found" }
|
|
end
|
|
|
|
if mailbox_id then
|
|
Bridge.Database.Query([[
|
|
UPDATE `sky_phone_mail_entries` SET `mailbox_id` = ?
|
|
WHERE `id` = ? AND `account_id` = ? AND `trashed_at` IS NULL
|
|
]], { mailbox_id, entry_id, session.id })
|
|
else
|
|
Bridge.Database.Query([[
|
|
UPDATE `sky_phone_mail_entries` SET `mailbox_id` = NULL
|
|
WHERE `id` = ? AND `account_id` = ? AND `trashed_at` IS NULL
|
|
]], { entry_id, session.id })
|
|
end
|
|
broadcast_mailbox_changed(session.id)
|
|
return { success = true }
|
|
end)
|
|
|
|
Bridge.Callbacks.Register("sky_phone:mail:trash", function(source, data)
|
|
local session, error_response = require_session(source)
|
|
if not session then
|
|
return error_response
|
|
end
|
|
|
|
data = validate_payload(source, "trash", data)
|
|
if not data then
|
|
return { success = false, error = "invalid_request" }
|
|
end
|
|
|
|
local id = tonumber(data.id)
|
|
if not id then
|
|
return { success = false, error = "invalid_message" }
|
|
end
|
|
|
|
Bridge.Database.Query([[
|
|
UPDATE `sky_phone_mail_entries` SET `trashed_at` = CURRENT_TIMESTAMP
|
|
WHERE `id` = ? AND `account_id` = ? AND `trashed_at` IS NULL
|
|
]], { id, session.id })
|
|
broadcast_mailbox_changed(session.id)
|
|
return { success = true }
|
|
end)
|
|
|
|
Bridge.Callbacks.Register("sky_phone:mail:restore", function(source, data)
|
|
local session, error_response = require_session(source)
|
|
if not session then
|
|
return error_response
|
|
end
|
|
|
|
data = validate_payload(source, "restore", data)
|
|
if not data then
|
|
return { success = false, error = "invalid_request" }
|
|
end
|
|
|
|
local id = tonumber(data.id)
|
|
if not id then
|
|
return { success = false, error = "invalid_message" }
|
|
end
|
|
|
|
Bridge.Database.Query([[
|
|
UPDATE `sky_phone_mail_entries` SET `trashed_at` = NULL
|
|
WHERE `id` = ? AND `account_id` = ? AND `trashed_at` IS NOT NULL
|
|
]], { id, session.id })
|
|
broadcast_mailbox_changed(session.id)
|
|
return { success = true }
|
|
end)
|
|
|
|
Bridge.Callbacks.Register("sky_phone:mail:delete-forever", function(source, data)
|
|
local session, error_response = require_session(source)
|
|
if not session then
|
|
return error_response
|
|
end
|
|
|
|
data = validate_payload(source, "delete-forever", data)
|
|
if not data then
|
|
return { success = false, error = "invalid_request" }
|
|
end
|
|
|
|
local id = tonumber(data.id)
|
|
if not id then
|
|
return { success = false, error = "invalid_message" }
|
|
end
|
|
|
|
Bridge.Database.Query([[
|
|
DELETE FROM `sky_phone_mail_entries`
|
|
WHERE `id` = ? AND `account_id` = ? AND `trashed_at` IS NOT NULL
|
|
]], { id, session.id })
|
|
Bridge.Database.Query([[
|
|
DELETE m FROM `sky_phone_mail_messages` m
|
|
LEFT JOIN `sky_phone_mail_entries` e ON e.`message_id` = m.`id`
|
|
WHERE e.`id` IS NULL
|
|
]], {})
|
|
broadcast_mailbox_changed(session.id)
|
|
return { success = true }
|
|
end)
|
|
|
|
Bridge.Callbacks.Register("sky_phone:mail:delete-many", function(source, data)
|
|
local session, error_response = require_session(source)
|
|
if not session then
|
|
return error_response
|
|
end
|
|
if not SkyPhone.AllowOperation(source, "mail_delete_many", Config.Mail.DeleteRequestsPerMinute, 60) then
|
|
return { success = false, error = "rate_limited" }
|
|
end
|
|
|
|
data = validate_payload(source, "delete-many", data)
|
|
if not data then
|
|
return { success = false, error = "invalid_request" }
|
|
end
|
|
|
|
local folder = data.folder
|
|
local custom_mailbox_id = mailbox_id_from_folder(folder)
|
|
if type(folder) ~= "string" or (not DELETE_FOLDERS[folder] and not custom_mailbox_id) then
|
|
return { success = false, error = "invalid_folder" }
|
|
end
|
|
if custom_mailbox_id and not find_owned_mailbox(session.id, custom_mailbox_id) then
|
|
return { success = false, error = "mailbox_not_found" }
|
|
end
|
|
|
|
local ids = normalize_delete_ids(folder, data.ids)
|
|
if not ids then
|
|
return { success = false, error = "invalid_request" }
|
|
end
|
|
|
|
local placeholders = make_placeholders(#ids)
|
|
if folder == "drafts" then
|
|
local parameters = { session.id }
|
|
append_values(parameters, ids)
|
|
Bridge.Database.Query(([[
|
|
DELETE FROM `sky_phone_mail_drafts`
|
|
WHERE `account_id` = ? AND `id` IN (%s)
|
|
]]):format(placeholders), parameters)
|
|
elseif folder == "inbox" or folder == "sent" then
|
|
local parameters = { session.id, folder }
|
|
append_values(parameters, ids)
|
|
Bridge.Database.Query(([[
|
|
UPDATE `sky_phone_mail_entries` SET `trashed_at` = CURRENT_TIMESTAMP
|
|
WHERE `account_id` = ? AND `folder` = ? AND `mailbox_id` IS NULL
|
|
AND `trashed_at` IS NULL
|
|
AND `id` IN (%s)
|
|
]]):format(placeholders), parameters)
|
|
elseif custom_mailbox_id then
|
|
local parameters = { session.id, custom_mailbox_id }
|
|
append_values(parameters, ids)
|
|
Bridge.Database.Query(([[
|
|
UPDATE `sky_phone_mail_entries` SET `trashed_at` = CURRENT_TIMESTAMP
|
|
WHERE `account_id` = ? AND `mailbox_id` = ? AND `trashed_at` IS NULL
|
|
AND `id` IN (%s)
|
|
]]):format(placeholders), parameters)
|
|
else
|
|
local orphan_parameters = { session.id }
|
|
append_values(orphan_parameters, ids)
|
|
orphan_parameters[#orphan_parameters + 1] = session.id
|
|
append_values(orphan_parameters, ids)
|
|
|
|
local delete_parameters = { session.id }
|
|
append_values(delete_parameters, ids)
|
|
|
|
local statements = {
|
|
{
|
|
query = ([[
|
|
DELETE m
|
|
FROM `sky_phone_mail_messages` m
|
|
JOIN `sky_phone_mail_entries` target ON target.`message_id` = m.`id`
|
|
WHERE target.`account_id` = ? AND target.`trashed_at` IS NOT NULL
|
|
AND target.`id` IN (%s)
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM `sky_phone_mail_entries` remaining
|
|
WHERE remaining.`message_id` = m.`id`
|
|
AND NOT (
|
|
remaining.`account_id` = ?
|
|
AND remaining.`trashed_at` IS NOT NULL
|
|
AND remaining.`id` IN (%s)
|
|
)
|
|
)
|
|
]]):format(placeholders, placeholders),
|
|
params = orphan_parameters,
|
|
},
|
|
{
|
|
query = ([[
|
|
DELETE FROM `sky_phone_mail_entries`
|
|
WHERE `account_id` = ? AND `trashed_at` IS NOT NULL
|
|
AND `id` IN (%s)
|
|
]]):format(placeholders),
|
|
params = delete_parameters,
|
|
},
|
|
}
|
|
if not Bridge.Database.Transaction(statements) then
|
|
return { success = false, error = "request_failed" }
|
|
end
|
|
end
|
|
|
|
broadcast_mailbox_changed(session.id)
|
|
return { success = true }
|
|
end)
|
|
|
|
Bridge.Callbacks.Register("sky_phone:mail:empty-trash", function(source)
|
|
local session, error_response = require_session(source)
|
|
if not session then
|
|
return error_response
|
|
end
|
|
|
|
Bridge.Database.Query(
|
|
"DELETE FROM `sky_phone_mail_entries` WHERE `account_id` = ? AND `trashed_at` IS NOT NULL",
|
|
{ session.id }
|
|
)
|
|
Bridge.Database.Query([[
|
|
DELETE m FROM `sky_phone_mail_messages` m
|
|
LEFT JOIN `sky_phone_mail_entries` e ON e.`message_id` = m.`id`
|
|
WHERE e.`id` IS NULL
|
|
]], {})
|
|
broadcast_mailbox_changed(session.id)
|
|
return { success = true }
|
|
end)
|
|
end)
|