-- members
INSERT INTO members (email, first_name, last_name, password, address1, address2, city, state, zipcode, country, county, phone, title, bio, image_path, notes, created, updated_at) VALUES
('samrussell99@pm.me', 'Sam', 'Russell', '$argon2id$v=19$m=65536,t=3,p=4$n/8BaBisEnBaQNbkxzs1VA$dvvnupWNtB5w5qTBgEciDsNA6rOgXaEypcEK1A0ndLM', '1234 address NW 12th St', NULL, 'Austin', 'Texas', '12345', 'US', 'Austin County', '123-456-789', 'CEO', 'This is my bio', NULL, 'no notes', '2026-01-15 09:58:01.0072', '2026-01-15 09:58:01.0072'),
('freddyjkrueger@gmail.com', 'Freddy','Krueger',  '$argon2id$v=19$m=65536,t=3,p=4$ioAYDPtyUulykMrH9W7q9A$lG43cq6Dj3/n1+bJrkupWpB5Xro3UIQaVd9rjuJJ6nM', '1234 address NW 12th St', NULL, 'Austin', 'Texas', '12345', 'US', 'Austin County', '123-456-789', 'Villain', 'This is my bio', NULL, 'no notes', '2026-01-13 13:38:46.0810', '2026-01-13 13:38:46.0810'),
('harmysmarmy@gmail.com', 'Harmy','Smarmy', '$argon2id$v=19$m=65536,t=3,p=4$FAhGtCtqNAQ19tBYD73wXQ$0AM/khyBFFuX2mv0ieqtGfsXRgtEldWKFwyeV3BA3Xk', '1234 address NW 12th St', NULL, 'Austin', 'Texas', '12345', 'US', 'Austin County', '123-456-789', 'Associate', 'This is my bio', NULL, 'no notes', '2026-01-13 13:41:41.0722', '2026-01-13 13:41:41.0722'),
('matiascarulli@gmail.com', 'Matias', 'Carulli', '$argon2id$v=19$m=65536,t=3,p=4$U2FsdGVkc3M$08SY0x7DUPwTV12ZckHdNg', '1234 address NW 12th St', NULL, 'Miramar', 'Florida', '33029', 'US', 'Broward', '123-456-789', 'Developer', 'This is my bio', '/db/images/users/member-4/profile.png', 'no notes', '2026-03-15 13:41:41.0722', '2026-03-15 13:41:41.0722'),
('boulder@example.com', 'CU', 'Boulder', '$argon2id$v=19$m=65536,t=3,p=4$CQwOYXNwwsLBP1s/zcZNJg$OM/wwVP5U+QUnAEDKAjk5mpvujpOzpT0XkouDcmHT8E', '1234 address NW 12th St', NULL, 'Austin', 'Texas', '12345', 'US', 'Austin County', '123-456-789', 'Associate', 'This is my bio', NULL, 'no notes', '2026-03-26 01:03:18.803016+00', '2026-03-26 01:03:18.803016+00');


-- networks
INSERT INTO networks (name, logo, abbreviation, stripe_account_id, created) VALUES
('Captured Sun', 'cs.svg',      'cs',      'acct_1Sn6DwLpyskwAml9', '2026-01-10 09:58:01.0074'),
('Hyperia',      'hyperia.svg', 'hyperia', 'acct_1S4w0GHZemeF9CKR', '2026-01-10 09:58:01.0074');

-- apps
INSERT INTO apps (name) VALUES
('settings'),
('people'),
('calendar'),
('treasury'),
('politics'),
('files'),
('jobs'),
('tasks'),
('chat'),
('announcements');

INSERT INTO roles (network_id, name, is_default) VALUES
(1, 'admin', false),
(1, 'member', true),
(2, 'admin', false),
(2, 'member', true);

-- network_apps
INSERT INTO role_apps (role_id, app_id) VALUES
(1, 1),
(1, 2),
(1, 3),
(1, 4),
(1, 5),
(1, 6),
(1, 7),
(1, 8),
(1, 9),
(1, 10),
(3, 1),
(3, 2),
(3, 3),
(3, 4),
(3, 5),
(3, 6),
(3, 7),
(3, 8),
(3, 9),
(3, 10);

-- member_networks
INSERT INTO member_networks (member_id, network_id, created) VALUES
(1, 1, '2025-11-24 00:54:36.0784'),
(2, 1, '2026-01-13 13:14:28.0178'),
(3, 1, '2026-01-13 13:28:35.0701'),
(1, 2, '2026-01-13 13:28:35.0701'),
(4, 2, '2026-03-15 13:28:35.0701');

