libredesk/internal/conversation/queries.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
-- Assigns only if the conversation is still unassigned in the team and the user is still an enabled, non-away agent of that team.
WITH assignee AS (
    SELECT u.id
    FROM users u
    JOIN team_members tm ON tm.user_id = u.id AND tm.team_id = $3
    WHERE u.id = $2 AND u.deleted_at IS NULL AND u.type = 'agent' AND u.enabled = true
    AND u.availability_status NOT IN ('away_manual', 'away_and_reassigning')
),
claimed AS (
    UPDATE conversations
    SET assigned_user_id = $2,
    updated_at = NOW()
    WHERE uuid = $1 AND assigned_user_id IS NULL AND assigned_team_id = $3
    AND EXISTS (SELECT 1 FROM assignee)
    RETURNING id
)
SELECT EXISTS (SELECT 1 FROM assignee), EXISTS (SELECT 1 FROM claimed);

-- 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-csat-messages
SELECT m.uuid, m.meta, c.uuid AS conversation_uuid
FROM csat_responses r
JOIN conversations c ON c.id = r.conversation_id
JOIN conversation_messages m ON m.conversation_id = c.id
WHERE r.uuid = $1 AND m.meta->>'csat_uuid' = r.uuid::text;

-- 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;