Skip to content

armgit / macro

Public Imported from GitHub
0

Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.

15.1 KB
Raw Blame
-- Fixture for testing dynamic email query builder
-- Sets up threads, messages, contacts, recipients, labels for comprehensive testing

-- Insert test user for project ownership
INSERT INTO "macro_user" (id, username, email, stripe_customer_id)
VALUES ('a1111111-1111-1111-1111-111111111111', '[email protected]', '[email protected]', 'stripe_user1');

INSERT INTO "User" (id, email, name, macro_user_id)
VALUES ('macro|[email protected]', '[email protected]', 'Test User 1', 'a1111111-1111-1111-1111-111111111111');

-- Insert test projects
INSERT INTO "Project" (id, name, "userId", "createdAt", "updatedAt")
VALUES
    ('proj-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'Project Alpha', 'macro|[email protected]', NOW(), NOW()),
    ('proj-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'Project Beta', 'macro|[email protected]', NOW(), NOW());

-- Insert test link
INSERT INTO email_links (id, macro_id, fusionauth_user_id, email_address, provider, is_sync_active, created_at, updated_at)
VALUES
    ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'macro|[email protected]', 'user1', '[email protected]', 'GMAIL', true, NOW(), NOW());

-- Insert test contacts
INSERT INTO email_contacts (id, link_id, email_address, name, created_at, updated_at)
VALUES
    ('40000001-0000-0000-0000-000000000001', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '[email protected]', 'John Doe', NOW(), NOW()),
    ('40000002-0000-0000-0000-000000000002', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '[email protected]', 'Jane Smith', NOW(), NOW()),
    ('40000003-0000-0000-0000-000000000003', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '[email protected]', 'Bob Johnson', NOW(), NOW()),
    ('40000004-0000-0000-0000-000000000004', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '[email protected]', 'Alice Williams', NOW(), NOW());

-- Insert test labels
INSERT INTO email_labels (id, link_id, provider_label_id, name, message_list_visibility, label_list_visibility, type, created_at)
VALUES
    ('10000001-0000-0000-0000-000000000001', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'INBOX', 'INBOX', 'Show', 'LabelShow', 'System', NOW()),
    ('10000002-0000-0000-0000-000000000002', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'IMPORTANT', 'IMPORTANT', 'Show', 'LabelShow', 'System', NOW()),
    ('10000003-0000-0000-0000-000000000003', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'SENT', 'SENT', 'Show', 'LabelShow', 'System', NOW()),
    ('10000004-0000-0000-0000-000000000004', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'STARRED', 'STARRED', 'Show', 'LabelShow', 'System', NOW()),
    ('10000005-0000-0000-0000-000000000005', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'DRAFT', 'DRAFT', 'Show', 'LabelShow', 'System', NOW()),
    ('10000006-0000-0000-0000-000000000006', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'TRASH', 'TRASH', 'Hide', 'LabelHide', 'System', NOW()),
    ('10000007-0000-0000-0000-000000000007', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'Label_Work', 'Work', 'Show', 'LabelShow', 'User', NOW()),
    -- Category labels for importance testing
    ('10000008-0000-0000-0000-000000000008', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'CATEGORY_PERSONAL', 'CATEGORY_PERSONAL', 'Show', 'LabelShow', 'System', NOW()),
    ('10000009-0000-0000-0000-000000000009', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'CATEGORY_UPDATES', 'CATEGORY_UPDATES', 'Show', 'LabelShow', 'System', NOW()),
    ('10000010-0000-0000-0000-000000000010', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'CATEGORY_PROMOTIONS', 'CATEGORY_PROMOTIONS', 'Show', 'LabelShow', 'System', NOW());

-- Insert test threads
INSERT INTO email_threads (
    id, provider_id, link_id, inbox_visible, is_read,
    latest_inbound_message_ts, latest_outbound_message_ts, latest_non_spam_message_ts,
    created_at, updated_at
)
VALUES
    -- Thread 1: Inbox, from [email protected], in Project Alpha
    ('20000001-0000-0000-0000-000000000001', 'thread1', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
     true, false, '2024-01-15 10:00:00+00', NULL, '2024-01-15 10:00:00+00', NOW(), NOW()),

    -- Thread 2: Sent, to [email protected], in Project Alpha
    ('20000002-0000-0000-0000-000000000002', 'thread2', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
     false, true, NULL, '2024-01-14 09:00:00+00', '2024-01-14 09:00:00+00', NOW(), NOW()),

    -- Thread 3: Draft from [email protected] (no project)
    ('20000003-0000-0000-0000-000000000003', 'thread3', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
     false, false, NULL, NULL, '2024-01-13 08:00:00+00', NOW(), NOW()),

    -- Thread 4: Starred, from [email protected] (no project)
    ('20000004-0000-0000-0000-000000000004', 'thread4', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
     true, false, '2024-01-12 07:00:00+00', NULL, '2024-01-12 07:00:00+00', NOW(), NOW()),

    -- Thread 5: Important inbox from [email protected], in Project Alpha
    ('20000005-0000-0000-0000-000000000005', 'thread5', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
     true, false, '2024-01-11 06:00:00+00', NULL, '2024-01-11 06:00:00+00', NOW(), NOW()),

    -- Thread 6: User label "Work", from [email protected], in Project Beta
    ('20000006-0000-0000-0000-000000000006', 'thread6', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
     false, false, NULL, NULL, '2024-01-10 05:00:00+00', NOW(), NOW()),

    -- Thread 7: Inbox with CC to [email protected]
    ('20000007-0000-0000-0000-000000000007', 'thread7', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
     true, false, '2024-01-09 04:00:00+00', NULL, '2024-01-09 04:00:00+00', NOW(), NOW()),

    -- Thread 8: Draft with depriority label (CATEGORY_UPDATES) - tests that DRAFT priority overrides depriority
    ('20000008-0000-0000-0000-000000000008', 'thread8', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
     false, false, NULL, NULL, '2024-01-08 03:00:00+00', NOW(), NOW()),

    -- Thread 9: Draft in TRASH - tests that importance=true excludes trashed drafts
    ('20000009-0000-0000-0000-000000000009', 'thread9', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
     false, false, NULL, NULL, '2024-01-07 02:00:00+00', NOW(), NOW()),

    -- Thread 10: Starred message in TRASH - tests that starred view excludes trashed messages
    ('20000010-0000-0000-0000-000000000010', 'thread10', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
     false, false, '2024-01-06 01:00:00+00', NULL, '2024-01-06 01:00:00+00', NOW(), NOW()),

    -- Thread 11: User label "Work" message in TRASH - tests that user label view excludes trashed messages
    ('20000011-0000-0000-0000-000000000011', 'thread11', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
     false, false, NULL, NULL, '2024-01-05 00:00:00+00', NOW(), NOW());

-- Assign threads to projects
UPDATE email_threads SET project_id = 'proj-aaaa-aaaa-aaaa-aaaaaaaaaaaa' WHERE id IN (
    '20000001-0000-0000-0000-000000000001',
    '20000002-0000-0000-0000-000000000002',
    '20000005-0000-0000-0000-000000000005'
);
UPDATE email_threads SET project_id = 'proj-bbbb-bbbb-bbbb-bbbbbbbbbbbb' WHERE id = '20000006-0000-0000-0000-000000000006';

-- Insert test messages
INSERT INTO email_messages (
    id, thread_id, link_id, provider_id, from_contact_id,
    subject, snippet, internal_date_ts,
    is_draft, is_sent, is_starred, is_read,
    created_at, updated_at
)
VALUES
    -- Message 1: Inbox from [email protected]
    ('30000001-0000-0000-0000-000000000001', '20000001-0000-0000-0000-000000000001',
     'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'msg1', '40000001-0000-0000-0000-000000000001',
     'Meeting Tomorrow', 'Lets meet tomorrow at 10am', '2024-01-15 10:00:00+00',
     false, false, false, false, NOW(), NOW()),

    -- Message 2: Sent to [email protected]
    ('30000002-0000-0000-0000-000000000002', '20000002-0000-0000-0000-000000000002',
     'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'msg2', '40000001-0000-0000-0000-000000000001',
     'Project Update', 'Here is the update', '2024-01-14 09:00:00+00',
     false, true, false, true, NOW(), NOW()),

    -- Message 3: Draft from [email protected]
    ('30000003-0000-0000-0000-000000000003', '20000003-0000-0000-0000-000000000003',
     'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'msg3', '40000003-0000-0000-0000-000000000003',
     'Draft Email', 'This is a draft', '2024-01-13 08:00:00+00',
     true, false, false, false, NOW(), NOW()),

    -- Message 4: Starred from [email protected]
    ('30000004-0000-0000-0000-000000000004', '20000004-0000-0000-0000-000000000004',
     'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'msg4', '40000004-0000-0000-0000-000000000004',
     'Important Info', 'Please review', '2024-01-12 07:00:00+00',
     false, false, true, false, NOW(), NOW()),

    -- Message 5: Important inbox from [email protected]
    ('30000005-0000-0000-0000-000000000005', '20000005-0000-0000-0000-000000000005',
     'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'msg5', '40000001-0000-0000-0000-000000000001',
     'Urgent: Action Required', 'Please respond ASAP', '2024-01-11 06:00:00+00',
     false, false, false, false, NOW(), NOW()),

    -- Message 6: User label "Work" from [email protected]
    ('30000006-0000-0000-0000-000000000006', '20000006-0000-0000-0000-000000000006',
     'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'msg6', '40000002-0000-0000-0000-000000000002',
     'Work Project', 'Quarterly review', '2024-01-10 05:00:00+00',
     false, false, false, false, NOW(), NOW()),

    -- Message 7: Inbox with CC to [email protected]
    ('30000007-0000-0000-0000-000000000007', '20000007-0000-0000-0000-000000000007',
     'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'msg7', '40000002-0000-0000-0000-000000000002',
     'Team Update', 'FYI for everyone', '2024-01-09 04:00:00+00',
     false, false, false, false, NOW(), NOW()),

    -- Message 8: Draft with depriority label from [email protected]
    ('30000008-0000-0000-0000-000000000008', '20000008-0000-0000-0000-000000000008',
     'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'msg8', '40000003-0000-0000-0000-000000000003',
     'Draft With Updates Label', 'Draft that also has a depriority label', '2024-01-08 03:00:00+00',
     true, false, false, false, NOW(), NOW()),

    -- Message 9: Draft in TRASH from [email protected]
    ('30000009-0000-0000-0000-000000000009', '20000009-0000-0000-0000-000000000009',
     'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'msg9', '40000004-0000-0000-0000-000000000004',
     'Trashed Draft', 'A draft that was moved to trash', '2024-01-07 02:00:00+00',
     true, false, false, false, NOW(), NOW()),

    -- Message 10: Starred message in TRASH from [email protected]
    ('30000010-0000-0000-0000-000000000010', '20000010-0000-0000-0000-000000000010',
     'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'msg10', '40000001-0000-0000-0000-000000000001',
     'Trashed Starred', 'A starred message that was moved to trash', '2024-01-06 01:00:00+00',
     false, false, true, false, NOW(), NOW()),

    -- Message 11: User label "Work" message in TRASH from [email protected]
    ('30000011-0000-0000-0000-000000000011', '20000011-0000-0000-0000-000000000011',
     'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'msg11', '40000002-0000-0000-0000-000000000002',
     'Trashed Work Email', 'A work email that was moved to trash', '2024-01-05 00:00:00+00',
     false, false, false, false, NOW(), NOW());

-- Insert message recipients
INSERT INTO email_message_recipients (message_id, contact_id, recipient_type)
VALUES
    -- Message 1: To [email protected]
    ('30000001-0000-0000-0000-000000000001', '40000004-0000-0000-0000-000000000004', 'TO'),

    -- Message 2: To [email protected]
    ('30000002-0000-0000-0000-000000000002', '40000002-0000-0000-0000-000000000002', 'TO'),

    -- Message 3: To [email protected]
    ('30000003-0000-0000-0000-000000000003', '40000004-0000-0000-0000-000000000004', 'TO'),

    -- Message 4: To [email protected]
    ('30000004-0000-0000-0000-000000000004', '40000001-0000-0000-0000-000000000001', 'TO'),

    -- Message 5: To [email protected]
    ('30000005-0000-0000-0000-000000000005', '40000004-0000-0000-0000-000000000004', 'TO'),

    -- Message 6: To [email protected]
    ('30000006-0000-0000-0000-000000000006', '40000001-0000-0000-0000-000000000001', 'TO'),

    -- Message 7: To [email protected], CC [email protected]
    ('30000007-0000-0000-0000-000000000007', '40000004-0000-0000-0000-000000000004', 'TO'),
    ('30000007-0000-0000-0000-000000000007', '40000003-0000-0000-0000-000000000003', 'CC'),

    -- Message 8: To [email protected]
    ('30000008-0000-0000-0000-000000000008', '40000004-0000-0000-0000-000000000004', 'TO'),

    -- Message 9: To [email protected]
    ('30000009-0000-0000-0000-000000000009', '40000001-0000-0000-0000-000000000001', 'TO'),

    -- Message 10: To [email protected]
    ('30000010-0000-0000-0000-000000000010', '40000004-0000-0000-0000-000000000004', 'TO'),

    -- Message 11: To [email protected]
    ('30000011-0000-0000-0000-000000000011', '40000003-0000-0000-0000-000000000003', 'TO');

-- Insert message-label relationships
INSERT INTO email_message_labels (message_id, label_id)
VALUES
    -- Message 1: INBOX
    ('30000001-0000-0000-0000-000000000001', '10000001-0000-0000-0000-000000000001'),

    -- Message 2: SENT
    ('30000002-0000-0000-0000-000000000002', '10000003-0000-0000-0000-000000000003'),

    -- Message 3: DRAFT
    ('30000003-0000-0000-0000-000000000003', '10000005-0000-0000-0000-000000000005'),

    -- Message 4: STARRED, INBOX
    ('30000004-0000-0000-0000-000000000004', '10000004-0000-0000-0000-000000000004'),
    ('30000004-0000-0000-0000-000000000004', '10000001-0000-0000-0000-000000000001'),

    -- Message 5: IMPORTANT, INBOX
    ('30000005-0000-0000-0000-000000000005', '10000002-0000-0000-0000-000000000002'),
    ('30000005-0000-0000-0000-000000000005', '10000001-0000-0000-0000-000000000001'),

    -- Message 6: Work (user label)
    ('30000006-0000-0000-0000-000000000006', '10000007-0000-0000-0000-000000000007'),

    -- Message 7: INBOX
    ('30000007-0000-0000-0000-000000000007', '10000001-0000-0000-0000-000000000001'),

    -- Importance labels:
    -- Message 1 (thread 1): CATEGORY_PERSONAL (priority label → important)
    ('30000001-0000-0000-0000-000000000001', '10000008-0000-0000-0000-000000000008'),
    -- Message 6 (thread 6): CATEGORY_UPDATES (depriority label, no priority → NOT important)
    ('30000006-0000-0000-0000-000000000006', '10000009-0000-0000-0000-000000000009'),
    -- Message 7 (thread 7): CATEGORY_PROMOTIONS (depriority label, no priority → NOT important)
    ('30000007-0000-0000-0000-000000000007', '10000010-0000-0000-0000-000000000010'),
    -- Message 8 (thread 8): DRAFT + CATEGORY_UPDATES (draft with depriority label → important because DRAFT is priority)
    ('30000008-0000-0000-0000-000000000008', '10000005-0000-0000-0000-000000000005'),
    ('30000008-0000-0000-0000-000000000008', '10000009-0000-0000-0000-000000000009'),
    -- Message 9 (thread 9): DRAFT + TRASH (trashed draft → excluded from importance=true)
    ('30000009-0000-0000-0000-000000000009', '10000005-0000-0000-0000-000000000005'),
    ('30000009-0000-0000-0000-000000000009', '10000006-0000-0000-0000-000000000006'),
    -- Message 10 (thread 10): STARRED + TRASH (trashed starred → excluded from starred view)
    ('30000010-0000-0000-0000-000000000010', '10000004-0000-0000-0000-000000000004'),
    ('30000010-0000-0000-0000-000000000010', '10000006-0000-0000-0000-000000000006'),
    -- Message 11 (thread 11): Work + TRASH (trashed user label → excluded from user label view)
    ('30000011-0000-0000-0000-000000000011', '10000007-0000-0000-0000-000000000007'),
    ('30000011-0000-0000-0000-000000000011', '10000006-0000-0000-0000-000000000006');