-- network_plans
INSERT INTO network_plans (id, network_id, stripe_price_id, name, price, description, active, created) VALUES
(1,	1,	'price_1T3uaxLpyskwAml9p0r0nh2h',	'Patron Membership',	200.00, 'Members 40+', true, '2026-03-29 22:14:45.414163'),
(2,	1,	'price_1T3uaQLpyskwAml9rZAKBcy0',	'Regular Membership',	100.00, 'Members 18-40', true, '2026-03-29 22:14:45.414163');

-- join form seed data
INSERT INTO org_1.join_form (fname, lname, email, phone, county, time) VALUES
('James',   'Mitchell', 'james.mitchell@gmail.com',  '512-555-0101', 'Comal',  '2025-12-16 23:11:31.0011'),
('Rachel',  'Torres',   'rachel.torres@yahoo.com',   '512-555-0102', 'Bexar',  '2025-12-19 19:23:12.0717'),
('David',   'Nguyen',   'david.nguyen@gmail.com',    '830-555-0103', 'Comal',  '2026-01-06 16:55:29.0288'),
('Emily',   'Sanders',  'emily.sanders@outlook.com', '210-555-0104', 'Hays',   '2026-01-07 17:14:01.0711');

-- contact form seed data
INSERT INTO org_1.contact_form (fname, lname, email, phone, county, message, time) VALUES
('Marcus',  'Webb',     'marcus.webb@gmail.com',     '512-555-0201', 'Comal',  'Interested in volunteering at upcoming events.',          '2025-12-29 13:20:28.0157'),
('Sandra',  'Holloway', 'sandra.holloway@gmail.com', '830-555-0202', 'Comal',  'Would love to connect with your organization.',           '2025-12-30 22:10:24.0971'),
('Robert',  'Finley',   'robert.finley@gmail.com',   '210-555-0203', 'Comal',  'Looking forward to getting more involved locally.',       '2026-01-10 21:23:51.0073'),
('Barbara', 'Crane',    'barbara.crane@outlook.com', '512-555-0204', 'Comal',  'Please reach out regarding the next meeting schedule.',   '2026-01-10 21:23:54.0841');

-- calendars table seed data
INSERT INTO events.calendars (network_id, owner_id, name, description, color) VALUES
(2, 1, 'Main Calendar', 'The main calendar for the network', '#9E1C29'),
(2, 1, 'Sub-Calendar', 'Sub-calendar for the network', '#3D6FAD'),
(2, 1, 'Sub-Calendar 2', 'Another sub-calendar for the network', '#2A8636');

-- events table seed data
INSERT INTO events.events (network_id, creator_id, title, description, time_start, time_end, location, all_day) VALUES
(2, 1, 'Client meeting', 'Meeting with big client for app deployment', '2026-04-01T17:30:00.000Z', '2026-04-01T19:00:00.000Z', 'Virtual', false),
(2, 1, 'Networking Event', 'Networking event for young professionals', '2026-04-04T04:00:00.000Z', '2026-04-06T04:00:00.000Z', 'GB Center', true),
(2, 1, 'App deployment party', 'Come celebrate the app deployment!', '2026-04-05T16:00:00.000Z', '2026-04-05T21:30:00.000Z', 'Captured Sun HQ', false),
(2, 4, 'Work on frontend changes', 'Reminder for work #1', '2026-04-02T15:00:00.000Z', '2026-04-02T19:00:00.000Z', null, false),
(2, 4, 'Day off', 'I dont have to work today', '2026-04-03T04:00:00.000Z', '2026-04-03T04:00:00.000Z', null, true),
(2, 4, 'Scrum Meeting - Main Team', 'Agile Week 32', '2026-03-31T03:00:00.000Z', '2026-03-31T06:15:00.000Z', 'Virtual', false),
(2, 1, 'Meeting with John Smiith', 'Lorem ipsum elorum', '2026-03-31T18:30:00.000Z', '2026-03-31T19:30:00.000Z', 'Virtual', false),
(2, 1, 'Meeting with Jane Doe', 'Lorem ipsum elorum', '2026-03-31T19:30:00.000Z', '2026-03-31T19:45:00.000Z', 'Virtual', false),
(2, 1, 'Meeting with president', 'Lorem ipsum elorum', '2026-03-31T19:45:00.000Z', '2026-03-31T21:15:00.000Z', 'Virtual', false),
(2, 1, 'Meeting with investors', 'Lorem ipsum elorum', '2026-03-31T21:30:00.000Z', '2026-03-31T23:00:00.000Z', 'Virtual', false),
(2, 1, 'Review Github changes', 'Review pushes from members to ensure all is well', '2026-04-03T04:00:00.000Z', '2026-04-03T04:00:00.000Z', null, true);

