-- ============================================
-- Add Publishing Module and Permissions
-- Date: 2025-10-02
-- Purpose: Add unified publishing program module
-- ============================================

-- Add Publishing Program
INSERT INTO `programs` (`code`, `name`, `description`) VALUES
('program.publishing', 'Publishing Management', 'Unified content publishing to all platforms');

-- Add Publishing Permissions
INSERT INTO `permissions` (`program_id`, `action`)
SELECT id, 'read' FROM `programs` WHERE code = 'program.publishing'
UNION ALL
SELECT id, 'create' FROM `programs` WHERE code = 'program.publishing'
UNION ALL
SELECT id, 'update' FROM `programs` WHERE code = 'program.publishing'
UNION ALL
SELECT id, 'delete' FROM `programs` WHERE code = 'program.publishing'
UNION ALL
SELECT id, 'execute' FROM `programs` WHERE code = 'program.publishing';

-- Grant all publishing permissions to Admin role
INSERT INTO `role_permissions` (`role_id`, `permission_id`, `allow`)
SELECT
    (SELECT id FROM roles WHERE code = 'admin'),
    p.id,
    1
FROM `permissions` p
INNER JOIN `programs` pr ON p.program_id = pr.id
WHERE pr.code = 'program.publishing';

-- Grant read and execute permissions to Ops role
INSERT INTO `role_permissions` (`role_id`, `permission_id`, `allow`)
SELECT
    (SELECT id FROM roles WHERE code = 'ops'),
    p.id,
    1
FROM `permissions` p
INNER JOIN `programs` pr ON p.program_id = pr.id
WHERE pr.code = 'program.publishing'
AND p.action IN ('read', 'create', 'execute');

-- Grant read and create permissions to Editor role
INSERT INTO `role_permissions` (`role_id`, `permission_id`, `allow`)
SELECT
    (SELECT id FROM roles WHERE code = 'editor'),
    p.id,
    1
FROM `permissions` p
INNER JOIN `programs` pr ON p.program_id = pr.id
WHERE pr.code = 'program.publishing'
AND p.action IN ('read', 'create');

-- Grant read permission to Viewer role
INSERT INTO `role_permissions` (`role_id`, `permission_id`, `allow`)
SELECT
    (SELECT id FROM roles WHERE code = 'viewer'),
    p.id,
    1
FROM `permissions` p
INNER JOIN `programs` pr ON p.program_id = pr.id
WHERE pr.code = 'program.publishing'
AND p.action = 'read';
