-- ============================================================
-- RBAC Migration: roles, role_permissions, role_id on users
-- Roles are GLOBAL (not company-scoped).
-- Company access is handled separately via user_companies table.
-- ============================================================

-- 1. Roles table (global, is_system = cannot be deleted)
CREATE TABLE IF NOT EXISTS `roles` (
    `id`          INT(11)      NOT NULL AUTO_INCREMENT,
    `name`        VARCHAR(80)  NOT NULL,
    `description` VARCHAR(255) DEFAULT NULL,
    `is_system`   TINYINT(1)   NOT NULL DEFAULT 0,
    `created_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_role_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Role permissions table
CREATE TABLE IF NOT EXISTS `role_permissions` (
    `id`           INT(11)     NOT NULL AUTO_INCREMENT,
    `role_id`      INT(11)     NOT NULL,
    `module`       VARCHAR(50) NOT NULL,
    `can_view`     TINYINT(1)  NOT NULL DEFAULT 0,
    `can_create`   TINYINT(1)  NOT NULL DEFAULT 0,
    `can_edit`     TINYINT(1)  NOT NULL DEFAULT 0,
    `can_delete`   TINYINT(1)  NOT NULL DEFAULT 0,
    `can_export`   TINYINT(1)  NOT NULL DEFAULT 0,
    `can_finalize` TINYINT(1)  NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_role_module` (`role_id`, `module`),
    FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Add role_id to users (nullable — NULL means no custom role assigned)
ALTER TABLE `users`
    ADD COLUMN IF NOT EXISTS `role_id` INT(11) DEFAULT NULL AFTER `role`,
    ADD FOREIGN KEY IF NOT EXISTS `fk_users_role_id` (`role_id`) REFERENCES `roles`(`id`) ON DELETE SET NULL;

-- ============================================================
-- Seed: 2 global system roles
-- ============================================================
INSERT IGNORE INTO `roles` (`name`, `description`, `is_system`) VALUES
('Administrator', 'Full access to all modules', 1),
('Staff',         'Standard staff access',       1);

-- ============================================================
-- Seed: Administrator — full access to all modules
-- ============================================================
INSERT IGNORE INTO `role_permissions` (`role_id`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `can_export`, `can_finalize`)
SELECT r.id, m.module, 1, 1, 1, 1, 1, 1
FROM `roles` r
JOIN (
    SELECT 'dashboard'         AS module UNION ALL
    SELECT 'products'                    UNION ALL
    SELECT 'vendors'                     UNION ALL
    SELECT 'clients'                     UNION ALL
    SELECT 'purchases'                   UNION ALL
    SELECT 'purchase_returns'            UNION ALL
    SELECT 'sales'                       UNION ALL
    SELECT 'sales_returns'               UNION ALL
    SELECT 'inventory'                   UNION ALL
    SELECT 'reports'                     UNION ALL
    SELECT 'ledgers'                     UNION ALL
    SELECT 'settings'
) m ON 1=1
WHERE r.name = 'Administrator';

-- ============================================================
-- Seed: Staff — limited access
-- ============================================================
INSERT IGNORE INTO `role_permissions` (`role_id`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `can_export`, `can_finalize`)
SELECT r.id, m.module, m.cv, m.cc, m.ce, m.cd, m.cex, m.cf
FROM `roles` r
JOIN (
    SELECT 'dashboard'         AS module, 1 cv, 0 cc, 0 ce, 0 cd, 0 cex, 0 cf UNION ALL
    SELECT 'products',                     1,   0,    0,    0,    0,      0     UNION ALL
    SELECT 'vendors',                      1,   0,    0,    0,    0,      0     UNION ALL
    SELECT 'clients',                      1,   1,    1,    0,    0,      0     UNION ALL
    SELECT 'purchases',                    1,   1,    1,    0,    1,      1     UNION ALL
    SELECT 'purchase_returns',             1,   1,    1,    0,    0,      0     UNION ALL
    SELECT 'sales',                        1,   1,    1,    0,    1,      1     UNION ALL
    SELECT 'sales_returns',                1,   1,    1,    0,    0,      0     UNION ALL
    SELECT 'inventory',                    1,   0,    0,    0,    0,      0     UNION ALL
    SELECT 'reports',                      0,   0,    0,    0,    0,      0     UNION ALL
    SELECT 'ledgers',                      0,   0,    0,    0,    0,      0     UNION ALL
    SELECT 'settings',                     0,   0,    0,    0,    0,      0
) m ON 1=1
WHERE r.name = 'Staff';
