-- ============================================
-- MIGRACIÓN: Sistema de Roles y Permisos
-- Fecha: 2026-01-26
-- Descripción: Tablas para gestión de roles y permisos por módulo
-- ============================================

-- ============================================
-- PASO 1: TABLA DE ROLES
-- ============================================

CREATE TABLE IF NOT EXISTS roles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(50) NOT NULL UNIQUE,
    nombre VARCHAR(100) NOT NULL,
    descripcion VARCHAR(255),
    activo TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_codigo (codigo),
    INDEX idx_activo (activo)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

-- Insertar roles predefinidos
INSERT INTO roles (codigo, nombre, descripcion, activo) VALUES
('admin', 'Administrador', 'Acceso total al sistema', 1),
('operador', 'Operador', 'Puede realizar lecturas, facturación y cortes', 1),
('cajero', 'Cajero', 'Solo puede registrar pagos y consultar información', 1),
('lector', 'Lector', 'Solo puede consultar información, sin modificaciones', 1)
ON DUPLICATE KEY UPDATE nombre=VALUES(nombre);

-- ============================================
-- PASO 2: TABLA DE PERMISOS POR ROL
-- ============================================

CREATE TABLE IF NOT EXISTS role_permissions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id INT UNSIGNED NOT NULL,
    module VARCHAR(50) NOT NULL,
    action VARCHAR(50) NOT NULL,
    granted TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_role_module_action (role_id, module, action),
    INDEX idx_role (role_id),
    INDEX idx_module (module),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

-- ============================================
-- PASO 3: PERMISOS POR DEFECTO
-- ============================================

-- Admin: acceso total
INSERT INTO role_permissions (role_id, module, action, granted)
SELECT r.id, m.module, a.action, 1
FROM roles r
CROSS JOIN (
    SELECT 'socios' as module UNION ALL
    SELECT 'lecturas' UNION ALL
    SELECT 'facturacion' UNION ALL
    SELECT 'pagos' UNION ALL
    SELECT 'cortes' UNION ALL
    SELECT 'subsidios' UNION ALL
    SELECT 'repactaciones' UNION ALL
    SELECT 'reportes' UNION ALL
    SELECT 'configuracion' UNION ALL
    SELECT 'usuarios'
) m
CROSS JOIN (
    SELECT 'lectura' as action UNION ALL
    SELECT 'escritura' UNION ALL
    SELECT 'eliminacion'
) a
WHERE r.codigo = 'admin'
ON DUPLICATE KEY UPDATE granted=1;

-- Operador: lecturas, facturación, cortes (lectura y escritura)
INSERT INTO role_permissions (role_id, module, action, granted)
SELECT r.id, m.module, a.action, 1
FROM roles r
CROSS JOIN (
    SELECT 'socios' as module UNION ALL
    SELECT 'lecturas' UNION ALL
    SELECT 'facturacion' UNION ALL
    SELECT 'cortes' UNION ALL
    SELECT 'subsidios' UNION ALL
    SELECT 'repactaciones' UNION ALL
    SELECT 'reportes'
) m
CROSS JOIN (
    SELECT 'lectura' as action UNION ALL
    SELECT 'escritura'
) a
WHERE r.codigo = 'operador'
ON DUPLICATE KEY UPDATE granted=1;

-- Cajero: pagos (escritura), consultas (lectura)
INSERT INTO role_permissions (role_id, module, action, granted)
SELECT r.id, m.module, a.action, 1
FROM roles r
CROSS JOIN (
    SELECT 'socios' as module UNION ALL
    SELECT 'pagos' UNION ALL
    SELECT 'reportes'
) m
CROSS JOIN (
    SELECT 'lectura' as action UNION ALL
    SELECT 'escritura'
) a
WHERE r.codigo = 'cajero' AND (m.module = 'pagos' OR a.action = 'lectura')
ON DUPLICATE KEY UPDATE granted=1;

-- Lector: solo lectura en todos los módulos
INSERT INTO role_permissions (role_id, module, action, granted)
SELECT r.id, m.module, 'lectura' as action, 1
FROM roles r
CROSS JOIN (
    SELECT 'socios' as module UNION ALL
    SELECT 'lecturas' UNION ALL
    SELECT 'facturacion' UNION ALL
    SELECT 'pagos' UNION ALL
    SELECT 'cortes' UNION ALL
    SELECT 'subsidios' UNION ALL
    SELECT 'repactaciones' UNION ALL
    SELECT 'reportes'
) m
WHERE r.codigo = 'lector'
ON DUPLICATE KEY UPDATE granted=1;

-- ============================================
-- PASO 4: ACTUALIZAR TABLA users PARA USAR role_id
-- ============================================
-- Opcional: cambiar role VARCHAR a role_id INT
-- ALTER TABLE users 
-- ADD COLUMN role_id INT UNSIGNED AFTER role,
-- ADD FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT;

-- ============================================
-- VERIFICACIÓN
-- ============================================
-- SELECT r.codigo, r.nombre, COUNT(rp.id) as total_permisos 
-- FROM roles r 
-- LEFT JOIN role_permissions rp ON r.id = rp.role_id 
-- GROUP BY r.id, r.codigo, r.nombre;
-- 
-- SELECT r.codigo, rp.module, rp.action, rp.granted
-- FROM roles r
-- JOIN role_permissions rp ON r.id = rp.role_id
-- ORDER BY r.codigo, rp.module, rp.action;