INSERT INTO events.event_calendars (event_id, calendar_id) VALUES
(1, 1),
(2, 2),
(3, 1),
(3, 3),
(4, 2),
(5, 2),
(6, 1),
(6, 2),
(7, 3),
(8, 2),
(8, 3),
(9, 3),
(10, 3),
(11, 3);

-- jobs table seed data
INSERT INTO public.jobs (network_id, creator_id, title, company, location, employment_type, experience_level, department, salary_number, salary_period, applicants, skills, description, created, updated_at) VALUES
(2, 1, 'Senior Frontend Engineer', 'Acme Corp', 'San Francisco, CA', 'full-time', 'senior', 'Engineering', 165000, 'year', 47, ARRAY['React', 'TypeScript', 'CSS', 'GraphQL', 'Node.js'], E'We''re looking for a Senior Frontend Engineer to join our growing product team. You''ll work closely with designers, backend engineers, and product managers to build fast, accessible, and beautiful web experiences.\n\nYou''ll have ownership over large features and be expected to make architectural decisions. We ship every week and care deeply about code quality and UX.', '2026-04-29 10:00:00', '2026-04-29 10:00:00'),
(2, 1, 'Product Designer', 'Blue River', 'New York, NY', 'full-time', 'mid', 'Design', 120000, 'year', 112, ARRAY['Figma', 'Design Systems', 'Prototyping', 'User Research'], E'Blue River is hiring a Product Designer to lead design across our core consumer product. You''ll partner with PMs and engineers to take features from zero to one, establish design patterns, and advocate for users in every decision.\n\nWe''re a small, fast-moving team and designers have a huge impact here.', '2026-04-26 09:00:00', '2026-04-26 09:00:00'),
(2, 1, 'Backend Engineer', 'Orbit Systems', 'Remote', 'contract', 'mid', 'Platform', 95, 'hour', 29, ARRAY['Go', 'PostgreSQL', 'Kubernetes', 'gRPC', 'Redis'], '6-month contract (potential to extend) for a backend engineer to help scale our platform infrastructure. You''ll be responsible for building and maintaining APIs used by millions of users, optimizing database performance, and improving system reliability.', '2026-04-30 14:00:00', '2026-04-30 14:00:00'),
(2, 1, 'Marketing Manager', 'Groundwork', 'Austin, TX', 'full-time', 'mid', 'Marketing', 98000, 'year', 88, ARRAY['SEO', 'Content Strategy', 'Analytics', 'Paid Acquisition', 'Email Marketing'], E'We need a Marketing Manager to own our top-of-funnel growth. You''ll manage content, paid channels, and email campaigns — and be the person who keeps the brand consistent across every touchpoint. You''ll report directly to our Head of Growth.', '2026-04-21 11:00:00', '2026-04-21 11:00:00'),
(2, 1, 'Data Analyst', 'Compass Data', 'Chicago, IL', 'full-time', 'entry', 'Analytics', 75000, 'year', 203, ARRAY['SQL', 'Python', 'Tableau', 'dbt', 'Excel'], 'A great entry-level opportunity for someone who loves data. You''ll work with our analytics team to build dashboards, run ad-hoc analysis, and help business teams make data-driven decisions. We''ll invest in your growth and give you plenty of mentorship.', '2026-04-28 08:30:00', '2026-04-28 08:30:00'),
(2, 1, 'iOS Engineer', 'Fieldwork', 'Seattle, WA', 'full-time', 'senior', 'Mobile', 175000, 'year', 34, ARRAY['Swift', 'SwiftUI', 'Combine', 'CoreData', 'Xcode'], E'Fieldwork is building next-generation tools for field service teams. Our iOS app is the most important surface we have — technicians use it every day on job sites. We need a senior iOS engineer who cares about performance, offline reliability, and a great UX.', '2026-04-24 13:00:00', '2026-04-24 13:00:00'),
(2, 1, 'Operations Coordinator', 'Maple & Co', 'Boston, MA', 'part-time', 'entry', 'Operations', 28, 'hour', 61, ARRAY['Project Management', 'Excel', 'Communication', 'Scheduling'], E'Part-time role (20 hrs/week) helping our operations team stay organized. You''ll coordinate schedules, manage vendor relationships, and help improve internal workflows. Ideal for someone who''s highly organized and excited to grow into a full-time ops role.', '2026-04-17 10:00:00', '2026-04-17 10:00:00'),
(2, 1, 'Machine Learning Intern', 'NeuralPath', 'Remote', 'internship', 'entry', 'AI Research', 8000, 'month', 394, ARRAY['Python', 'PyTorch', 'Linear Algebra', 'Git'], 'Summer internship (12 weeks) on our ML research team. You''ll work alongside researchers on real problems in NLP and recommendation systems. We expect you to ship something you''re proud of by the end of the summer. Strong preference for candidates who can start June 2.', '2026-05-01 09:00:00', '2026-05-01 09:00:00');

