-- Migration: Add per-page granular permissions
CREATE TABLE IF NOT EXISTS user_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    page VARCHAR(50) NOT NULL,
    can_view TINYINT(1) DEFAULT 0,
    can_add TINYINT(1) DEFAULT 0,
    can_edit TINYINT(1) DEFAULT 0,
    can_delete TINYINT(1) DEFAULT 0,
    UNIQUE KEY user_page (user_id, page),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Seed permissions for existing users based on their roles
-- Inbound Mail
INSERT IGNORE INTO user_permissions (user_id, page, can_view, can_add, can_edit, can_delete)
SELECT id, 'inbound', can_view, can_add, can_edit, can_delete FROM users;

-- Outbound Mail
INSERT IGNORE INTO user_permissions (user_id, page, can_view, can_add, can_edit, can_delete)
SELECT id, 'outbound', can_view, can_add, can_edit, can_delete FROM users;

-- Internal Mail
INSERT IGNORE INTO user_permissions (user_id, page, can_view, can_add, can_edit, can_delete)
SELECT id, 'internal', can_view, can_add, can_edit, can_delete FROM users;

-- Users (Only Admins)
INSERT IGNORE INTO user_permissions (user_id, page, can_view, can_add, can_edit, can_delete)
SELECT id, 'users', 
    IF(role = 'admin', 1, 0), 
    IF(role = 'admin', 1, 0), 
    IF(role = 'admin', 1, 0), 
    IF(role = 'admin', 1, 0) 
FROM users;

-- Settings (Only Admins)
INSERT IGNORE INTO user_permissions (user_id, page, can_view, can_add, can_edit, can_delete)
SELECT id, 'settings', 
    IF(role = 'admin', 1, 0), 
    IF(role = 'admin', 1, 0), 
    IF(role = 'admin', 1, 0), 
    IF(role = 'admin', 1, 0) 
FROM users;

-- Reports
INSERT IGNORE INTO user_permissions (user_id, page, can_view, can_add, can_edit, can_delete)
SELECT id, 'reports', 
    IF(role IN ('admin', 'clerk'), 1, 0), 
    0, 0, 0 
FROM users;