-- Sistema de roles y permisos para Cler Admin Platform

\c cler_admin;

-- Tabla de roles
CREATE TABLE roles (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    display_name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabla de permisos
CREATE TABLE permissions (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    display_name VARCHAR(100) NOT NULL,
    description TEXT,
    module VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabla de usuarios administradores
CREATE TABLE admin_users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(255),
    avatar_url TEXT,
    role_id INT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    last_login TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT
);

-- Tabla intermedia roles-permisos
CREATE TABLE role_permissions (
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

-- Tabla de sesiones
CREATE TABLE admin_sessions (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    token VARCHAR(255) UNIQUE NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES admin_users(id) ON DELETE CASCADE
);

CREATE INDEX idx_session_token ON admin_sessions(token);
CREATE INDEX idx_session_expires ON admin_sessions(expires_at);

-- Insertar roles
INSERT INTO roles (name, display_name, description) VALUES
('super_admin', 'Super Administrador', 'Acceso total al sistema sin restricciones'),
('admin', 'Administrador', 'Acceso completo a gestión de usuarios y configuración'),
('moderator', 'Moderador', 'Gestión de errores, anuncios y contenido'),
('analyst', 'Analista', 'Solo lectura de métricas y reportes'),
('support', 'Soporte', 'Gestión de errores y usuarios básico');

-- Insertar permisos
INSERT INTO permissions (name, display_name, description, module) VALUES
-- Dashboard
('view_dashboard', 'Ver Dashboard', 'Acceso al panel principal', 'dashboard'),

-- Usuarios Admin
('view_admin_users', 'Ver Usuarios Admin', 'Listar usuarios administradores', 'admin'),
('create_admin_users', 'Crear Usuarios Admin', 'Crear nuevos administradores', 'admin'),
('edit_admin_users', 'Editar Usuarios Admin', 'Modificar administradores', 'admin'),
('delete_admin_users', 'Eliminar Usuarios Admin', 'Eliminar administradores', 'admin'),

-- Landing Page
('view_landing', 'Ver Landing Page', 'Ver contenido de landing', 'landing'),
('edit_landing', 'Editar Landing Page', 'Modificar contenido de landing', 'landing'),

-- Roles y Permisos
('view_roles', 'Ver Roles', 'Listar roles del sistema', 'admin'),
('manage_roles', 'Gestionar Roles', 'Crear y modificar roles y permisos', 'admin');

-- Asignar permisos a Super Admin (todos)
INSERT INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions;

-- Asignar permisos a Admin (todos excepto gestión de roles)
INSERT INTO role_permissions (role_id, permission_id)
SELECT 2, id FROM permissions WHERE name != 'manage_roles';

-- Asignar permisos a Moderador
INSERT INTO role_permissions (role_id, permission_id)
SELECT 3, id FROM permissions WHERE module IN ('dashboard', 'admin', 'landing') 
AND name NOT LIKE '%delete%' AND name != 'manage_roles';

-- Asignar permisos a Analista (solo lectura)
INSERT INTO role_permissions (role_id, permission_id)
SELECT 4, id FROM permissions WHERE name LIKE 'view_%';

-- Asignar permisos a Soporte
INSERT INTO role_permissions (role_id, permission_id)
SELECT 5, id FROM permissions WHERE name LIKE 'view_%';

-- Crear usuario super admin por defecto
-- Password: admin123 (cambiar en producción)
INSERT INTO admin_users (username, email, password_hash, full_name, role_id) VALUES
('admin', 'admin@cler.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Administrador Principal', 1);

-- Crear algunos usuarios de ejemplo
INSERT INTO admin_users (username, email, password_hash, full_name, role_id) VALUES
('moderador', 'moderador@cler.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Juan Moderador', 3),
('analista', 'analista@cler.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'María Analista', 4);
