arp/init_prod.sql

-- ARP Initial Data Bootstrap Script
-- Run this script to set up initial permissions, roles, and an admin user
-- 
-- Usage:
--   sqlite3 arp.db < init.sql
--
-- Note: The password hash below is for "secret123" using bcrypt.
-- You can generate a new hash with: 
-- go run -e 'package main; import ("fmt"; "golang.org/x/crypto/bcrypt"); func main() { h, _ := bcrypt.GenerateFromPassword([]byte("your-password"), 10); fmt.Println(string(h)) }'
-- or
-- python3 -c "import bcrypt; print(bcrypt.hashpw(b'your_password', bcrypt.gensalt()).decode())"

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

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

-- 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$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LJZdL17lhWy', 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'));