arp/graph/testutil/init_tests.sql

-- ARP Initial Data Bootstrap Script
-- Run this script to set up initial permissions, roles, and an admin user
--
-- Note: The password hash below is for "secret123" using bcrypt.

-- Permissions (no created_at/updated_at in model)
INSERT INTO permissions (id, code, description) VALUES 
  (1, 'user:create', 'Create users'),
  (2, 'user:read', 'Read users'),
  (3, 'user:update', 'Update users'),
  (4, 'user:delete', 'Delete users'),
  (5, 'role:create', 'Create roles'),
  (6, 'role:read', 'Read roles'),
  (7, 'role:update', 'Update roles'),
  (8, 'role:delete', 'Delete roles'),
  (9, 'permission:create', 'Create permissions'),
  (10, 'permission:read', 'Read permissions'),
  (11, 'permission:update', 'Update permissions'),
  (12, 'permission:delete', 'Delete permissions'),
  (13, 'service:create', 'Create services'),
  (14, 'service:read', 'Read services'),
  (15, 'service:update', 'Update services'),
  (16, 'service:delete', 'Delete services'),
  (17, 'task:create', 'Create tasks'),
  (18, 'task:read', 'Read tasks'),
  (19, 'task:update', 'Update tasks'),
  (20, 'task:delete', 'Delete tasks'),
  (21, 'note:create', 'Create notes'),
  (22, 'note:read', 'Read notes'),
  (23, 'note:update', 'Update notes'),
  (24, 'note:delete', 'Delete notes'),
  (25, 'channel:create', 'Create channels'),
  (26, 'channel:read', 'Read channels'),
  (27, 'channel:update', 'Update channels'),
  (28, 'channel:delete', 'Delete channels'),
  (29, 'message:create', 'Create messages'),
  (30, 'message:read', 'Read messages'),
  (31, 'message:update', 'Update messages'),
  (32, 'message:delete', 'Delete messages'),
  (33, 'taskstatus:create', 'Create task statuses'),
  (34, 'taskstatus:read', 'Read task statuses'),
  (35, 'taskstatus:update', 'Update task statuses'),
  (36, 'taskstatus:delete', 'Delete task statuses');

-- Roles (no created_at/updated_at in model)
INSERT INTO roles (id, name, description) VALUES 
  (1, 'admin', 'Administrator with full access'),
  (2, 'manager', 'Service manager with task management'),
  (3, 'user', 'Regular user with limited access');

-- Role-Permission associations (admin gets all permissions)
INSERT INTO role_permissions (role_id, permission_id) 
SELECT 1, id FROM permissions;

-- Manager role permissions (service, task, note operations)
INSERT INTO role_permissions (role_id, permission_id) VALUES
  (2, 13), (2, 14), (2, 15), (2, 16), -- service:*
  (2, 17), (2, 18), (2, 19), (2, 20), -- task:*
  (2, 21), (2, 22), (2, 23), (2, 24), -- note:*
  (2, 25), (2, 26), (2, 27), (2, 28), -- channel:*
  (2, 29), (2, 30), (2, 31), (2, 32), -- message:*
  (2, 33), (2, 34), (2, 35), (2, 36); -- taskstatus:*

-- User role permissions (read-only + create notes/messages)
INSERT INTO role_permissions (role_id, permission_id) VALUES
  (3, 2), (3, 6), (3, 10), (3, 14), (3, 18), (3, 22), (3, 26), (3, 30), (3, 34), -- read permissions
  (3, 21), (3, 29); -- create notes and messages

-- Admin user (password: secret123)
-- bcrypt hash generated with cost 10
INSERT INTO users (id, email, password, created_at, updated_at) VALUES 
  (1, 'admin@example.com', '$2a$10$9CNePaChncemsl8ZgMFDfeFm.Rl1K1l8rurgZxVx7C6sbv5tojUDC', datetime('now'), datetime('now'));

-- Associate admin user with admin role
INSERT INTO user_roles (user_id, role_id) VALUES (1, 1);

-- Task Statuses (common workflow states)
INSERT INTO task_statuses (id, code, label, created_at, updated_at) VALUES 
  (1, 'open', 'Open', datetime('now'), datetime('now')),
  (2, 'in_progress', 'In Progress', datetime('now'), datetime('now')),
  (3, 'blocked', 'Blocked', datetime('now'), datetime('now')),
  (4, 'review', 'In Review', datetime('now'), datetime('now')),
  (5, 'done', 'Done', datetime('now'), datetime('now')),
  (6, 'cancelled', 'Cancelled', datetime('now'), datetime('now'));