INSERT INTO permissions (key, app_id, description) VALUES
('events.add', 3, 'Can add events'),
('events.delete', 3, 'Can delete events'),
('events.edit', 3, 'Can edit events'),
('events.get', 3, 'Can view events'),
('jobs.add', 7, 'Can add jobs'),
('jobs.delete', 7, 'Can delete jobs'),
('jobs.edit', 7, 'Can edit jobs'),
('jobs.get', 7, 'Can view jobs'),
('announcements.add', 10, 'Can add announcements'),
('announcements.delete', 10, 'Can delete announcements'),
('announcements.edit', 10, 'Can edit announcements'),
('announcements.get', 10, 'Can view announcements'),
('role_apps.edit', 1, 'Can edit role apps'),
('roles.create', 1, 'Can create roles'),
('roles.delete', 1, 'Can delete roles'),
('role_notifications.edit', 1, 'Can edit role notifications'),
('chats.create', 9, 'Can create chats'),
('chats.edit', 9, 'Can edit chats'),
('chats.delete', 9, 'Can delete chats'),
('chats_message.send', 9, 'Can send messages'),
('chats_message.edit', 9, 'Can edit messages'),
('chats_message.delete', 9, 'Can delete messages');

INSERT INTO role_permissions (role_id, permission_key) VALUES
(1, 'events.get'), (1, 'jobs.get'), (1, 'announcements.get'), (1, 'events.add'), (1, 'events.delete'), (1, 'events.edit'), (1, 'jobs.add'), (1, 'jobs.delete'), (1, 'jobs.edit'), (1, 'announcements.add'), (1, 'announcements.delete'), (1, 'announcements.edit'), (1, 'role_apps.edit'), (1, 'roles.create'), (1, 'roles.delete'), (1, 'chats.create'), (1, 'chats.edit'), (1, 'chats.delete'), (1, 'chats_message.send'), (1, 'chats_message.edit'), (1, 'chats_message.delete'), (1, 'role_notifications.edit'),-- network 1 admin
(2, 'events.get'), (2, 'jobs.get'), (2, 'announcements.get'), (2, 'events.add'), (2, 'chats.create'), (2, 'chats_message.send'), -- network 1 user
(3, 'events.get'), (3, 'jobs.get'), (3, 'announcements.get'), (3, 'events.add'), (3, 'events.delete'), (3, 'events.edit'), (3, 'jobs.add'), (3, 'jobs.delete'), (3, 'jobs.edit'), (3, 'announcements.add'), (3, 'announcements.delete'), (3, 'announcements.edit'), (3, 'role_apps.edit'), (3, 'roles.create'), (3, 'roles.delete'), (3, 'chats.create'), (3, 'chats.edit'), (3, 'chats.delete'), (3, 'chats_message.send'), (3, 'chats_message.edit'), (3, 'chats_message.delete'), (3, 'role_notifications.edit'),-- network 2 admin
(4, 'events.get'), (4, 'jobs.get'), (4, 'announcements.get'), (4, 'events.add'), (4, 'chats.create'), (4, 'chats_message.send'); -- network 2 user

INSERT INTO member_roles (member_id, role_id, granted_by) VALUES
(1, 1, 1), (2, 2, 1), (3, 2, 1), -- network 1
(1, 3, 1), (4, 3, 1), (6, 3, 1); -- network 2

