

-- =============================================================================
-- ROLES TABLE
-- =============================================================================
CREATE TABLE IF NOT EXISTS roles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    description TEXT,
    is_system BOOLEAN NOT NULL DEFAULT 0, -- System roles cannot be deleted
    is_deleted BOOLEAN NOT NULL DEFAULT 0,
    created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
    updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
);

CREATE INDEX IF NOT EXISTS idx_roles_name ON roles(name) WHERE is_deleted = 0;

-- =============================================================================
-- USER_ROLES TABLE (Many-to-Many relationship)
-- =============================================================================
CREATE TABLE IF NOT EXISTS user_roles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    role_id INTEGER NOT NULL,
    assigned_by INTEGER, -- User ID who assigned this role
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE SET NULL,
    UNIQUE(user_id, role_id)
);

CREATE INDEX IF NOT EXISTS idx_user_roles_user ON user_roles(user_id);
CREATE INDEX IF NOT EXISTS idx_user_roles_role ON user_roles(role_id);

-- =============================================================================
-- RESOURCES TABLE
-- =============================================================================
-- Resources represent API endpoints or groups of endpoints
CREATE TABLE IF NOT EXISTS resources (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE, -- e.g., "node_subject", "admin_system", "user"
    description TEXT,
    is_system BOOLEAN NOT NULL DEFAULT 0,
    created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
);

CREATE INDEX IF NOT EXISTS idx_resources_name ON resources(name);

-- =============================================================================
-- ACTIONS TABLE
-- =============================================================================
-- Actions represent operations on resources (aligned with HTTP verbs)
CREATE TABLE IF NOT EXISTS actions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE, -- e.g., "get", "post", "put", "patch", "delete", "all"
    description TEXT,
    is_system BOOLEAN NOT NULL DEFAULT 0,
    created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
);

CREATE INDEX IF NOT EXISTS idx_actions_name ON actions(name);

-- =============================================================================
-- ROLE_PERMISSIONS TABLE
-- =============================================================================
-- Defines what actions roles can perform on resources
CREATE TABLE IF NOT EXISTS role_permissions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    role_id INTEGER NOT NULL,
    resource_id INTEGER NOT NULL,
    action_id INTEGER NOT NULL,
    allowed BOOLEAN NOT NULL DEFAULT 1, -- TRUE = allow, FALSE = deny
    is_system BOOLEAN NOT NULL DEFAULT 0, -- System permissions cannot be modified/deleted
    created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (resource_id) REFERENCES resources(id) ON DELETE CASCADE,
    FOREIGN KEY (action_id) REFERENCES actions(id) ON DELETE CASCADE,
    UNIQUE(role_id, resource_id, action_id)
);

CREATE INDEX IF NOT EXISTS idx_role_permissions_role ON role_permissions(role_id);
CREATE INDEX IF NOT EXISTS idx_role_permissions_resource ON role_permissions(resource_id);
CREATE INDEX IF NOT EXISTS idx_role_permissions_action ON role_permissions(action_id);

-- =============================================================================
-- USER_PERMISSIONS TABLE
-- =============================================================================
-- User-specific permission overrides (takes precedence over role permissions)
CREATE TABLE IF NOT EXISTS user_permissions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    resource_id INTEGER NOT NULL,
    action_id INTEGER NOT NULL,
    allowed BOOLEAN NOT NULL, -- TRUE = allow, FALSE = deny
    created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
    granted_by INTEGER, -- User ID who granted this permission
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (resource_id) REFERENCES resources(id) ON DELETE CASCADE,
    FOREIGN KEY (action_id) REFERENCES actions(id) ON DELETE CASCADE,
    FOREIGN KEY (granted_by) REFERENCES users(id) ON DELETE SET NULL,
    UNIQUE(user_id, resource_id, action_id)
);

