-- ============================================================
-- NICC Admin Menu - Database Schema
-- Sistema de permisos para menu de administracion
-- ============================================================

-- Categorias del menu
CREATE TABLE IF NOT EXISTS admin_menu_categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    code TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    description TEXT,
    icon TEXT,
    color TEXT,
    sort_order INTEGER DEFAULT 0,
    is_collapsible INTEGER DEFAULT 1,
    default_collapsed INTEGER DEFAULT 0,
    is_active INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Items del menu (modulos)
CREATE TABLE IF NOT EXISTS admin_menu_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category_id INTEGER NOT NULL,
    code TEXT NOT NULL UNIQUE,
    plugin_name TEXT,
    name TEXT NOT NULL,
    description TEXT,
    icon TEXT,
    url TEXT,
    badge_text TEXT,
    badge_color TEXT,
    sort_order INTEGER DEFAULT 0,
    is_active INTEGER DEFAULT 1,
    is_dangerous INTEGER DEFAULT 0,
    requires_confirmation INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES admin_menu_categories(id)
);

-- Permisos por rol
CREATE TABLE IF NOT EXISTS admin_menu_permissions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    item_id INTEGER NOT NULL,
    role TEXT NOT NULL,
    can_view INTEGER DEFAULT 0,
    can_execute INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(item_id, role),
    FOREIGN KEY (item_id) REFERENCES admin_menu_items(id)
);

-- Preferencias de usuario
CREATE TABLE IF NOT EXISTS admin_menu_user_prefs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id TEXT NOT NULL,
    theme TEXT DEFAULT 'light',
    collapsed_categories TEXT,
    favorites TEXT,
    last_visited TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id)
);

-- Indices
CREATE INDEX IF NOT EXISTS idx_menu_items_category ON admin_menu_items(category_id);
CREATE INDEX IF NOT EXISTS idx_menu_permissions_item ON admin_menu_permissions(item_id);
CREATE INDEX IF NOT EXISTS idx_menu_permissions_role ON admin_menu_permissions(role);

-- ============================================================
-- DATOS INICIALES - Categorias
-- ============================================================

INSERT OR IGNORE INTO admin_menu_categories (code, name, description, icon, color, sort_order) VALUES
('system', 'Sistema y Configuracion', 'Herramientas principales del sistema', 'settings', '#3b82f6', 1),
('users', 'Usuarios y Permisos', 'Gestion de usuarios y control de acceso', 'users', '#8b5cf6', 2),
('content', 'Contenido y Paginas', 'Herramientas de gestion de contenido', 'file-text', '#10b981', 3),
('nicc_modules', 'Modulos NICC', 'Modulos adicionales del sistema NICC', 'puzzle', '#ec4899', 4),
('tools', 'Herramientas', 'Utilidades y herramientas avanzadas', 'wrench', '#f59e0b', 5),
('danger', 'Zona de Peligro', 'Herramientas que requieren precaucion', 'alert-triangle', '#ef4444', 99);

-- ============================================================
-- DATOS INICIALES - Items del menu (Sistema)
-- ============================================================

INSERT OR IGNORE INTO admin_menu_items (category_id, code, plugin_name, name, description, icon, url, sort_order) VALUES
((SELECT id FROM admin_menu_categories WHERE code='system'), 'config', 'config', 'Configuracion', 'Ajustes generales del sistema', 'settings', '?do=admin&page=config', 1),
((SELECT id FROM admin_menu_categories WHERE code='system'), 'styling', 'styling', 'Ajustes de Plantilla', 'Personalizar apariencia', 'palette', '?do=admin&page=styling', 2),
((SELECT id FROM admin_menu_categories WHERE code='system'), 'logviewer', 'logviewer', 'Ver Registros', 'Logs del sistema', 'scroll-text', '?do=admin&page=logviewer', 3),
((SELECT id FROM admin_menu_categories WHERE code='system'), 'extension', 'extension', 'Extensiones', 'Gestionar plugins', 'puzzle', '?do=admin&page=extension', 4);

-- Items del menu (Usuarios)
INSERT OR IGNORE INTO admin_menu_items (category_id, code, plugin_name, name, description, icon, url, sort_order) VALUES
((SELECT id FROM admin_menu_categories WHERE code='users'), 'acl', 'acl', 'Control de Acceso', 'Perfiles y permisos ACL', 'shield', '?do=admin&page=acl', 1),
((SELECT id FROM admin_menu_categories WHERE code='users'), 'usermanager', 'usermanager', 'Administrar Usuarios', 'Crear y gestionar usuarios', 'users', '?do=admin&page=usermanager', 2),
((SELECT id FROM admin_menu_categories WHERE code='users'), 'projectacl', 'projectacl', 'Permisos de Proyectos', 'ACL por proyecto', 'folder-lock', '?do=admin&page=projectacl', 3);