INSERT INTO announcements (network_id, creator_id, text, created, updated_at) VALUES
(2, 1, 'This is the first announcement', '2026-01-13 15:37:00.0000', '2026-01-13 15:37:00.0000'),
(2, 1, 'Here goes another announcement.', '2026-01-13 15:39:00.0000', '2026-01-13 15:39:00.0000'),
(2, 4, 'My first announcement!', '2026-01-14 15:41:00.0000', '2026-01-14 15:41:00.0000'),
(2, 1, 'Testing announcement.', '2026-02-17 12:30:00.0000', '2026-02-17 12:30:00.0000'),
(2, 4, 'Quick fire!', '2026-03-23 15:56:00.0000', '2026-03-23 15:56:00.0000'),
(2, 4, 'Quick fire!', '2026-03-23 15:56:30.0000', '2026-03-23 15:56:30.0000'),
(2, 4, 'Quick fire!', '2026-03-23 15:57:00.0000', '2026-03-23 15:57:00.0000'),
(2, 1, 'Trying another user.', '2026-02-05 15:56:30.0000', '2026-02-05 15:56:30.0000'),
(2, 1, 'One last announcement.', '2026-04-01 15:57:00.0000', '2026-01-13 15:57:00.0000');

INSERT INTO public.join_code (code, network_id) VALUES
('cs', 1),
('hyperia', 2);

-- Recurrence rules for 3 seed events (0=Sun, 1=Mon, 2=Tue, 3=Wed, 4=Thu, 5=Fri, 6=Sat)
INSERT INTO events.event_recurrence (frequency, interval, days_of_week, end_date, count) VALUES
('weekly', 1, '{2}',     null, null),  -- id=1: every Tuesday             → Scrum Meeting
('weekly', 2, '{3}',     null, 10  ),  -- id=2: every 2nd Wednesday x10   → Client Meeting
('weekly', 1, '{1,3,5}', null, null);  -- id=3: every Mon/Wed/Fri         → Review Github

UPDATE events.events SET recurrence_id = 1 WHERE id = 6;
UPDATE events.events SET recurrence_id = 2 WHERE id = 1;
UPDATE events.events SET recurrence_id = 3 WHERE id = 11;

ALTER TABLE public.files ALTER COLUMN type TYPE varchar(100);

-- chat seed members (Sarah McIntyre=6, Marcus Webb=7, Priya Anand=8, Jordan Kim=9)
INSERT INTO members (email, first_name, last_name, password, address1, address2, city, state, zipcode, country, county, phone, title, bio, image_path, notes, created, updated_at) VALUES
('sarah.mcintyre@example.com', 'Sarah',  'McIntyre', '$argon2id$v=19$m=65536,t=3,p=4$U2FsdGVkc3M$08SY0x7DUPwTV12ZckHdNg', '1234 Address St', NULL, 'Austin', 'Texas', '12345', 'US', 'Travis', '123-456-789', 'Designer',  'This is my bio', NULL, NULL, '2026-02-01 09:00:00+00', '2026-02-01 09:00:00+00'),
('marcus.webb@example.com',    'Marcus', 'Webb',      '$argon2id$v=19$m=65536,t=3,p=4$ioAYDPtyUulykMrH9W7q9A$lG43cq6Dj3/n1+bJrkupWpB5Xro3UIQaVd9rjuJJ6nM', '1234 Address St', NULL, 'Austin', 'Texas', '12345', 'US', 'Travis', '123-456-789', 'Engineer',  'This is my bio', NULL, NULL, '2026-02-01 09:00:00+00', '2026-02-01 09:00:00+00'),
('priya.anand@example.com',    'Priya',  'Anand',     '$argon2id$v=19$m=65536,t=3,p=4$ioAYDPtyUulykMrH9W7q9A$lG43cq6Dj3/n1+bJrkupWpB5Xro3UIQaVd9rjuJJ6nM', '1234 Address St', NULL, 'Austin', 'Texas', '12345', 'US', 'Travis', '123-456-789', 'PM',        'This is my bio', NULL, NULL, '2026-02-01 09:00:00+00', '2026-02-01 09:00:00+00'),
('jordan.kim@example.com',     'Jordan', 'Kim',       '$argon2id$v=19$m=65536,t=3,p=4$ioAYDPtyUulykMrH9W7q9A$lG43cq6Dj3/n1+bJrkupWpB5Xro3UIQaVd9rjuJJ6nM', '1234 Address St', NULL, 'Austin', 'Texas', '12345', 'US', 'Travis', '123-456-789', 'Designer',  'This is my bio', NULL, NULL, '2026-02-01 09:00:00+00', '2026-02-01 09:00:00+00');

INSERT INTO member_networks (member_id, network_id, created) VALUES
(6, 2, '2026-02-01 09:00:00+00'),
(7, 2, '2026-02-01 09:00:00+00'),
(8, 2, '2026-02-01 09:00:00+00'),
(9, 2, '2026-02-01 09:00:00+00');

