mirror of
https://github.com/abhinavxd/libredesk.git
synced 2026-11-02 09:23:53 +00:00
Abhinav Raut
703f38fafc
Quoted history now leaves out CSAT emails and anything the contact was not part of. Earlier messages load only when the outgoing template or the reply uses PreviousMessages.
1377 lines
47 KiB
SQL
1377 lines
47 KiB
SQL
-- name: unsnooze-all
|
|
UPDATE conversations
|
|
SET snoozed_until = NULL, status_id = (SELECT id FROM conversation_statuses WHERE name = 'Open')
|
|
WHERE snoozed_until <= NOW()
|
|
AND status_id = (SELECT id FROM conversation_statuses WHERE name = 'Snoozed');
|
|
|
|
-- name: insert-conversation
|
|
-- $11 = rate limit window start (timestamptz), $12 = max conversations (0 = unlimited)
|
|
-- $13 = subject reference marker template (placeholder: {ref})
|
|
WITH
|
|
status_id AS (
|
|
SELECT id FROM conversation_statuses WHERE name = $2
|
|
),
|
|
reference_number AS (
|
|
SELECT generate_reference_number($7) AS reference_number
|
|
)
|
|
INSERT INTO conversations
|
|
(contact_id, status_id, inbox_id, last_message, last_message_at, subject, reference_number, meta, custom_attributes)
|
|
SELECT
|
|
$1,
|
|
(SELECT id FROM status_id),
|
|
$3,
|
|
$4,
|
|
$5,
|
|
CASE
|
|
WHEN $8 = TRUE THEN CONCAT($6::text, ' - ', REPLACE($13::text, '{ref}', (SELECT reference_number FROM reference_number)))
|
|
ELSE $6::text
|
|
END,
|
|
(SELECT reference_number FROM reference_number),
|
|
$9,
|
|
$10
|
|
WHERE $12::int = 0 OR (SELECT COUNT(*) FROM conversations WHERE contact_id = $1 AND created_at >= $11) < $12::int
|
|
RETURNING id, uuid;
|
|
|
|
-- name: get-conversations
|
|
-- $1 = viewing user ID for per-agent unread count
|
|
-- $2 = include mentioned message UUID (true for mentioned inbox, false otherwise)
|
|
SELECT
|
|
COUNT(*) OVER() as total,
|
|
conversations.id,
|
|
conversations.created_at,
|
|
conversations.updated_at,
|
|
conversations.uuid,
|
|
conversations.reference_number,
|
|
conversations.waiting_since,
|
|
users.created_at as "contact.created_at",
|
|
users.updated_at as "contact.updated_at",
|
|
users.first_name as "contact.first_name",
|
|
users.last_name as "contact.last_name",
|
|
users.email as "contact.email",
|
|
users.avatar_url as "contact.avatar_url",
|
|
inboxes.channel as inbox_channel,
|
|
inboxes.name as inbox_name,
|
|
conversations.sla_policy_id,
|
|
conversations.first_reply_at,
|
|
conversations.last_reply_at,
|
|
conversations.resolved_at,
|
|
conversations.last_resolved_at,
|
|
conversations.subject,
|
|
conversations.last_message,
|
|
conversations.last_message_at,
|
|
conversations.last_message_sender,
|
|
conversations.last_interaction,
|
|
conversations.last_interaction_at,
|
|
conversations.last_interaction_sender,
|
|
conversations.next_sla_deadline_at,
|
|
conversations.priority_id,
|
|
conversations.assigned_user_id,
|
|
conversations.assigned_team_id,
|
|
CASE WHEN assignee.id IS NULL THEN 'null'::json ELSE json_build_object(
|
|
'id', assignee.id,
|
|
'first_name', assignee.first_name,
|
|
'last_name', assignee.last_name,
|
|
'avatar_url', assignee.avatar_url,
|
|
'type', assignee.type,
|
|
'availability_status', assignee.availability_status
|
|
) END AS assignee,
|
|
(
|
|
SELECT CASE WHEN COUNT(*) > 9 THEN 10 ELSE COUNT(*) END
|
|
FROM (
|
|
SELECT 1 FROM conversation_messages
|
|
WHERE conversation_id = conversations.id
|
|
AND created_at > COALESCE(
|
|
(SELECT last_seen_at FROM conversation_last_seen
|
|
WHERE conversation_id = conversations.id AND user_id = $1),
|
|
'1970-01-01'::TIMESTAMPTZ
|
|
)
|
|
AND (meta IS NULL OR NOT COALESCE((meta->>'continuity_email')::boolean, false))
|
|
LIMIT 10
|
|
) t
|
|
) as unread_message_count,
|
|
conversation_statuses.name as status,
|
|
conversation_priorities.name as priority,
|
|
as_latest.first_response_deadline_at,
|
|
as_latest.resolution_deadline_at,
|
|
as_latest.id as applied_sla_id,
|
|
nxt_resp_event.deadline_at AS next_response_deadline_at,
|
|
nxt_resp_event.met_at as next_response_met_at,
|
|
CASE WHEN $2 = true THEN (
|
|
SELECT msg.uuid
|
|
FROM conversation_mentions cm2
|
|
JOIN conversation_messages msg ON msg.id = cm2.message_id
|
|
WHERE cm2.conversation_id = conversations.id
|
|
AND (cm2.mentioned_user_id = $1 OR EXISTS(
|
|
SELECT 1 FROM team_members tm2
|
|
WHERE tm2.team_id = cm2.mentioned_team_id AND tm2.user_id = $1
|
|
))
|
|
ORDER BY cm2.created_at DESC
|
|
LIMIT 1
|
|
) ELSE NULL END as mentioned_message_uuid
|
|
FROM conversations
|
|
JOIN users ON contact_id = users.id
|
|
LEFT JOIN users assignee ON conversations.assigned_user_id = assignee.id
|
|
AND assignee.deleted_at IS NULL AND (assignee.email != 'System' OR assignee.type = 'ai_assistant')
|
|
JOIN inboxes ON inbox_id = inboxes.id
|
|
LEFT JOIN conversation_statuses ON status_id = conversation_statuses.id
|
|
LEFT JOIN conversation_priorities ON priority_id = conversation_priorities.id
|
|
LEFT JOIN LATERAL (
|
|
SELECT id, first_response_deadline_at, resolution_deadline_at
|
|
FROM applied_slas
|
|
WHERE conversation_id = conversations.id
|
|
ORDER BY created_at DESC LIMIT 1
|
|
) as_latest ON true
|
|
LEFT JOIN LATERAL (
|
|
SELECT se.deadline_at, se.met_at
|
|
FROM sla_events se
|
|
WHERE se.applied_sla_id = as_latest.id
|
|
AND se.type = 'next_response'
|
|
ORDER BY se.created_at DESC
|
|
LIMIT 1
|
|
) nxt_resp_event ON true
|
|
WHERE 1=1 %s
|
|
|
|
-- name: get-conversation-list-item
|
|
SELECT
|
|
conversations.id,
|
|
conversations.created_at,
|
|
conversations.updated_at,
|
|
conversations.uuid,
|
|
conversations.reference_number,
|
|
conversations.waiting_since,
|
|
users.created_at as "contact.created_at",
|
|
users.updated_at as "contact.updated_at",
|
|
users.first_name as "contact.first_name",
|
|
users.last_name as "contact.last_name",
|
|
users.email as "contact.email",
|
|
users.avatar_url as "contact.avatar_url",
|
|
inboxes.channel as inbox_channel,
|
|
inboxes.name as inbox_name,
|
|
conversations.sla_policy_id,
|
|
conversations.first_reply_at,
|
|
conversations.last_reply_at,
|
|
conversations.resolved_at,
|
|
conversations.subject,
|
|
conversations.last_message,
|
|
conversations.last_message_at,
|
|
conversations.last_message_sender,
|
|
conversations.last_interaction,
|
|
conversations.last_interaction_at,
|
|
conversations.last_interaction_sender,
|
|
conversations.next_sla_deadline_at,
|
|
conversations.priority_id,
|
|
conversations.assigned_user_id,
|
|
conversations.assigned_team_id,
|
|
CASE WHEN assignee.id IS NULL THEN 'null'::json ELSE json_build_object(
|
|
'id', assignee.id,
|
|
'first_name', assignee.first_name,
|
|
'last_name', assignee.last_name,
|
|
'avatar_url', assignee.avatar_url,
|
|
'type', assignee.type,
|
|
'availability_status', assignee.availability_status
|
|
) END AS assignee,
|
|
conversation_statuses.name as status,
|
|
conversation_priorities.name as priority,
|
|
as_latest.first_response_deadline_at,
|
|
as_latest.resolution_deadline_at,
|
|
as_latest.id as applied_sla_id,
|
|
nxt_resp_event.deadline_at AS next_response_deadline_at,
|
|
nxt_resp_event.met_at as next_response_met_at
|
|
FROM conversations
|
|
JOIN users ON contact_id = users.id
|
|
LEFT JOIN users assignee ON conversations.assigned_user_id = assignee.id
|
|
AND assignee.deleted_at IS NULL AND (assignee.email != 'System' OR assignee.type = 'ai_assistant')
|
|
JOIN inboxes ON inbox_id = inboxes.id
|
|
LEFT JOIN conversation_statuses ON status_id = conversation_statuses.id
|
|
LEFT JOIN conversation_priorities ON priority_id = conversation_priorities.id
|
|
LEFT JOIN LATERAL (
|
|
SELECT id, first_response_deadline_at, resolution_deadline_at
|
|
FROM applied_slas
|
|
WHERE conversation_id = conversations.id
|
|
ORDER BY created_at DESC LIMIT 1
|
|
) as_latest ON true
|
|
LEFT JOIN LATERAL (
|
|
SELECT se.deadline_at, se.met_at
|
|
FROM sla_events se
|
|
WHERE se.applied_sla_id = as_latest.id
|
|
AND se.type = 'next_response'
|
|
ORDER BY se.created_at DESC
|
|
LIMIT 1
|
|
) nxt_resp_event ON true
|
|
WHERE conversations.uuid = $1::uuid;
|
|
|
|
-- name: get-conversation
|
|
SELECT
|
|
c.id,
|
|
c.created_at,
|
|
c.updated_at,
|
|
c.closed_at,
|
|
c.resolved_at,
|
|
c.last_resolved_at,
|
|
c.contact_last_seen_at,
|
|
c.inbox_id,
|
|
inb.name as inbox_name,
|
|
COALESCE(inb.from, '') as inbox_mail,
|
|
COALESCE(inb.config->>'reply_to', '') as inbox_reply_to,
|
|
COALESCE((SELECT jsonb_agg(jsonb_build_object('email', email, 'verification_status', verification_status, 'verified_at', verified_at) ORDER BY position, id) FROM inbox_email_addresses WHERE inbox_id = inb.id AND kind = 'alias'), '[]'::jsonb) AS inbox_aliases,
|
|
COALESCE(inb.channel::TEXT, '') as inbox_channel,
|
|
c.status_id,
|
|
c.priority_id,
|
|
p.name as priority,
|
|
s.name as status,
|
|
s.category as status_category,
|
|
c.uuid,
|
|
c.reference_number,
|
|
c.first_reply_at,
|
|
c.last_reply_at,
|
|
c.waiting_since,
|
|
c.snoozed_until,
|
|
c.assigned_user_id,
|
|
c.assigned_team_id,
|
|
CASE WHEN assignee.id IS NULL THEN 'null'::json ELSE json_build_object(
|
|
'id', assignee.id,
|
|
'first_name', assignee.first_name,
|
|
'last_name', assignee.last_name,
|
|
'avatar_url', assignee.avatar_url,
|
|
'type', assignee.type,
|
|
'availability_status', assignee.availability_status
|
|
) END AS assignee,
|
|
c.subject,
|
|
c.contact_id,
|
|
c.sla_policy_id,
|
|
c.next_sla_deadline_at,
|
|
c.meta,
|
|
sla.name as sla_policy_name,
|
|
c.last_message_at,
|
|
c.last_message_sender,
|
|
c.last_message,
|
|
c.last_interaction,
|
|
c.last_interaction_at,
|
|
c.last_interaction_sender,
|
|
c.custom_attributes,
|
|
(SELECT COALESCE(
|
|
(SELECT json_agg(t.name)
|
|
FROM tags t
|
|
INNER JOIN conversation_tags ct ON ct.tag_id = t.id
|
|
WHERE ct.conversation_id = c.id),
|
|
'[]'::json
|
|
)) AS tags,
|
|
ct.id as "contact.id",
|
|
ct.created_at as "contact.created_at",
|
|
ct.updated_at as "contact.updated_at",
|
|
ct.first_name as "contact.first_name",
|
|
ct.last_name as "contact.last_name",
|
|
ct.email as "contact.email",
|
|
ct.type as "contact.type",
|
|
ct.availability_status as "contact.availability_status",
|
|
ct.avatar_url as "contact.avatar_url",
|
|
ct.phone_number as "contact.phone_number",
|
|
ct.phone_number_country_code as "contact.phone_number_country_code",
|
|
ct.country as "contact.country",
|
|
ct.custom_attributes as "contact.custom_attributes",
|
|
ct.enabled as "contact.enabled",
|
|
ct.last_active_at as "contact.last_active_at",
|
|
ct.last_login_at as "contact.last_login_at",
|
|
ct.external_user_id as "contact.external_user_id",
|
|
(SELECT json_agg(json_build_object('channel', cci.channel, 'identifier', cci.identifier))
|
|
FROM contact_channel_identities cci WHERE cci.contact_id = ct.id) as "contact.channel_identities",
|
|
as_latest.first_response_deadline_at,
|
|
as_latest.resolution_deadline_at,
|
|
as_latest.id as applied_sla_id,
|
|
nxt_resp_event.deadline_at AS next_response_deadline_at,
|
|
nxt_resp_event.met_at as next_response_met_at,
|
|
c.last_continuity_email_sent_at,
|
|
c.last_inbound_at,
|
|
(SELECT MAX(c2.last_inbound_at) FROM conversations c2 WHERE c2.contact_id = c.contact_id AND c2.inbox_id = c.inbox_id) AS contact_last_inbound_at,
|
|
csat.rating as csat_rating,
|
|
csat.feedback as csat_feedback,
|
|
csat.response_timestamp as csat_responded_at
|
|
FROM conversations c
|
|
JOIN users ct ON c.contact_id = ct.id
|
|
LEFT JOIN users assignee ON c.assigned_user_id = assignee.id
|
|
AND assignee.deleted_at IS NULL AND (assignee.email != 'System' OR assignee.type = 'ai_assistant')
|
|
JOIN inboxes inb ON c.inbox_id = inb.id
|
|
LEFT JOIN LATERAL (
|
|
SELECT rating, feedback, response_timestamp
|
|
FROM csat_responses
|
|
WHERE conversation_id = c.id
|
|
ORDER BY response_timestamp DESC NULLS LAST, created_at DESC
|
|
LIMIT 1
|
|
) csat ON true
|
|
LEFT JOIN sla_policies sla ON c.sla_policy_id = sla.id
|
|
LEFT JOIN teams at ON at.id = c.assigned_team_id
|
|
LEFT JOIN conversation_statuses s ON c.status_id = s.id
|
|
LEFT JOIN conversation_priorities p ON c.priority_id = p.id
|
|
LEFT JOIN LATERAL (
|
|
SELECT id, first_response_deadline_at, resolution_deadline_at
|
|
FROM applied_slas
|
|
WHERE conversation_id = c.id
|
|
ORDER BY created_at DESC LIMIT 1
|
|
) as_latest ON true
|
|
LEFT JOIN LATERAL (
|
|
SELECT se.deadline_at, se.met_at
|
|
FROM sla_events se
|
|
WHERE se.applied_sla_id = as_latest.id
|
|
AND se.type = 'next_response'
|
|
ORDER BY se.created_at DESC
|
|
LIMIT 1
|
|
) nxt_resp_event ON true
|
|
WHERE
|
|
($1 > 0 AND c.id = $1)
|
|
OR
|
|
(NULLIF($2, '')::uuid IS NOT NULL AND c.uuid = NULLIF($2, '')::uuid)
|
|
OR
|
|
($3::TEXT != '' AND c.reference_number = $3::TEXT)
|
|
|
|
|
|
-- name: get-conversations-created-after
|
|
SELECT
|
|
c.id,
|
|
c.uuid
|
|
FROM conversations c
|
|
WHERE c.created_at > $1 AND c.id > $2
|
|
ORDER BY c.id
|
|
LIMIT $3;
|
|
|
|
-- name: get-contact-previous-conversations
|
|
SELECT
|
|
c.id,
|
|
c.created_at,
|
|
c.updated_at,
|
|
c.uuid,
|
|
c.subject,
|
|
u.first_name AS "contact.first_name",
|
|
u.last_name AS "contact.last_name",
|
|
u.avatar_url AS "contact.avatar_url",
|
|
c.last_message as last_message,
|
|
c.last_message_at as last_message_at
|
|
FROM users u
|
|
JOIN conversations c ON c.contact_id = u.id
|
|
WHERE c.contact_id = $1
|
|
AND $5
|
|
AND (
|
|
$6
|
|
OR ($7 AND c.assigned_user_id = $3)
|
|
OR ($8 AND c.assigned_team_id = ANY($4::int[]))
|
|
OR ($9 AND c.assigned_team_id = ANY($4::int[]) AND c.assigned_user_id IS NULL)
|
|
OR ($10 AND c.assigned_user_id IS NULL AND c.assigned_team_id IS NULL)
|
|
)
|
|
ORDER BY c.created_at DESC
|
|
LIMIT $2;
|
|
|
|
-- name: get-contact-conversations-for-ai
|
|
SELECT
|
|
c.id,
|
|
c.reference_number,
|
|
c.subject,
|
|
cs.name AS status,
|
|
c.created_at,
|
|
c.last_message_at,
|
|
c.assigned_user_id,
|
|
c.assigned_team_id
|
|
FROM conversations c
|
|
LEFT JOIN conversation_statuses cs ON c.status_id = cs.id
|
|
WHERE c.contact_id = $1
|
|
AND c.id != $2
|
|
ORDER BY c.created_at DESC
|
|
LIMIT 50;
|
|
|
|
-- name: get-conversations-by-contact-email-for-ai
|
|
SELECT
|
|
c.id,
|
|
c.reference_number,
|
|
c.subject,
|
|
cs.name AS status,
|
|
c.created_at,
|
|
c.last_message_at,
|
|
c.assigned_user_id,
|
|
c.assigned_team_id,
|
|
TRIM(CONCAT(u.first_name, ' ', COALESCE(u.last_name, ''))) AS contact_name
|
|
FROM conversations c
|
|
JOIN users u ON c.contact_id = u.id
|
|
LEFT JOIN conversation_statuses cs ON c.status_id = cs.id
|
|
WHERE LOWER(u.email) = LOWER($1)
|
|
AND u.type = 'contact'
|
|
AND u.deleted_at IS NULL
|
|
ORDER BY c.created_at DESC
|
|
LIMIT 50;
|
|
|
|
-- name: get-chat-conversation
|
|
SELECT
|
|
COALESCE((SELECT json_agg(json_build_object('name', media.filename, 'content_type', media.content_type, 'uuid', media.uuid, 'size', media.size, 'content_id', media.content_id, 'disposition', media.disposition))
|
|
FROM media WHERE media.model_type = 'messages' AND media.model_id = (
|
|
SELECT m.id FROM conversation_messages m
|
|
WHERE m.conversation_id = c.id AND m.private = FALSE AND m.type IN ('incoming', 'outgoing')
|
|
ORDER BY m.created_at DESC, m.id DESC LIMIT 1
|
|
)), '[]'::json) AS "last_message.attachments",
|
|
COALESCE(c.contact_last_seen_at, c.created_at) AS contact_last_seen_at,
|
|
c.created_at,
|
|
c.uuid,
|
|
cs.name as status,
|
|
COALESCE(c.last_interaction, '') as "last_message.content",
|
|
c.last_interaction_at as "last_message.created_at",
|
|
COALESCE(lis.id, 0) AS "last_message.author.id",
|
|
COALESCE(lis.first_name, '') AS "last_message.author.first_name",
|
|
COALESCE(lis.last_name, '') AS "last_message.author.last_name",
|
|
COALESCE(lis.avatar_url, '') AS "last_message.author.avatar_url",
|
|
COALESCE(c.last_interaction_sender::TEXT, '') AS "last_message.author.type",
|
|
(SELECT CASE WHEN COUNT(*) > 9 THEN 10 ELSE COUNT(*) END
|
|
FROM (
|
|
SELECT 1 FROM conversation_messages unread
|
|
WHERE unread.conversation_id = c.id
|
|
AND unread.created_at > COALESCE(c.contact_last_seen_at, c.created_at)
|
|
AND unread.type = 'outgoing'
|
|
AND unread.private = false
|
|
LIMIT 10
|
|
) t) AS unread_message_count,
|
|
COALESCE(au.availability_status::TEXT, '') as "assignee.availability_status",
|
|
au.avatar_url as "assignee.avatar_url",
|
|
COALESCE(au.first_name, '') as "assignee.first_name",
|
|
COALESCE(au.id, 0) as "assignee.id",
|
|
COALESCE(au.last_name, '') as "assignee.last_name",
|
|
COALESCE(au.type::TEXT, '') as "assignee.type",
|
|
COALESCE(aa.expectation, '') as "assignee.expectation"
|
|
FROM conversations c
|
|
INNER JOIN inboxes inb on c.inbox_id = inb.id
|
|
LEFT JOIN conversation_statuses cs ON c.status_id = cs.id
|
|
LEFT JOIN users au ON c.assigned_user_id = au.id
|
|
LEFT JOIN ai_assistants aa ON aa.user_id = au.id
|
|
LEFT JOIN users lis ON c.last_interaction_sender_id = lis.id
|
|
WHERE c.uuid = $1
|
|
AND inb.deleted_at IS NULL;
|
|
|
|
-- name: get-contact-unread-preview-messages
|
|
SELECT
|
|
m.id,
|
|
m.created_at,
|
|
m.status,
|
|
m.type,
|
|
m.content,
|
|
m.text_content,
|
|
m.content_type,
|
|
m.conversation_id,
|
|
m.uuid,
|
|
m.private,
|
|
m.sender_id,
|
|
m.sender_type,
|
|
m.meta,
|
|
c.uuid AS conversation_uuid,
|
|
u.id AS "author.id",
|
|
u.first_name AS "author.first_name",
|
|
u.last_name AS "author.last_name",
|
|
u.email AS "author.email",
|
|
u.avatar_url AS "author.avatar_url",
|
|
u.availability_status AS "author.availability_status",
|
|
u.type AS "author.type",
|
|
u.last_active_at AS "author.last_active_at",
|
|
COALESCE(
|
|
(SELECT json_agg(
|
|
json_build_object(
|
|
'name', filename,
|
|
'content_type', content_type,
|
|
'uuid', uuid,
|
|
'size', size,
|
|
'content_id', content_id,
|
|
'disposition', disposition
|
|
) ORDER BY filename
|
|
) FROM media
|
|
WHERE model_type = 'messages' AND model_id = m.id),
|
|
'[]'::json) AS attachments
|
|
FROM conversation_messages m
|
|
JOIN conversations c ON c.id = m.conversation_id
|
|
JOIN inboxes inb ON inb.id = c.inbox_id
|
|
JOIN users u ON u.id = m.sender_id
|
|
WHERE c.contact_id = $1
|
|
AND c.inbox_id = $2
|
|
AND inb.deleted_at IS NULL
|
|
AND m.created_at > COALESCE(c.contact_last_seen_at, c.created_at)
|
|
AND m.type = 'outgoing'
|
|
AND m.private = false
|
|
AND u.type IN ('agent', 'ai_assistant')
|
|
AND (m.meta IS NULL OR NOT COALESCE((m.meta->>'continuity_email')::boolean, false))
|
|
ORDER BY m.created_at DESC, m.id DESC
|
|
LIMIT $3;
|
|
|
|
-- name: get-contact-chat-conversations
|
|
SELECT
|
|
COALESCE((SELECT json_agg(json_build_object('name', media.filename, 'content_type', media.content_type, 'uuid', media.uuid, 'size', media.size, 'content_id', media.content_id, 'disposition', media.disposition))
|
|
FROM media WHERE media.model_type = 'messages' AND media.model_id = (
|
|
SELECT m.id FROM conversation_messages m
|
|
WHERE m.conversation_id = c.id AND m.private = FALSE AND m.type IN ('incoming', 'outgoing')
|
|
ORDER BY m.created_at DESC, m.id DESC LIMIT 1
|
|
)), '[]'::json) AS "last_message.attachments",
|
|
COALESCE(c.contact_last_seen_at, c.created_at) AS contact_last_seen_at,
|
|
c.created_at,
|
|
c.uuid,
|
|
cs.name as status,
|
|
COALESCE(c.last_interaction, '') as "last_message.content",
|
|
c.last_interaction_at as "last_message.created_at",
|
|
COALESCE(lis.id, 0) AS "last_message.author.id",
|
|
COALESCE(lis.first_name, '') AS "last_message.author.first_name",
|
|
COALESCE(lis.last_name, '') AS "last_message.author.last_name",
|
|
COALESCE(lis.avatar_url, '') AS "last_message.author.avatar_url",
|
|
COALESCE(c.last_interaction_sender::TEXT, '') AS "last_message.author.type",
|
|
(SELECT CASE WHEN COUNT(*) > 9 THEN 10 ELSE COUNT(*) END
|
|
FROM (
|
|
SELECT 1 FROM conversation_messages unread
|
|
WHERE unread.conversation_id = c.id
|
|
AND unread.created_at > COALESCE(c.contact_last_seen_at, c.created_at)
|
|
AND unread.type = 'outgoing'
|
|
AND unread.private = false
|
|
LIMIT 10
|
|
) t) AS unread_message_count,
|
|
COALESCE(au.availability_status::TEXT, '') as "assignee.availability_status",
|
|
au.avatar_url as "assignee.avatar_url",
|
|
COALESCE(au.first_name, '') as "assignee.first_name",
|
|
COALESCE(au.id, 0) as "assignee.id",
|
|
COALESCE(au.last_name, '') as "assignee.last_name",
|
|
COALESCE(au.type::TEXT, '') as "assignee.type"
|
|
FROM conversations c
|
|
INNER JOIN inboxes inb ON c.inbox_id = inb.id
|
|
INNER JOIN users con ON c.contact_id = con.id
|
|
LEFT JOIN conversation_statuses cs ON c.status_id = cs.id
|
|
LEFT JOIN users au ON c.assigned_user_id = au.id
|
|
LEFT JOIN users lis ON c.last_interaction_sender_id = lis.id
|
|
WHERE c.contact_id = $1 AND c.inbox_id = $2
|
|
AND inb.deleted_at IS NULL
|
|
AND con.deleted_at IS NULL
|
|
ORDER BY c.created_at DESC
|
|
LIMIT 200;
|
|
|
|
-- name: get-conversation-uuid
|
|
SELECT uuid from conversations where id = $1;
|
|
|
|
-- name: get-conversation-inbox-contact
|
|
SELECT inbox_id, contact_id FROM conversations WHERE uuid = $1;
|
|
|
|
-- name: update-conversation-assigned-user
|
|
UPDATE conversations
|
|
SET assigned_user_id = $2,
|
|
updated_at = NOW()
|
|
WHERE uuid = $1;
|
|
|
|
-- name: claim-unassigned-conversation
|
|
UPDATE conversations
|
|
SET assigned_user_id = $2,
|
|
updated_at = NOW()
|
|
WHERE uuid = $1 AND assigned_user_id IS NULL AND assigned_team_id = $3;
|
|
|
|
-- name: update-conversation-contact-last-seen
|
|
UPDATE conversations
|
|
SET contact_last_seen_at = NOW(),
|
|
updated_at = NOW()
|
|
WHERE uuid = $1
|
|
RETURNING contact_last_seen_at;
|
|
|
|
-- name: update-conversation-assigned-team
|
|
UPDATE conversations
|
|
SET assigned_team_id = $2,
|
|
updated_at = NOW()
|
|
WHERE uuid = $1;
|
|
|
|
|
|
-- name: update-conversation-status
|
|
WITH new_status AS (
|
|
SELECT id, category FROM conversation_statuses WHERE name = $2
|
|
)
|
|
UPDATE conversations
|
|
SET status_id = (SELECT id FROM new_status),
|
|
resolved_at = COALESCE(resolved_at, CASE WHEN (SELECT category FROM new_status) = 'resolved' THEN NOW() END),
|
|
last_resolved_at = CASE WHEN (SELECT category FROM new_status) = 'resolved' THEN NOW() ELSE last_resolved_at END,
|
|
closed_at = COALESCE(closed_at, CASE WHEN $2 = 'Closed' THEN NOW() END),
|
|
snoozed_until = CASE WHEN $2 = 'Snoozed' THEN $3::timestamptz ELSE NULL END,
|
|
updated_at = NOW()
|
|
WHERE uuid = $1;
|
|
|
|
-- name: get-user-active-conversations-count
|
|
SELECT COUNT(*) FROM conversations WHERE status_id IN (SELECT id FROM conversation_statuses WHERE category = 'open') AND assigned_user_id = $1;
|
|
|
|
-- name: get-sidebar-standard-counts
|
|
SELECT
|
|
COUNT(*) FILTER (WHERE conversations.assigned_user_id = $1) AS assigned,
|
|
COUNT(*) FILTER (WHERE conversations.assigned_user_id IS NULL AND conversations.assigned_team_id IS NULL) AS unassigned,
|
|
COUNT(*) FILTER (WHERE EXISTS (
|
|
SELECT 1 FROM conversation_mentions cm
|
|
WHERE cm.conversation_id = conversations.id
|
|
AND (cm.mentioned_user_id = $1 OR EXISTS (
|
|
SELECT 1 FROM team_members tm
|
|
WHERE tm.team_id = cm.mentioned_team_id AND tm.user_id = $1
|
|
))
|
|
)) AS mentioned,
|
|
COUNT(*) AS "all"
|
|
FROM conversations
|
|
WHERE conversations.status_id IN (SELECT id FROM conversation_statuses WHERE category = 'open');
|
|
|
|
-- name: get-conversations-count-base
|
|
-- The list-type WHERE clause is appended at %s; view filters are added by BuildFilterQuery.
|
|
SELECT 1
|
|
FROM conversations
|
|
JOIN users ON contact_id = users.id
|
|
JOIN inboxes ON inbox_id = inboxes.id
|
|
LEFT JOIN conversation_statuses ON status_id = conversation_statuses.id
|
|
WHERE TRUE
|
|
%s
|
|
|
|
-- name: update-conversation-priority
|
|
UPDATE conversations
|
|
SET priority_id = (SELECT id FROM conversation_priorities WHERE name = $2),
|
|
updated_at = NOW()
|
|
WHERE uuid = $1;
|
|
|
|
-- name: upsert-user-last-seen
|
|
INSERT INTO conversation_last_seen (user_id, conversation_id, last_seen_at, updated_at)
|
|
SELECT $1, c.id, COALESCE(m.created_at, NOW()), clock_timestamp()
|
|
FROM conversations c
|
|
LEFT JOIN conversation_messages m ON m.conversation_id = c.id AND m.uuid = NULLIF($3::text, '')::uuid
|
|
WHERE c.uuid = $2 AND ($3::text = '' OR m.id IS NOT NULL)
|
|
ON CONFLICT (conversation_id, user_id)
|
|
DO UPDATE SET
|
|
last_seen_at = GREATEST(conversation_last_seen.last_seen_at, EXCLUDED.last_seen_at),
|
|
updated_at = GREATEST(conversation_last_seen.updated_at + INTERVAL '1 microsecond', clock_timestamp())
|
|
RETURNING last_seen_at, (EXTRACT(EPOCH FROM updated_at) * 1000000)::bigint AS read_version;
|
|
|
|
-- name: update-conversation-last-message
|
|
-- $1=id, $2=uuid, $3=content, $4=sender_type, $5=timestamp, $6=message_type, $7=private, $8=sender_id
|
|
UPDATE conversations SET
|
|
last_message = $3,
|
|
last_message_sender = $4,
|
|
last_message_sender_id = $8,
|
|
last_message_at = $5,
|
|
last_interaction = CASE WHEN $6 != 'activity' AND $7 = false THEN $3 ELSE last_interaction END,
|
|
last_interaction_sender = CASE WHEN $6 != 'activity' AND $7 = false THEN $4 ELSE last_interaction_sender END,
|
|
last_interaction_sender_id = CASE WHEN $6 != 'activity' AND $7 = false THEN $8 ELSE last_interaction_sender_id END,
|
|
last_interaction_at = CASE WHEN $6 != 'activity' AND $7 = false THEN $5 ELSE last_interaction_at END,
|
|
updated_at = NOW()
|
|
WHERE CASE
|
|
WHEN $1 > 0 THEN id = $1
|
|
ELSE uuid = $2
|
|
END
|
|
|
|
-- name: get-conversation-participants
|
|
SELECT users.id as id, first_name, last_name, avatar_url
|
|
FROM conversation_participants
|
|
INNER JOIN users ON users.id = conversation_participants.user_id
|
|
WHERE conversation_id =
|
|
(
|
|
SELECT id FROM conversations WHERE uuid = $1
|
|
);
|
|
|
|
-- name: get-conversation-participant-agents
|
|
SELECT users.id, users.first_name, users.last_name, users.email
|
|
FROM conversation_participants
|
|
INNER JOIN users ON users.id = conversation_participants.user_id
|
|
WHERE conversation_participants.conversation_id = (SELECT id FROM conversations WHERE uuid = $1)
|
|
AND users.type = 'agent'
|
|
AND EXISTS (
|
|
SELECT 1 FROM conversation_messages m
|
|
WHERE m.conversation_id = conversation_participants.conversation_id
|
|
AND m.sender_id = users.id
|
|
AND m.sender_type = 'agent'
|
|
AND m.type = 'outgoing'
|
|
AND NOT m.private
|
|
)
|
|
AND users.email != 'System'
|
|
AND users.enabled
|
|
AND users.deleted_at IS NULL;
|
|
|
|
-- name: insert-conversation-participant
|
|
INSERT INTO conversation_participants
|
|
(user_id, conversation_id)
|
|
VALUES($1, (SELECT id FROM conversations WHERE uuid = $2))
|
|
ON CONFLICT (conversation_id, user_id) DO NOTHING;
|
|
|
|
-- name: get-unassigned-conversations
|
|
SELECT
|
|
c.created_at,
|
|
c.updated_at,
|
|
c.uuid,
|
|
c.assigned_team_id,
|
|
inb.channel as inbox_channel,
|
|
inb.name as inbox_name
|
|
FROM conversations c
|
|
JOIN inboxes inb ON c.inbox_id = inb.id
|
|
WHERE assigned_user_id IS NULL AND assigned_team_id IS NOT NULL
|
|
ORDER BY c.created_at ASC;
|
|
|
|
-- name: add-conversation-tags
|
|
-- Insert new tags
|
|
INSERT INTO conversation_tags (conversation_id, tag_id)
|
|
SELECT c.id, t.id
|
|
FROM conversations c, tags t
|
|
WHERE t.name = ANY($2::text[]) AND c.uuid = $1
|
|
ON CONFLICT (conversation_id, tag_id) DO UPDATE SET tag_id = EXCLUDED.tag_id;
|
|
|
|
-- name: set-conversation-tags
|
|
WITH conversation_id AS (
|
|
SELECT id FROM conversations WHERE uuid = $1
|
|
),
|
|
-- Insert new tags
|
|
inserted AS (
|
|
INSERT INTO conversation_tags (conversation_id, tag_id)
|
|
SELECT conversation_id.id, t.id
|
|
FROM conversation_id, tags t
|
|
WHERE t.name = ANY($2::text[])
|
|
ON CONFLICT (conversation_id, tag_id) DO UPDATE SET tag_id = EXCLUDED.tag_id
|
|
)
|
|
-- Delete tags that are not in the new list
|
|
DELETE FROM conversation_tags
|
|
WHERE conversation_id = (SELECT id FROM conversation_id)
|
|
AND tag_id NOT IN (
|
|
SELECT id FROM tags WHERE name = ANY($2::text[])
|
|
);
|
|
|
|
-- name: remove-conversation-tags
|
|
-- Delete tags that are not in the new list
|
|
DELETE FROM conversation_tags
|
|
WHERE conversation_id = (SELECT id FROM conversations WHERE uuid = $1)
|
|
AND tag_id IN (
|
|
SELECT id FROM tags WHERE name = ANY($2::text[])
|
|
);
|
|
|
|
-- name: get-conversation-tags
|
|
SELECT t.name
|
|
FROM conversation_tags ct
|
|
JOIN tags t ON ct.tag_id = t.id
|
|
WHERE ct.conversation_id = (SELECT id FROM conversations WHERE uuid = $1);
|
|
|
|
-- name: get-conversation-uuid-from-message-uuid
|
|
SELECT c.uuid AS conversation_uuid
|
|
FROM conversation_messages m
|
|
JOIN conversations c ON m.conversation_id = c.id
|
|
WHERE m.uuid = $1;
|
|
|
|
-- name: unassign-open-conversations
|
|
UPDATE conversations
|
|
SET assigned_user_id = NULL,
|
|
updated_at = NOW()
|
|
WHERE assigned_user_id = $1 AND status_id IN (SELECT id FROM conversation_statuses WHERE category != 'resolved');
|
|
|
|
-- name: update-conversation-custom-attributes
|
|
UPDATE conversations
|
|
SET custom_attributes = $2,
|
|
updated_at = NOW()
|
|
WHERE uuid = $1;
|
|
|
|
-- name: start-conversation-waiting-since
|
|
UPDATE conversations
|
|
SET waiting_since = $2,
|
|
updated_at = NOW()
|
|
WHERE uuid = $1 AND waiting_since IS NULL;
|
|
|
|
-- name: update-conversation-reply-timestamps
|
|
WITH old AS (
|
|
SELECT first_reply_at IS NULL AS is_first FROM conversations WHERE id = $1
|
|
)
|
|
UPDATE conversations SET
|
|
first_reply_at = COALESCE(conversations.first_reply_at, $2),
|
|
last_reply_at = $2,
|
|
waiting_since = NULL,
|
|
updated_at = NOW()
|
|
FROM old WHERE conversations.id = $1
|
|
RETURNING old.is_first AS is_first_reply;
|
|
|
|
-- name: remove-conversation-assignee
|
|
UPDATE conversations
|
|
SET
|
|
assigned_user_id = CASE WHEN $2 = 'user' THEN NULL ELSE assigned_user_id END,
|
|
assigned_team_id = CASE WHEN $2 = 'team' THEN NULL ELSE assigned_team_id END,
|
|
updated_at = NOW()
|
|
WHERE uuid = $1;
|
|
|
|
-- name: re-open-conversation
|
|
-- Open conversation if it is not already open and unset the assigned user if they are away and reassigning.
|
|
UPDATE conversations
|
|
SET
|
|
status_id = (SELECT id FROM conversation_statuses WHERE name = 'Open'),
|
|
snoozed_until = NULL,
|
|
updated_at = NOW(),
|
|
assigned_user_id = CASE
|
|
WHEN EXISTS (
|
|
SELECT 1 FROM users
|
|
WHERE users.id = conversations.assigned_user_id
|
|
AND users.availability_status = 'away_and_reassigning'
|
|
) THEN NULL
|
|
ELSE assigned_user_id
|
|
END
|
|
WHERE
|
|
uuid = $1
|
|
AND status_id IN (
|
|
SELECT id FROM conversation_statuses WHERE name NOT IN ('Open')
|
|
)
|
|
RETURNING id;
|
|
|
|
-- name: get-conversation-by-message-id
|
|
SELECT
|
|
c.id,
|
|
c.uuid,
|
|
c.assigned_team_id,
|
|
c.assigned_user_id
|
|
FROM conversation_messages m
|
|
JOIN conversations c ON m.conversation_id = c.id
|
|
WHERE m.id = $1;
|
|
|
|
-- name: delete-conversation
|
|
DELETE FROM conversations WHERE uuid = $1;
|
|
|
|
-- MESSAGE queries.
|
|
-- name: delete-message
|
|
DELETE FROM conversation_messages WHERE CASE
|
|
WHEN $1 > 0 THEN id = $1
|
|
ELSE uuid = $2
|
|
END;
|
|
|
|
-- name: delete-private-message
|
|
-- $1 = message uuid, $2 = conversation uuid, $3 = deleted placeholder text, $4 = sender id, 0 to skip the sender check.
|
|
WITH deleted AS (
|
|
UPDATE conversation_messages
|
|
SET content = $3, text_content = $3, updated_at = NOW(),
|
|
meta = COALESCE(meta, '{}'::jsonb) || jsonb_build_object('deleted_at', NOW())
|
|
WHERE uuid = $1
|
|
AND private = true
|
|
AND meta->>'deleted_at' IS NULL
|
|
AND ($4 = 0 OR sender_id = $4)
|
|
AND conversation_id = (SELECT id FROM conversations WHERE uuid = $2)
|
|
RETURNING id, conversation_id, created_at
|
|
),
|
|
media_unlink AS (
|
|
UPDATE media SET model_id = 0
|
|
FROM deleted d
|
|
WHERE media.model_type = 'messages' AND media.model_id = d.id
|
|
),
|
|
preview AS (
|
|
UPDATE conversations c
|
|
SET last_message = $3, updated_at = NOW()
|
|
FROM deleted d
|
|
WHERE c.id = d.conversation_id
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM conversation_messages m2
|
|
WHERE m2.conversation_id = d.conversation_id
|
|
AND m2.created_at > d.created_at
|
|
)
|
|
RETURNING c.id
|
|
)
|
|
SELECT EXISTS (SELECT 1 FROM preview) AS preview_updated
|
|
FROM deleted d;
|
|
|
|
-- name: get-message-source-ids
|
|
SELECT
|
|
source_id
|
|
FROM conversation_messages
|
|
WHERE conversation_id = $1
|
|
AND type in ('incoming', 'outgoing') and private = false
|
|
and source_id > ''
|
|
ORDER BY id DESC
|
|
LIMIT $2;
|
|
|
|
-- name: get-previous-messages
|
|
SELECT
|
|
m.created_at,
|
|
m.type = 'incoming' AS is_incoming,
|
|
COALESCE(m.content, '') AS content,
|
|
COALESCE(m.text_content, '') AS text_content,
|
|
COALESCE(m.content_type::text, '') AS content_type,
|
|
u.first_name,
|
|
COALESCE(u.last_name, '') AS last_name,
|
|
COALESCE(u.email, '') AS email
|
|
FROM conversation_messages m
|
|
JOIN conversations c ON c.id = m.conversation_id
|
|
JOIN users u ON u.id = m.sender_id
|
|
JOIN users ct ON ct.id = c.contact_id
|
|
WHERE m.conversation_id = $1
|
|
AND m.id < $2
|
|
AND m.private = false
|
|
AND m.status != 'failed'
|
|
AND NOT COALESCE((m.meta->>'continuity_email')::boolean, false)
|
|
AND NOT COALESCE((m.meta->>'is_csat')::boolean, false)
|
|
AND (
|
|
(m.type = 'incoming' AND m.sender_id = c.contact_id)
|
|
OR (m.type = 'outgoing' AND EXISTS (
|
|
SELECT 1
|
|
FROM jsonb_array_elements_text(COALESCE(m.meta->'to', '[]'::jsonb) || COALESCE(m.meta->'cc', '[]'::jsonb)) AS r(addr)
|
|
WHERE lower(r.addr) = lower(ct.email)
|
|
))
|
|
)
|
|
ORDER BY m.id DESC
|
|
LIMIT $3;
|
|
|
|
-- name: get-email-sender-message
|
|
SELECT m.type, m.meta
|
|
FROM conversation_messages m
|
|
JOIN conversations c ON c.id = m.conversation_id
|
|
WHERE c.uuid = $1 AND m.private = false AND m.type IN ('incoming', 'outgoing')
|
|
ORDER BY (m.type = 'outgoing') DESC, m.id DESC
|
|
LIMIT 1;
|
|
|
|
-- name: get-outgoing-pending-messages
|
|
SELECT
|
|
m.id,
|
|
m.created_at,
|
|
m.updated_at,
|
|
m.status,
|
|
m.type,
|
|
m.content,
|
|
m.text_content,
|
|
m.sender_type,
|
|
m.content_type,
|
|
m.conversation_id,
|
|
m.uuid,
|
|
m.private,
|
|
m.sender_type,
|
|
m.sender_id,
|
|
m.meta,
|
|
c.uuid as conversation_uuid,
|
|
m.content_type,
|
|
m.source_id,
|
|
m.meta,
|
|
ARRAY(SELECT jsonb_array_elements_text(m.meta->'cc')) AS cc,
|
|
ARRAY(SELECT jsonb_array_elements_text(m.meta->'bcc')) AS bcc,
|
|
ARRAY(SELECT jsonb_array_elements_text(m.meta->'to')) AS to,
|
|
c.inbox_id,
|
|
c.uuid as conversation_uuid,
|
|
c.subject,
|
|
c.contact_id as message_receiver_id,
|
|
c.subject,
|
|
c.reference_number
|
|
FROM conversation_messages m
|
|
INNER JOIN conversations c ON c.id = m.conversation_id
|
|
WHERE m.status = 'pending' AND m.type = 'outgoing' AND m.private = false
|
|
AND NOT (m.conversation_id = ANY($1::INT[]))
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM conversation_messages earlier
|
|
WHERE earlier.conversation_id = m.conversation_id
|
|
AND earlier.status = 'pending'
|
|
AND earlier.type = 'outgoing'
|
|
AND earlier.private = false
|
|
AND earlier.id < m.id
|
|
)
|
|
ORDER BY m.id
|
|
|
|
-- name: get-message
|
|
SELECT
|
|
m.id,
|
|
m.created_at,
|
|
m.updated_at,
|
|
m.status,
|
|
m.type,
|
|
m.content,
|
|
m.text_content,
|
|
m.content_type,
|
|
m.conversation_id,
|
|
m.uuid,
|
|
m.private,
|
|
m.sender_type,
|
|
m.sender_id,
|
|
m.meta,
|
|
c.uuid as conversation_uuid,
|
|
u.id AS "author.id",
|
|
u.first_name AS "author.first_name",
|
|
u.last_name AS "author.last_name",
|
|
u.email AS "author.email",
|
|
u.avatar_url AS "author.avatar_url",
|
|
u.availability_status AS "author.availability_status",
|
|
u.type AS "author.type",
|
|
u.last_active_at AS "author.last_active_at",
|
|
COALESCE(
|
|
json_agg(
|
|
json_build_object(
|
|
'name', media.filename,
|
|
'content_type', media.content_type,
|
|
'uuid', media.uuid,
|
|
'size', media.size,
|
|
'content_id', media.content_id,
|
|
'disposition', media.disposition
|
|
) ORDER BY media.filename
|
|
) FILTER (WHERE media.id IS NOT NULL),
|
|
'[]'::json
|
|
) AS attachments
|
|
FROM conversation_messages m
|
|
INNER JOIN conversations c ON c.id = m.conversation_id
|
|
JOIN users u ON m.sender_id = u.id
|
|
LEFT JOIN media ON media.model_type = 'messages' AND media.model_id = m.id
|
|
WHERE m.uuid = $1
|
|
GROUP BY
|
|
m.id, m.created_at, m.updated_at, m.status, m.type, m.content, m.uuid, m.private, m.sender_type, c.uuid,
|
|
u.id, u.first_name, u.last_name, u.email, u.avatar_url, u.availability_status, u.type, u.last_active_at
|
|
ORDER BY m.created_at;
|
|
|
|
-- name: get-messages
|
|
SELECT
|
|
COUNT(*) OVER() AS total,
|
|
m.id,
|
|
m.created_at,
|
|
m.updated_at,
|
|
m.status,
|
|
m.type,
|
|
m.content,
|
|
m.text_content,
|
|
m.content_type,
|
|
m.conversation_id,
|
|
m.uuid,
|
|
m.private,
|
|
m.sender_id,
|
|
m.sender_type,
|
|
m.meta,
|
|
$1::uuid AS conversation_uuid,
|
|
u.id AS "author.id",
|
|
u.first_name AS "author.first_name",
|
|
u.last_name AS "author.last_name",
|
|
u.email AS "author.email",
|
|
u.avatar_url AS "author.avatar_url",
|
|
u.availability_status AS "author.availability_status",
|
|
u.type AS "author.type",
|
|
u.last_active_at AS "author.last_active_at",
|
|
COALESCE(
|
|
(SELECT json_agg(
|
|
json_build_object(
|
|
'name', filename,
|
|
'content_type', content_type,
|
|
'uuid', uuid,
|
|
'size', size,
|
|
'content_id', content_id,
|
|
'disposition', disposition
|
|
) ORDER BY filename
|
|
) FROM media
|
|
WHERE model_type = 'messages' AND model_id = m.id),
|
|
'[]'::json) AS attachments
|
|
FROM conversation_messages m
|
|
JOIN users u ON m.sender_id = u.id
|
|
WHERE m.conversation_id = (
|
|
SELECT id FROM conversations WHERE uuid = $1 LIMIT 1
|
|
)
|
|
AND ($2::boolean IS NULL OR m.private = $2)
|
|
AND ($3::text[] IS NULL OR m.type::text = ANY($3))
|
|
AND (m.meta IS NULL OR NOT COALESCE((m.meta->>'continuity_email')::boolean, false))
|
|
ORDER BY m.created_at DESC, m.id DESC %s
|
|
|
|
-- name: insert-message
|
|
WITH conversation_id AS (
|
|
SELECT id
|
|
FROM conversations
|
|
WHERE CASE
|
|
WHEN $3 > 0 THEN id = $3
|
|
ELSE uuid = $4
|
|
END
|
|
),
|
|
inserted_msg AS (
|
|
INSERT INTO conversation_messages (
|
|
"type", status, conversation_id, "content",
|
|
text_content, sender_id, sender_type, private,
|
|
content_type, source_id, meta
|
|
)
|
|
VALUES (
|
|
$1, $2, (SELECT id FROM conversation_id),
|
|
$5, $6, $7, $8, $9, $10, $11, $12
|
|
)
|
|
RETURNING *
|
|
)
|
|
SELECT * FROM inserted_msg;
|
|
|
|
-- name: message-exists-by-source-id
|
|
SELECT conversation_id
|
|
FROM conversation_messages
|
|
WHERE source_id = ANY($1::text []);
|
|
|
|
-- name: update-message-status
|
|
UPDATE conversation_messages SET status = $1::message_status, updated_at = NOW()
|
|
WHERE uuid = $2 AND NOT ($1 = 'sent' AND status = 'failed');
|
|
|
|
-- name: mark-message-pending-for-retry
|
|
-- Keeping the old wamid or provider_status would let a late webhook re-fail the retried row.
|
|
UPDATE conversation_messages m
|
|
SET status = 'pending',
|
|
source_id = CASE WHEN inb.channel = 'whatsapp' THEN NULL ELSE m.source_id END,
|
|
meta = COALESCE(m.meta, '{}'::jsonb)
|
|
- 'provider_status' - 'provider_status_updated_at' - 'provider_sent_at'
|
|
- 'provider_delivered_at' - 'provider_read_at' - 'provider_failed_at'
|
|
- 'provider_failure_reason',
|
|
updated_at = NOW()
|
|
FROM conversations c
|
|
JOIN inboxes inb ON inb.id = c.inbox_id
|
|
WHERE m.uuid = $1 AND c.id = m.conversation_id AND m.status IN ('failed', 'sent');
|
|
|
|
-- name: update-message-source-id
|
|
UPDATE conversation_messages SET source_id = $1 WHERE id = $2;
|
|
|
|
-- name: update-message-source-id-by-uuid
|
|
UPDATE conversation_messages SET source_id = $2, updated_at = NOW() WHERE uuid = $1;
|
|
|
|
-- name: merge-message-meta-by-uuid
|
|
UPDATE conversation_messages m
|
|
SET meta = COALESCE(m.meta, '{}'::jsonb) || $2::jsonb,
|
|
updated_at = NOW()
|
|
FROM conversations c
|
|
WHERE m.uuid = $1
|
|
AND c.id = m.conversation_id
|
|
RETURNING m.uuid, c.uuid AS conversation_uuid, m.meta;
|
|
|
|
-- name: apply-whatsapp-message-status
|
|
-- Meta guard is monotonic (rank order) and sticky on failure. The enum status only guards against un-failing a failed message.
|
|
WITH ranks(status, rank) AS (
|
|
VALUES ('sent', 1), ('delivered', 2), ('read', 3), ('failed', 4)
|
|
)
|
|
UPDATE conversation_messages m
|
|
SET status = CASE WHEN m.status != 'failed' THEN $2::message_status ELSE m.status END,
|
|
meta = CASE
|
|
WHEN COALESCE(m.meta->>'provider_status', '') != 'failed'
|
|
AND COALESCE(
|
|
(SELECT rank FROM ranks WHERE status = ($3::jsonb)->>'provider_status'),
|
|
0
|
|
) >= COALESCE(
|
|
(SELECT rank FROM ranks WHERE status = m.meta->>'provider_status'),
|
|
0
|
|
)
|
|
THEN COALESCE(m.meta, '{}'::jsonb) || $3::jsonb
|
|
ELSE m.meta
|
|
END,
|
|
updated_at = NOW()
|
|
FROM conversations c
|
|
WHERE m.source_id = $1
|
|
AND c.id = m.conversation_id
|
|
RETURNING m.uuid, c.uuid AS conversation_uuid, m.status, m.meta;
|
|
|
|
-- name: get-whatsapp-read-receipt-target
|
|
SELECT cm.source_id, c.inbox_id
|
|
FROM conversation_messages cm
|
|
JOIN conversations c ON c.id = cm.conversation_id
|
|
JOIN inboxes i ON i.id = c.inbox_id
|
|
WHERE c.uuid = $1
|
|
AND i.channel = 'whatsapp'
|
|
AND ($3::text = '' OR cm.created_at <= (SELECT created_at FROM conversation_messages WHERE conversation_id = c.id AND uuid = NULLIF($3::text, '')::uuid))
|
|
AND cm.type = 'incoming'
|
|
AND COALESCE(cm.source_id, '') != ''
|
|
AND cm.created_at > COALESCE(
|
|
(SELECT last_seen_at FROM conversation_last_seen ls
|
|
WHERE ls.conversation_id = c.id AND ls.user_id = $2),
|
|
'epoch'::timestamptz)
|
|
ORDER BY cm.created_at DESC
|
|
LIMIT 1;
|
|
|
|
-- name: update-conversation-last-inbound-at
|
|
UPDATE conversations
|
|
SET last_inbound_at = GREATEST(last_inbound_at, $2),
|
|
updated_at = CASE WHEN last_inbound_at IS NULL OR last_inbound_at < $2 THEN NOW() ELSE updated_at END
|
|
WHERE id = $1
|
|
RETURNING contact_id, inbox_id;
|
|
|
|
-- name: get-contact-window-inbound-at
|
|
SELECT MAX(last_inbound_at) FROM conversations WHERE contact_id = $1 AND inbox_id = $2;
|
|
|
|
-- name: get-latest-open-conversation-by-contact-inbox
|
|
SELECT id, uuid
|
|
FROM conversations
|
|
WHERE contact_id = $1
|
|
AND inbox_id = $2
|
|
AND status_id IN (SELECT id FROM conversation_statuses WHERE category != 'resolved')
|
|
ORDER BY last_interaction_at DESC NULLS LAST, created_at DESC
|
|
LIMIT 1;
|
|
|
|
-- name: get-latest-reopenable-conversation-by-contact-inbox
|
|
SELECT id, uuid
|
|
FROM conversations
|
|
WHERE contact_id = $1
|
|
AND inbox_id = $2
|
|
AND status_id IN (SELECT id FROM conversation_statuses WHERE category = 'resolved')
|
|
AND last_resolved_at IS NOT NULL
|
|
AND last_resolved_at >= NOW() - make_interval(hours => $3)
|
|
ORDER BY last_resolved_at DESC
|
|
LIMIT 1;
|
|
|
|
-- name: clear-message-handoff-form-pending
|
|
UPDATE conversation_messages
|
|
SET meta = meta || '{"handoff_form_pending": false}'::jsonb, updated_at = NOW()
|
|
WHERE uuid = $1 AND COALESCE((meta->>'handoff_form_pending')::boolean, false);
|
|
|
|
-- name: get-offline-livechat-conversations
|
|
SELECT
|
|
c.id,
|
|
c.uuid,
|
|
c.contact_id,
|
|
c.inbox_id,
|
|
c.contact_last_seen_at,
|
|
c.last_continuity_email_sent_at,
|
|
i.linked_email_inbox_id,
|
|
u.email as contact_email,
|
|
u.first_name as contact_first_name,
|
|
u.last_name as contact_last_name,
|
|
c.reference_number,
|
|
c.meta->>'continuity_email_subject' as continuity_email_subject
|
|
FROM conversations c
|
|
JOIN users u ON u.id = c.contact_id
|
|
JOIN inboxes i ON i.id = c.inbox_id
|
|
WHERE i.channel = 'livechat'
|
|
AND i.enabled = TRUE
|
|
AND i.linked_email_inbox_id IS NOT NULL
|
|
AND c.contact_last_seen_at IS NOT NULL
|
|
AND c.contact_last_seen_at < NOW() - MAKE_INTERVAL(mins => $1)
|
|
AND EXISTS (
|
|
SELECT 1 FROM conversation_messages cm
|
|
WHERE cm.conversation_id = c.id
|
|
AND cm.created_at > c.contact_last_seen_at
|
|
AND cm.type = 'outgoing'
|
|
AND cm.private = false
|
|
AND (cm.meta IS NULL OR NOT COALESCE((cm.meta->>'continuity_email')::boolean, false))
|
|
AND (cm.meta IS NULL OR NOT COALESCE((cm.meta->>'continuity_emailed')::boolean, false))
|
|
)
|
|
AND u.email > ''
|
|
AND (c.last_continuity_email_sent_at IS NULL
|
|
OR c.last_continuity_email_sent_at < NOW() - MAKE_INTERVAL(mins => $2))
|
|
AND c.inbox_id = $3;
|
|
|
|
-- name: get-unread-messages
|
|
SELECT
|
|
m.id,
|
|
m.created_at,
|
|
m.updated_at,
|
|
m.status,
|
|
m.type,
|
|
m.content,
|
|
m.text_content,
|
|
m.uuid,
|
|
m.private,
|
|
m.sender_id,
|
|
m.sender_type,
|
|
m.meta,
|
|
u.first_name as "sender.first_name",
|
|
u.last_name as "sender.last_name",
|
|
u.type as "sender.type",
|
|
COALESCE((SELECT string_agg(md.filename, ',') FROM media md WHERE md.model_id = m.id AND md.model_type = 'messages'), '') AS attachment_names
|
|
FROM conversation_messages m
|
|
LEFT JOIN users u ON u.id = m.sender_id
|
|
WHERE m.conversation_id = $1
|
|
AND m.created_at > $2
|
|
AND m.type = 'outgoing'
|
|
AND m.private = false
|
|
AND (m.meta IS NULL OR NOT COALESCE((m.meta->>'continuity_email')::boolean, false))
|
|
AND (m.meta IS NULL OR NOT COALESCE((m.meta->>'continuity_emailed')::boolean, false))
|
|
ORDER BY m.created_at ASC
|
|
LIMIT $3;
|
|
|
|
-- name: mark-messages-continuity-emailed
|
|
UPDATE conversation_messages
|
|
SET meta = COALESCE(meta, '{}'::jsonb) || '{"continuity_emailed": true}'::jsonb
|
|
WHERE id = ANY($1::int[]);
|
|
|
|
-- name: update-continuity-email-tracking
|
|
UPDATE conversations
|
|
SET last_continuity_email_sent_at = NOW(),
|
|
meta = jsonb_set(COALESCE(meta, '{}'::jsonb), '{continuity_email_subject}', to_jsonb($2::text))
|
|
WHERE id = $1;
|
|
|
|
-- name: upsert-conversation-draft
|
|
INSERT INTO conversation_drafts (conversation_id, user_id, type, content, meta, updated_at)
|
|
VALUES ($1, $2, $3, $4, $5, NOW())
|
|
ON CONFLICT (conversation_id, user_id, type)
|
|
DO UPDATE SET content = EXCLUDED.content, meta = EXCLUDED.meta, updated_at = NOW()
|
|
RETURNING *;
|
|
|
|
-- name: get-all-user-drafts
|
|
SELECT cd.id, cd.conversation_id, cd.user_id, cd.type, cd.content, cd.meta, cd.created_at, cd.updated_at, c.uuid as conversation_uuid
|
|
FROM conversation_drafts cd
|
|
INNER JOIN conversations c ON cd.conversation_id = c.id
|
|
WHERE cd.user_id = $1
|
|
ORDER BY cd.updated_at DESC;
|
|
|
|
-- name: delete-conversation-draft
|
|
DELETE FROM conversation_drafts
|
|
WHERE conversation_id IN (
|
|
SELECT id FROM conversations
|
|
WHERE ($1 > 0 AND id = $1) OR (NULLIF($2, '')::uuid IS NOT NULL AND uuid = NULLIF($2, '')::uuid)
|
|
) AND user_id = $3
|
|
AND ($4::text = '' OR type = $4::text);
|
|
|
|
-- name: delete-stale-drafts
|
|
DELETE FROM conversation_drafts
|
|
WHERE created_at < $1;
|
|
|
|
-- name: insert-mention
|
|
INSERT INTO conversation_mentions (conversation_id, message_id, mentioned_user_id, mentioned_team_id, mentioned_by_user_id)
|
|
VALUES ($1, $2, $3, $4, $5);
|
|
|
|
-- name: mark-conversation-unread
|
|
WITH target AS (
|
|
SELECT id FROM conversations WHERE uuid = $2
|
|
),
|
|
last_msg AS (
|
|
SELECT created_at - INTERVAL '1 second' AS ts
|
|
FROM conversation_messages
|
|
WHERE conversation_id = (SELECT id FROM target)
|
|
AND (meta IS NULL OR NOT COALESCE((meta->>'continuity_email')::boolean, false))
|
|
ORDER BY created_at DESC LIMIT 1
|
|
),
|
|
read_replies AS (
|
|
UPDATE user_notifications n SET is_read = true, updated_at = NOW()
|
|
FROM conversation_messages m, conversation_last_seen ls
|
|
WHERE n.user_id = $1 AND n.conversation_id = (SELECT id FROM target) AND NOT n.is_read
|
|
AND n.notification_type = ANY($3::user_notification_type[])
|
|
AND m.id = n.message_id
|
|
AND ls.user_id = $1 AND ls.conversation_id = n.conversation_id AND ls.last_seen_at >= m.created_at
|
|
)
|
|
INSERT INTO conversation_last_seen (user_id, conversation_id, last_seen_at, updated_at)
|
|
SELECT $1, (SELECT id FROM target), ts, clock_timestamp() FROM last_msg
|
|
ON CONFLICT (conversation_id, user_id)
|
|
DO UPDATE SET
|
|
last_seen_at = EXCLUDED.last_seen_at,
|
|
updated_at = GREATEST(conversation_last_seen.updated_at + INTERVAL '1 microsecond', clock_timestamp())
|
|
RETURNING last_seen_at, (EXTRACT(EPOCH FROM updated_at) * 1000000)::bigint AS read_version;
|
|
|
|
-- name: get-active-livechat-conversations-by-agent
|
|
SELECT c.uuid, c.contact_id, c.inbox_id
|
|
FROM conversations c
|
|
JOIN inboxes i ON i.id = c.inbox_id
|
|
JOIN users u ON u.id = c.contact_id
|
|
WHERE c.assigned_user_id = $1
|
|
AND i.channel = 'livechat'
|
|
AND i.enabled = TRUE
|
|
AND i.deleted_at IS NULL
|
|
AND u.availability_status = 'online'
|
|
ORDER BY c.last_interaction_at DESC
|
|
LIMIT 50;
|
|
|
|
-- name: filter-authorized-list-uuids
|
|
-- $1: uuids (uuid[])
|
|
-- $2: user_id
|
|
-- $3: team_ids (int[])
|
|
-- $4: has 'conversations:read'
|
|
-- $5: has 'conversations:read_all'
|
|
-- $6: has 'conversations:read_assigned'
|
|
-- $7: has 'conversations:read_team_all'
|
|
-- $8: has 'conversations:read_team_inbox'
|
|
-- $9: has 'conversations:read_unassigned'
|
|
SELECT uuid::text
|
|
FROM conversations
|
|
WHERE uuid = ANY($1::uuid[])
|
|
AND $4
|
|
AND (
|
|
$5
|
|
OR ($6 AND assigned_user_id = $2)
|
|
OR ($7 AND assigned_team_id = ANY($3::int[]))
|
|
OR ($8 AND assigned_team_id = ANY($3::int[]) AND assigned_user_id IS NULL)
|
|
OR ($9 AND assigned_user_id IS NULL AND assigned_team_id IS NULL)
|
|
);
|
|
|
|
-- name: get-conversation-uuids-by-contact
|
|
SELECT uuid::text
|
|
FROM conversations
|
|
WHERE contact_id = $1
|
|
ORDER BY last_message_at DESC NULLS LAST
|
|
LIMIT 200;
|
|
|
|
-- name: get-conversation-uuids-by-contact-inbox
|
|
SELECT uuid::text
|
|
FROM conversations
|
|
WHERE contact_id = $1 AND inbox_id = $2
|
|
ORDER BY last_message_at DESC NULLS LAST
|
|
LIMIT 200;
|
|
|
|
-- name: lock-campaign-delivery
|
|
SELECT COALESCE(conversation_uuid::text, '') FROM widget_campaign_deliveries WHERE id = $1 FOR UPDATE;
|
|
|
|
-- name: complete-campaign-delivery
|
|
UPDATE widget_campaign_deliveries
|
|
SET conversation_uuid = $2, contact_id = $3, replied = TRUE, opened = TRUE, displayed = TRUE
|
|
WHERE id = $1;
|
|
|
|
-- name: assign-proactive-team
|
|
UPDATE conversations SET assigned_team_id = NULLIF($2, 0) WHERE id = $1;
|