-- Items del menu (Modulos NICC)
INSERT OR IGNORE INTO admin_menu_items (category_id, code, plugin_name, name, description, icon, url, sort_order, badge_text, badge_color) VALUES
((SELECT id FROM admin_menu_categories WHERE code='nicc_modules'), 'beztaxcalendar', 'beztaxcalendar', 'Calendario Tributario DIAN', 'Gestion de obligaciones tributarias', 'calendar', '?do=admin&page=beztaxcalendar', 1, 'Nuevo', '#10b981'),
((SELECT id FROM admin_menu_categories WHERE code='nicc_modules'), 'tasksemaphore', 'tasksemaphore', 'Semaforizacion de Tareas', 'Control visual de estados', 'traffic-cone', '?do=admin&page=tasksemaphore', 2, NULL, NULL),
((SELECT id FROM admin_menu_categories WHERE code='nicc_modules'), 'backup', 'backup', 'Backup Tool', 'Copias de seguridad', 'hard-drive', '?do=admin&page=backup', 3, NULL, NULL),
((SELECT id FROM admin_menu_categories WHERE code='nicc_modules'), 'sqlite', 'sqlite', 'SQLite Interfaz', 'Administrar base de datos', 'database', '?do=admin&page=sqlite', 4, NULL, NULL),
((SELECT id FROM admin_menu_categories WHERE code='nicc_modules'), 'bezdbrepair', 'bezdbrepair', 'Reparar BD BEZ', 'Diagnostico y reparacion', 'wrench', '?do=admin&page=bezdbrepair', 5, NULL, NULL);

-- Items del menu (Herramientas)
INSERT OR IGNORE INTO admin_menu_items (category_id, code, plugin_name, name, description, icon, url, sort_order) VALUES
((SELECT id FROM admin_menu_categories WHERE code='tools'), 'revert', 'revert', 'Revertir Cambios', 'Revertir paginas modificadas', 'rotate-ccw', '?do=admin&page=revert', 1),
((SELECT id FROM admin_menu_categories WHERE code='tools'), 'clearhistory', 'clearhistory', 'Limpiar Historial', 'Eliminar revisiones antiguas', 'clock', '?do=admin&page=clearhistory', 2);

-- Items del menu (Zona de Peligro)
INSERT OR IGNORE INTO admin_menu_items (category_id, code, plugin_name, name, description, icon, url, sort_order, is_dangerous, requires_confirmation) VALUES
((SELECT id FROM admin_menu_categories WHERE code='danger'), 'dbcleanup', 'dbcleanup', 'Limpiar Base de Datos', 'Eliminar datos obsoletos - IRREVERSIBLE', 'trash-2', '?do=admin&page=dbcleanup', 1, 1, 1);

-- ============================================================
-- PERMISOS POR DEFECTO
-- ============================================================

-- Superuser: acceso total a todo
INSERT OR IGNORE INTO admin_menu_permissions (item_id, role, can_view, can_execute)
SELECT id, 'superuser', 1, 1 FROM admin_menu_items;

-- Admin: acceso a casi todo excepto zona de peligro
INSERT OR IGNORE INTO admin_menu_permissions (item_id, role, can_view, can_execute)
SELECT id, 'admin', 1, 1 FROM admin_menu_items WHERE is_dangerous = 0;

INSERT OR IGNORE INTO admin_menu_permissions (item_id, role, can_view, can_execute)
SELECT id, 'admin', 0, 0 FROM admin_menu_items WHERE is_dangerous = 1;

-- Manager: acceso limitado a modulos operativos
INSERT OR IGNORE INTO admin_menu_permissions (item_id, role, can_view, can_execute)
SELECT id, 'manager', 1, 1 FROM admin_menu_items
WHERE code IN ('beztaxcalendar', 'tasksemaphore', 'backup', 'logviewer', 'bezdbrepair');

INSERT OR IGNORE INTO admin_menu_permissions (item_id, role, can_view, can_execute)
SELECT id, 'manager', 0, 0 FROM admin_menu_items
WHERE code NOT IN ('beztaxcalendar', 'tasksemaphore', 'backup', 'logviewer', 'bezdbrepair');