arp/init_prod.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.

-- Enable foreign keys
PRAGMA foreign_keys = ON;

-- Permissions table (no created_at/updated_at in model)
CREATE TABLE IF NOT EXISTS permissions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    code TEXT NOT NULL UNIQUE,
    description TEXT
);

-- Roles table (no created_at/updated_at in model)
CREATE TABLE IF NOT EXISTS roles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    description TEXT
);

-- Role-Permission join table
CREATE TABLE IF NOT EXISTS role_permissions (
    role_id INTEGER NOT NULL,
    permission_id INTEGER NOT NULL,
    PRIMARY KEY (role_id, permission_id)
);

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    created_at DATETIME,
    updated_at DATETIME
);

-- User-Role join table
CREATE TABLE IF NOT EXISTS user_roles (
    user_id INTEGER NOT NULL,
    role_id INTEGER NOT NULL,
    PRIMARY KEY (user_id, role_id)
);

-- Task Statuses table
CREATE TABLE IF NOT EXISTS task_statuses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    code TEXT NOT NULL UNIQUE,
    label TEXT,
    created_at DATETIME,
    updated_at DATETIME
);

-- Services table
CREATE TABLE IF NOT EXISTS services (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    created_by_id INTEGER,
    created_at DATETIME,
    updated_at DATETIME
);

-- Service Participants join table
CREATE TABLE IF NOT EXISTS service_participants (
    service_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    PRIMARY KEY (service_id, user_id)
);

-- Tasks table
CREATE TABLE IF NOT EXISTS tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    service_id INTEGER,
    created_by_id INTEGER NOT NULL,
    updated_by_id INTEGER NOT NULL,
    assignee_id INTEGER,
    status_id INTEGER,
    due_date DATETIME,
    priority TEXT,
    created_at DATETIME,
    updated_at DATETIME
);

-- Notes table
CREATE TABLE IF NOT EXISTS notes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    user_id INTEGER,
    service_id INTEGER,
    created_at DATETIME,
    updated_at DATETIME
);

-- Channels table
CREATE TABLE IF NOT EXISTS channels (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    created_at DATETIME,
    updated_at DATETIME
);

-- Conversation Participants join table
CREATE TABLE IF NOT EXISTS conversation_participants (
    channel_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    PRIMARY KEY (channel_id, user_id)
);

-- Messages table
CREATE TABLE IF NOT EXISTS messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sender_id INTEGER NOT NULL,
    content TEXT NOT NULL,
    sent_at DATETIME,
    created_at DATETIME,
    updated_at DATETIME
);

-- Workflow Templates table
CREATE TABLE IF NOT EXISTS workflow_templates (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    description TEXT,
    definition TEXT NOT NULL,
    is_active INTEGER DEFAULT 1,
    created_by_id INTEGER,
    created_at DATETIME,
    updated_at DATETIME
);

-- Workflow Instances table
CREATE TABLE IF NOT EXISTS workflow_instances (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    workflow_template_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    context TEXT,
    service_id INTEGER,
    created_at DATETIME,
    updated_at DATETIME,
    completed_at DATETIME
);

-- Workflow Nodes table
CREATE TABLE IF NOT EXISTS workflow_nodes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    workflow_instance_id INTEGER NOT NULL,
    node_key TEXT NOT NULL,
    node_type TEXT NOT NULL,
    status TEXT NOT NULL,
    task_id INTEGER,
    retry_count INTEGER DEFAULT 0,
    input_data TEXT,
    output_data TEXT,
    created_at DATETIME,
    updated_at DATETIME,
    started_at DATETIME,
    completed_at DATETIME
);

-- Workflow Edges table
CREATE TABLE IF NOT EXISTS workflow_edges (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    workflow_instance_id INTEGER NOT NULL,
    from_node_id INTEGER NOT NULL,
    to_node_id INTEGER NOT NULL,
    condition TEXT
);

-- ============================================
-- INSERT STATEMENTS
-- ============================================

-- Permissions
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'),
  (37, 'workflow:create', 'Create workflow templates'),
  (38, 'workflow:start', 'Start workflow instances'),
  (39, 'workflow:manage', 'Manage workflow templates'),
  (40, 'workflow:intervene', 'Manual intervention for failed workflow nodes'),
  (41, 'workflow:view', 'View workflows and instances');

-- Roles
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:*

-- Manager role workflow permissions (idempotent for existing databases)
INSERT OR IGNORE INTO role_permissions (role_id, permission_id) VALUES
  (2, 37), (2, 38), (2, 39), (2, 40), (2, 41); -- workflow:*

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

-- User role workflow permissions (idempotent for existing databases)
INSERT OR IGNORE INTO role_permissions (role_id, permission_id) VALUES
  (3, 38), (3, 41); -- workflow:start, workflow:view

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