CREATE INDEX IF NOT EXISTS idx_user_permissions_user ON user_permissions(user_id);
CREATE INDEX IF NOT EXISTS idx_user_permissions_resource ON user_permissions(resource_id);
CREATE INDEX IF NOT EXISTS idx_user_permissions_action ON user_permissions(action_id);

-- =============================================================================
-- INSERT SYSTEM RESOURCES
-- =============================================================================
INSERT OR IGNORE INTO resources (name, description, is_system) VALUES
    ('user', 'User self-service endpoints', 1),
    ('admin_system', 'Administrative system endpoints', 1),
    ('admin_api_key', 'Administrative API key endpoints', 1),
    ('admin_roles', 'Administrative role and permission endpoints', 1),
    ('admin_users', 'Administrative user management endpoints', 1),
    ('node_system', 'Node information endpoints', 1),
    ('node_subject', 'Ledger subject and governance endpoints', 1),
    ('node_request', 'Ledger request submission endpoints', 1),
    ('user_api_key', 'User self-service API key endpoints', 1),
    ('node_management', 'Node configuration and metrics endpoints', 1);

-- =============================================================================
-- INSERT SYSTEM ACTIONS
-- =============================================================================
INSERT OR IGNORE INTO actions (name, description, is_system) VALUES
    ('get', 'Read/view resources', 1),
    ('post', 'Create resources or trigger operations', 1),
    ('put', 'Replace or update resources', 1),
    ('patch', 'Partial update or custom action', 1),
    ('delete', 'Delete resources', 1),
    ('all', 'Full access to the resource', 1);

-- =============================================================================
-- INSERT SYSTEM ROLES
-- =============================================================================
INSERT OR IGNORE INTO roles (name, description, is_system) VALUES
    ('superadmin', 'Full system access with all privileges', 1),
    ('admin', 'Administrative access to users, roles, and API keys', 1),
    ('sender', 'Limited to sending event requests', 1),
    ('manager', 'Business manager with operational control', 1),
    ('data', 'Read-only access to business data', 1);
END;

-- Roles table trigger
CREATE TRIGGER IF NOT EXISTS update_roles_timestamp
AFTER UPDATE ON roles
FOR EACH ROW
BEGIN
    UPDATE roles SET updated_at = strftime('%s', 'now') WHERE id = NEW.id;
END;
END;
END;
END;

-- =============================================================================
-- API KEY LIMIT ENFORCEMENT
-- =============================================================================
-- SECURITY NOTE: The max_keys_per_user limit is enforced at application level
-- (see database_apikeys.rs:85-99). A database-level trigger was considered but
-- removed because:
-- 1. It would require hardcoding the limit (20) or using a config table
-- 2. The race condition window is extremely small and difficult to exploit
-- 3. Application-level check is more flexible and respects config changes
-- 4. SQLite transactions already provide isolation for the check+insert operation
--
-- The application performs the check within a transaction, making the race
-- condition very unlikely. If stricter enforcement is needed in the future,
-- consider using:
-- - SERIALIZABLE transaction isolation mode
-- - A database-level config table with trigger reading from it
-- - Row-level locking (though SQLite uses database-level locks)

-- =============================================================================
-- TRIGGER: AUTO-GRANT SUPERADMIN ON NEW RESOURCES
-- =============================================================================
-- Whenever a new resource is inserted, superadmin automatically gets 'all'
-- permission on it. This ensures future migrations that add resources never
-- need to remember to update superadmin permissions manually.
-- Note: the WHERE clause guards against firing during initial setup before
-- roles/actions tables are populated.
CREATE TRIGGER IF NOT EXISTS grant_superadmin_on_new_resource
AFTER INSERT ON resources
BEGIN
    INSERT OR IGNORE INTO role_permissions (role_id, resource_id, action_id, allowed, is_system)
    SELECT
        r.id,
        NEW.id,
        a.id,
        1,
        1
    FROM roles r, actions a
    WHERE r.name = 'superadmin'
      AND a.name = 'all';
END;

-- =============================================================================
-- END OF MIGRATION
-- =============================================================================