INSERT INTO member_roles (member_id, role_id, granted_by) VALUES
(6, 4, 1),
(7, 4, 1),
(8, 4, 1),
(9, 4, 1);

-- chats (network 2 / Hyperia)
-- updated_at reflects the last message's sent_at for each chat
INSERT INTO chats.chats (network_id, creator_id, type, name, created, updated_at) VALUES
(2, 4, 'dm',           NULL,            '2026-04-01 09:00:00+00', '2026-05-01 11:42:00+00'),  -- 1: DM Matias↔Sarah
(2, 4, 'dm',           NULL,            '2026-04-01 09:00:00+00', '2026-05-01 10:30:00+00'),  -- 2: DM Matias↔Marcus
(2, 4, 'dm',           NULL,            '2026-04-01 09:00:00+00', '2026-04-30 18:00:00+00'),  -- 3: DM Matias↔Priya
(2, 4, 'group',        'Product Team',  '2026-04-01 09:00:00+00', '2026-05-01 11:51:00+00'),  -- 4
(2, 4, 'group',        'Design Review', '2026-04-01 09:00:00+00', '2026-04-30 10:00:00+00'),  -- 5
(2, 4, 'channel',      'general',       '2026-04-01 09:00:00+00', '2026-05-01 11:55:00+00'),  -- 6
(2, 4, 'channel',      'engineering',   '2026-04-01 09:00:00+00', '2026-05-01 09:00:00+00'),  -- 7
(2, 8, 'announcement', 'Announcements', '2026-04-01 09:00:00+00', '2026-05-01 00:00:00+00');  -- 8

-- chat_members
-- last_read_at for member 4 (Matias) is set to match the unread counts from the hardcoded data
INSERT INTO chats.chat_members (chat_id, member_id, last_read_at, joined_at) VALUES
-- chat 1: DM Sarah (unread=2 → Matias last read before the final 2 messages from Sarah)
(1, 4, '2026-05-01 07:12:00+00', '2026-04-01 09:00:00+00'),
(1, 6, NULL,                     '2026-04-01 09:00:00+00'),
-- chat 2: DM Marcus (unread=0)
(2, 4, '2026-05-01 10:30:00+00', '2026-04-01 09:00:00+00'),
(2, 7, NULL,                     '2026-04-01 09:00:00+00'),
-- chat 3: DM Priya (unread=0)
(3, 4, '2026-04-30 18:00:00+00', '2026-04-01 09:00:00+00'),
(3, 8, NULL,                     '2026-04-01 09:00:00+00'),
-- chat 4: Product Team (unread=5 → Matias last read after the 4th message)
(4, 4, '2026-05-01 04:30:00+00', '2026-04-01 09:00:00+00'),
(4, 6, NULL,                     '2026-04-01 09:00:00+00'),
(4, 7, NULL,                     '2026-04-01 09:00:00+00'),
(4, 8, NULL,                     '2026-04-01 09:00:00+00'),
-- chat 5: Design Review (unread=0)
(5, 4, '2026-04-30 10:00:00+00', '2026-04-01 09:00:00+00'),
(5, 6, NULL,                     '2026-04-01 09:00:00+00'),
(5, 9, NULL,                     '2026-04-01 09:00:00+00'),
-- chat 6: #general (unread=11 → all unread, NULL)
(6, 1, NULL, '2026-04-01 09:00:00+00'),
(6, 4, NULL, '2026-04-01 09:00:00+00'),
(6, 6, NULL, '2026-04-01 09:00:00+00'),
(6, 7, NULL, '2026-04-01 09:00:00+00'),
(6, 8, NULL, '2026-04-01 09:00:00+00'),
(6, 9, NULL, '2026-04-01 09:00:00+00'),
-- chat 7: #engineering (unread=0)
(7, 1, NULL,                     '2026-04-01 09:00:00+00'),
(7, 4, '2026-05-01 09:00:00+00', '2026-04-01 09:00:00+00'),
(7, 7, NULL,                     '2026-04-01 09:00:00+00'),
-- chat 8: Announcements (unread=1 → Matias last read after the 2nd message)
(8, 1, NULL,                     '2026-04-01 09:00:00+00'),
(8, 4, '2026-04-29 12:00:00+00', '2026-04-01 09:00:00+00'),
(8, 6, NULL,                     '2026-04-01 09:00:00+00'),
(8, 7, NULL,                     '2026-04-01 09:00:00+00'),
(8, 8, NULL,                     '2026-04-01 09:00:00+00'),
(8, 9, NULL,                     '2026-04-01 09:00:00+00');

-- messages
-- Timestamps computed from "now" = 2026-05-01 12:00:00+00, matching ago(h) from the hardcoded constructor
-- sender_id mapping: Matias=4, Sarah=6, Marcus=7, Priya=8, Jordan=9
INSERT INTO chats.messages (chat_id, sender_id, text, sent_at, updated_at) VALUES
-- chat 1: DM Matias↔Sarah
(1, 4, 'Hey Sarah, did you see the new design mockups?',              '2026-04-30 12:00:00+00', '2026-04-30 12:00:00+00'),
(1, 6, 'Just looked — they''re really clean. I love the new sidebar.','2026-04-30 12:30:00+00', '2026-04-30 12:30:00+00'),
(1, 4, 'Agreed. Alex did a great job.',                               '2026-04-30 12:36:00+00', '2026-04-30 12:36:00+00'),
(1, 6, 'Are we going to ship this week or wait for the backend?',     '2026-05-01 07:00:00+00', '2026-05-01 07:00:00+00'),
(1, 4, 'Let''s aim for Thursday. I''ll sync with Marcus.',            '2026-05-01 07:12:00+00', '2026-05-01 07:12:00+00'),
(1, 6, 'Sounds good 👍',                                              '2026-05-01 07:18:00+00', '2026-05-01 07:18:00+00'),
(1, 6, 'Can you review the PR when you get a chance?',                '2026-05-01 11:42:00+00', '2026-05-01 11:42:00+00'),
-- chat 2: DM Matias↔Marcus
(2, 7, 'Hey, the API endpoint is returning 500s on staging.',         '2026-05-01 09:00:00+00', '2026-05-01 09:00:00+00'),
(2, 4, 'Oh no — is it the auth middleware again?',                    '2026-05-01 09:06:00+00', '2026-05-01 09:06:00+00'),
(2, 7, 'Yep. Same issue as last week.',                               '2026-05-01 09:12:00+00', '2026-05-01 09:12:00+00'),
(2, 4, 'I''ll patch it now. Give me 20 mins.',                        '2026-05-01 09:15:00+00', '2026-05-01 09:15:00+00'),
(2, 7, 'Thanks, no rush.',                                            '2026-05-01 09:18:00+00', '2026-05-01 09:18:00+00'),
(2, 4, 'Fixed. Can you redeploy and check?',                          '2026-05-01 09:48:00+00', '2026-05-01 09:48:00+00'),
(2, 7, 'All green 🎉 Thanks!',                                        '2026-05-01 09:54:00+00', '2026-05-01 09:54:00+00'),
(2, 4, 'I''ll send over the specs by EOD',                            '2026-05-01 10:30:00+00', '2026-05-01 10:30:00+00'),
-- chat 3: DM Matias↔Priya
(3, 8, 'Quick question — what''s the launch date for v2?',            '2026-04-30 16:00:00+00', '2026-04-30 16:00:00+00'),
(3, 4, 'Still TBD, but we''re targeting end of May.',                 '2026-04-30 16:12:00+00', '2026-04-30 16:12:00+00'),
(3, 8, 'Got it. I''ll update the roadmap doc.',                       '2026-04-30 16:30:00+00', '2026-04-30 16:30:00+00'),
(3, 4, 'Perfect, thanks Priya.',                                      '2026-04-30 16:36:00+00', '2026-04-30 16:36:00+00'),
(3, 8, 'See you at the standup!',                                     '2026-04-30 18:00:00+00', '2026-04-30 18:00:00+00'),
-- chat 4: Product Team
(4, 7, 'Morning everyone! API docs are updated.',                     '2026-05-01 04:00:00+00', '2026-05-01 04:00:00+00'),
(4, 8, 'Nice work Marcus 🙌',                                         '2026-05-01 04:06:00+00', '2026-05-01 04:06:00+00'),
(4, 4, 'I''ll start on the integration tests today.',                 '2026-05-01 04:12:00+00', '2026-05-01 04:12:00+00'),
(4, 6, 'Great. I''m finishing up the onboarding screens.',            '2026-05-01 04:30:00+00', '2026-05-01 04:30:00+00'),
(4, 8, 'Can we do a quick sync at 2pm?',                              '2026-05-01 10:00:00+00', '2026-05-01 10:00:00+00'),
(4, 4, 'Works for me.',                                               '2026-05-01 10:03:00+00', '2026-05-01 10:03:00+00'),
(4, 7, 'Same',                                                        '2026-05-01 10:06:00+00', '2026-05-01 10:06:00+00'),
(4, 6, 'I''ll send the invite.',                                      '2026-05-01 10:09:00+00', '2026-05-01 10:09:00+00'),
(4, 6, 'I''ve updated the Figma file with the new flows',             '2026-05-01 11:51:00+00', '2026-05-01 11:51:00+00'),
-- chat 5: Design Review
(5, 9, 'Hey, sharing the first round of designs for the settings page.', '2026-04-30 06:00:00+00', '2026-04-30 06:00:00+00'),
(5, 6, 'These look great! Love the card layout.',                     '2026-04-30 06:30:00+00', '2026-04-30 06:30:00+00'),
(5, 4, 'Agreed. One thought — the spacing on the form feels a bit tight.', '2026-04-30 07:00:00+00', '2026-04-30 07:00:00+00'),
(5, 9, 'Good call. I''ll loosen it up.',                              '2026-04-30 07:12:00+00', '2026-04-30 07:12:00+00'),
(5, 6, 'Also maybe we increase the font size slightly?',              '2026-04-30 08:00:00+00', '2026-04-30 08:00:00+00'),
(5, 9, 'The contrast on mobile looks off — can we bump it?',          '2026-04-30 10:00:00+00', '2026-04-30 10:00:00+00'),
-- chat 6: #general
(6, 8, 'Good morning team! Reminder: all-hands is Thursday at 10am.', '2026-05-01 03:00:00+00', '2026-05-01 03:00:00+00'),
(6, 6, 'Thanks for the reminder!',                                    '2026-05-01 03:06:00+00', '2026-05-01 03:06:00+00'),
(6, 9, 'Will there be a recording for those in other time zones?',    '2026-05-01 03:18:00+00', '2026-05-01 03:18:00+00'),
(6, 8, 'Yes — I''ll post the link in #announcements after.',          '2026-05-01 03:24:00+00', '2026-05-01 03:24:00+00'),
(6, 4, 'Thanks Priya 🙏',                                             '2026-05-01 03:30:00+00', '2026-05-01 03:30:00+00'),
(6, 7, 'Staging is back up btw, had a brief outage this morning.',    '2026-05-01 08:00:00+00', '2026-05-01 08:00:00+00'),
(6, 6, 'Oh I didn''t even notice, nice quick fix!',                   '2026-05-01 08:12:00+00', '2026-05-01 08:12:00+00'),
(6, 7, 'Just pushed the hotfix to production',                        '2026-05-01 11:55:00+00', '2026-05-01 11:55:00+00'),
-- chat 7: #engineering
(7, 7, 'Heads up: I''m updating the CI pipeline today. Builds might be slow for a bit.', '2026-05-01 07:00:00+00', '2026-05-01 07:00:00+00'),
(7, 4, 'Noted, thanks for the warning.',                              '2026-05-01 07:06:00+00', '2026-05-01 07:06:00+00'),
(7, 7, 'Back to normal now.',                                         '2026-05-01 08:00:00+00', '2026-05-01 08:00:00+00'),
(7, 4, 'PR is up: #247 — adds rate limiting to the auth routes',      '2026-05-01 09:00:00+00', '2026-05-01 09:00:00+00'),
-- chat 8: Announcements
(8, 8, 'Welcome to the team, Jordan! 🎉 Jordan joins us as a Product Designer.',                          '2026-04-28 12:00:00+00', '2026-04-28 12:00:00+00'),
(8, 8, 'Reminder: expense reports for March are due this Friday.',                                        '2026-04-29 12:00:00+00', '2026-04-29 12:00:00+00'),
(8, 8, 'Q2 planning kick-off is next Monday at 9am. Please come prepared with your team''s priorities.',  '2026-05-01 00:00:00+00', '2026-05-01 00:00:00+00');

-- member_apps
INSERT INTO member_apps (member_id, app_id) VALUES
(1, 1), (4, 1), (6, 1),
(1, 2), (4, 2), (6, 2),
(1, 3), (4, 3), (6, 3),
(1, 4), (4, 4), (6, 4),
(1, 5), (4, 5), (6, 5),
(1, 6), (4, 6), (6, 6),
(1, 7), (4, 7), (6, 7),
(1, 8), (4, 8), (6, 8),
(1, 9), (4, 10), (6, 9);