-- =====================================================
-- SCRIPT DE ACTUALIZACIÓN PARA PRODUCCIÓN - ProfixCRM
-- =====================================================
-- Este script debe ejecutarse en phpMyAdmin para actualizar
-- la base de datos de producción con todas las tablas y datos necesarios
-- =====================================================

-- Crear base de datos si no existe
CREATE DATABASE IF NOT EXISTS simple_crm;
USE simple_crm;

-- =====================================================
-- CREACIÓN DE TABLAS PRINCIPALES
-- =====================================================

-- Tabla de usuarios
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    last_login DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    voiso_extension VARCHAR(50) NULL,
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_status (status)
);

-- Tabla de roles
CREATE TABLE IF NOT EXISTS roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    display_name VARCHAR(100) NOT NULL,
    description TEXT,
    is_system BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name),
    INDEX idx_is_system (is_system)
);

-- Tabla de permisos
CREATE TABLE IF NOT EXISTS permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    display_name VARCHAR(150) NOT NULL,
    description TEXT,
    module VARCHAR(50) NOT NULL DEFAULT 'general',
    action VARCHAR(50) NOT NULL DEFAULT 'view',
    is_system BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name),
    INDEX idx_module_action (module, action),
    INDEX idx_is_system (is_system)
);

-- Tabla de relación usuarios-roles
CREATE TABLE IF NOT EXISTS user_roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_role (user_id, role_id),
    INDEX idx_user_id (user_id),
    INDEX idx_role_id (role_id)
);

-- Tabla de relación roles-permisos
CREATE TABLE IF NOT EXISTS role_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE,
    UNIQUE KEY unique_role_permission (role_id, permission_id),
    INDEX idx_role_id (role_id),
    INDEX idx_permission_id (permission_id)
);

-- Tabla de escritorios (desks)
CREATE TABLE IF NOT EXISTS desks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    manager_id INT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (manager_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_name (name),
    INDEX idx_status (status),
    INDEX idx_manager_id (manager_id)
);

-- Tabla de usuarios asignados a escritorios
CREATE TABLE IF NOT EXISTS desk_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    desk_id INT NOT NULL,
    user_id INT NOT NULL,
    assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (desk_id) REFERENCES desks(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_desk_user (desk_id, user_id),
    INDEX idx_desk_id (desk_id),
    INDEX idx_user_id (user_id)
);

-- Tabla de estados permitidos por escritorio
CREATE TABLE IF NOT EXISTS desk_allowed_statuses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    desk_id INT NOT NULL,
    status VARCHAR(50) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (desk_id) REFERENCES desks(id) ON DELETE CASCADE,
    UNIQUE KEY unique_desk_status (desk_id, status),
    INDEX idx_desk_id (desk_id)
);

-- Tabla de leads
CREATE TABLE IF NOT EXISTS leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    company VARCHAR(100),
    job_title VARCHAR(100),
    source VARCHAR(50),
    status VARCHAR(50) DEFAULT 'new',
    priority ENUM('low', 'medium', 'high') DEFAULT 'medium',
    assigned_to INT NULL,
    desk_id INT NULL,
    notes TEXT,
    country VARCHAR(100),
    city VARCHAR(100),
    trading_experience ENUM('none', 'beginner', 'intermediate', 'advanced') DEFAULT 'none',
    investment_amount DECIMAL(15,2) DEFAULT 0.00,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (desk_id) REFERENCES desks(id) ON DELETE SET NULL,
    INDEX idx_email (email),
    INDEX idx_status (status),
    INDEX idx_assigned_to (assigned_to),
    INDEX idx_desk_id (desk_id),
    INDEX idx_created_at (created_at)
);

-- Tabla de historial de estados de leads
CREATE TABLE IF NOT EXISTS lead_status_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_id INT NOT NULL,
    old_status VARCHAR(50),
    new_status VARCHAR(50) NOT NULL,
    changed_by INT NOT NULL,
    notes TEXT,
    changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
    FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_lead_id (lead_id),
    INDEX idx_changed_at (changed_at)
);

-- Tabla de actividades de leads
CREATE TABLE IF NOT EXISTS lead_activities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_id INT NOT NULL,
    type ENUM('call', 'email', 'meeting', 'note', 'task') NOT NULL,
    subject VARCHAR(255) NOT NULL,
    description TEXT,
    status ENUM('pending', 'completed', 'cancelled') DEFAULT 'pending',
    scheduled_at DATETIME NULL,
    duration_min INT NULL,
    completed_at DATETIME NULL,
    created_by INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_lead_id (lead_id),
    INDEX idx_type (type),
    INDEX idx_status (status),
    INDEX idx_scheduled_at (scheduled_at),
    INDEX idx_created_by (created_by)
);

-- =====================================================
-- TABLAS DE CLIENTES Y TRANSACCIONES
-- =====================================================

-- Tabla de clientes
CREATE TABLE IF NOT EXISTS clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_id INT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone VARCHAR(20),
    status ENUM('active','inactive','suspended') DEFAULT 'active',
    password_hash VARCHAR(255) NOT NULL,
    desk_id INT NULL,
    balance DECIMAL(18,2) DEFAULT 0,
    equity DECIMAL(18,2) DEFAULT 0,
    margin_percent DECIMAL(7,4) DEFAULT 0,
    pnl DECIMAL(18,2) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE SET NULL,
    FOREIGN KEY (desk_id) REFERENCES desks(id) ON DELETE SET NULL,
    INDEX idx_email (email),
    INDEX idx_status (status),
    INDEX idx_clients_desk (desk_id)
);
ALTER TABLE clients ADD COLUMN IF NOT EXISTS last_seen DATETIME NULL;

CREATE TABLE IF NOT EXISTS client_presence (
    client_id INT PRIMARY KEY,
    last_seen DATETIME NULL,
    source VARCHAR(20) NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Añadir columna para agente asignado directamente al cliente
ALTER TABLE clients 
    ADD COLUMN IF NOT EXISTS assigned_to INT NULL;
ALTER TABLE clients 
    ADD CONSTRAINT IF NOT EXISTS fk_clients_assigned FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE clients ADD INDEX IF NOT EXISTS idx_clients_assigned_to (assigned_to);

-- Perfil del cliente: añadir campos de dirección y documento si no existen
ALTER TABLE clients 
    ADD COLUMN IF NOT EXISTS address_line VARCHAR(255) NULL,
    ADD COLUMN IF NOT EXISTS country VARCHAR(64) NULL,
    ADD COLUMN IF NOT EXISTS phone_country_code VARCHAR(8) NULL,
    ADD COLUMN IF NOT EXISTS state VARCHAR(100) NULL,
    ADD COLUMN IF NOT EXISTS province VARCHAR(100) NULL,
    ADD COLUMN IF NOT EXISTS city VARCHAR(100) NULL,
    ADD COLUMN IF NOT EXISTS postal_code VARCHAR(20) NULL,
    ADD COLUMN IF NOT EXISTS district VARCHAR(100) NULL,
    ADD COLUMN IF NOT EXISTS document_number VARCHAR(64) NULL;

-- Tabla de cuentas de clientes
CREATE TABLE IF NOT EXISTS client_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    account_type VARCHAR(50) NOT NULL,
    account_number VARCHAR(100) NOT NULL,
    currency VARCHAR(10) DEFAULT 'USD',
    balance DECIMAL(18,2) DEFAULT 0,
    provider VARCHAR(100) NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    UNIQUE KEY unique_client_account (client_id, account_number),
    INDEX idx_client_id (client_id)
);

-- Tabla de depósitos de clientes
CREATE TABLE IF NOT EXISTS client_deposits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    amount DECIMAL(18,2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'USD',
    method VARCHAR(50) NOT NULL,
    reference VARCHAR(100) NULL,
    status ENUM('pending','confirmed','failed') DEFAULT 'confirmed',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    INDEX idx_client_id (client_id),
    INDEX idx_created_at (created_at)
);

-- Tabla de transacciones de clientes
CREATE TABLE IF NOT EXISTS client_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    type ENUM('deposit','withdrawal','adjustment') NOT NULL,
    amount DECIMAL(18,2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'USD',
    balance_after DECIMAL(18,2) NULL,
    reference VARCHAR(100) NULL,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    INDEX idx_client_id (client_id),
    INDEX idx_type (type),
    INDEX idx_created_at (created_at)
);

-- Actividades de clientes
CREATE TABLE IF NOT EXISTS client_activities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    type ENUM('call','email','meeting','note','task','system') NOT NULL,
    subject VARCHAR(255) NOT NULL,
    description TEXT,
    status ENUM('pending','completed','cancelled') DEFAULT 'completed',
    scheduled_at DATETIME NULL,
    duration_min INT NULL,
    completed_at DATETIME NULL,
    created_by INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_client_id (client_id),
    INDEX idx_type (type),
    INDEX idx_status (status),
    INDEX idx_scheduled_at (scheduled_at),
    INDEX idx_created_by (created_by),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabla de documentos de clientes (KYC)
CREATE TABLE IF NOT EXISTS client_documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    type ENUM('id','address','selfie','other') NOT NULL,
    filename VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NULL,
    mime_type VARCHAR(100) NULL,
    size INT NULL,
    status ENUM('submitted','approved','rejected') DEFAULT 'submitted',
    notes TEXT NULL,
    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    INDEX idx_client_type (client_id, type),
    INDEX idx_client_status (client_id, status)
);

-- =====================================================
-- TABLAS DE SOPORTE
-- =====================================================

-- Tabla de tickets de soporte
CREATE TABLE IF NOT EXISTS support_tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    subject VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    status ENUM('open','pending','closed') DEFAULT 'open',
    priority ENUM('low','medium','high') DEFAULT 'medium',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    INDEX idx_client_status (client_id, status)
);

-- Tabla de mensajes de tickets
CREATE TABLE IF NOT EXISTS support_ticket_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NOT NULL,
    sender_type ENUM('client','agent') DEFAULT 'client',
    message TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE,
    INDEX idx_ticket (ticket_id),
    INDEX idx_sender (sender_type)
);

-- =====================================================
-- TABLA DE CONFIGURACIÓN DEL SISTEMA
-- =====================================================

-- Tabla de configuración del sistema
CREATE TABLE IF NOT EXISTS system_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    config_key VARCHAR(100) NOT NULL UNIQUE,
    config_value TEXT,
    description VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_config_key (config_key)
);

-- =====================================================
-- INSERCIÓN DE DATOS INICIALES
-- =====================================================

-- Insertar roles del sistema
INSERT INTO roles (name, display_name, description, is_system, is_active) VALUES
('admin', 'Administrador', 'Acceso completo al sistema', TRUE, TRUE),
('manager', 'Gerente', 'Gestión de equipos y reportes', TRUE, TRUE),
('user', 'Usuario', 'Acceso básico al sistema', TRUE, TRUE),
('sales_rep', 'Representante de Ventas', 'Gestión de leads y oportunidades', FALSE, TRUE),
('marketing', 'Marketing', 'Gestión de campañas y contenido', FALSE, TRUE),
('support', 'Soporte', 'Gestión de tickets y revisión de documentos KYC', FALSE, TRUE)
ON DUPLICATE KEY UPDATE 
    display_name = VALUES(display_name),
    description = VALUES(description),
    is_system = VALUES(is_system),
    is_active = VALUES(is_active);

-- Insertar permisos del sistema
INSERT INTO permissions (name, display_name, description, module, action, is_system) VALUES
-- Permisos de usuarios
('users.view', 'Ver Usuarios', 'Permite ver la lista de usuarios', 'users', 'view', TRUE),
('users.create', 'Crear Usuarios', 'Permite crear nuevos usuarios', 'users', 'create', TRUE),
('users.edit', 'Editar Usuarios', 'Permite editar información de usuarios', 'users', 'edit', TRUE),
('users.delete', 'Eliminar Usuarios', 'Permite eliminar usuarios del sistema', 'users', 'delete', TRUE),
('users.manage_roles', 'Gestionar Roles de Usuario', 'Permite asignar y remover roles', 'users', 'manage_roles', TRUE),

-- Permisos de leads
('leads.view', 'Ver Leads', 'Permite ver la lista de leads', 'leads', 'view', TRUE),
('leads.view_all', 'Ver Todos los Leads', 'Permite ver leads de todos los usuarios', 'leads', 'view_all', TRUE),
('leads.create', 'Crear Leads', 'Permite crear nuevos leads', 'leads', 'create', TRUE),
('leads.edit', 'Editar Leads', 'Permite editar información de leads', 'leads', 'edit', TRUE),
('leads.edit_all', 'Editar Todos los Leads', 'Permite editar leads de cualquier usuario', 'leads', 'edit_all', TRUE),
('leads.delete', 'Eliminar Leads', 'Permite eliminar leads', 'leads', 'delete', TRUE),
('leads.assign', 'Asignar Leads', 'Permite asignar leads a otros usuarios', 'leads', 'assign', TRUE),
('leads.export', 'Exportar Leads', 'Permite exportar datos de leads', 'leads', 'export', TRUE),
('leads.import', 'Importar Leads', 'Permite importar leads masivamente', 'leads', 'import', TRUE),
('leads.view_email_full', 'Ver Email Completo', 'Permite ver el email del lead sin enmascarar', 'leads', 'view_email_full', TRUE),
('leads.view_phone_full', 'Ver Teléfono Completo', 'Permite ver el teléfono del lead sin enmascarar', 'leads', 'view_phone_full', TRUE),
('lead_email_mask', 'Ver Email Completo (Leads)', 'Permite ver el email del lead sin enmascarar', 'leads', 'mask_email', TRUE),
('lead_number_mask', 'Ver Teléfono Completo (Leads)', 'Permite ver el teléfono del lead sin enmascarar', 'leads', 'mask_phone', TRUE),

-- Permisos de clientes
('clients.view', 'Ver Clientes', 'Permite ver la lista de clientes', 'clients', 'view', TRUE),
('clients.create', 'Crear Clientes', 'Permite crear nuevos clientes', 'clients', 'create', TRUE),
('clients.edit', 'Editar Clientes', 'Permite editar información de clientes', 'clients', 'edit', TRUE),
('clients.delete', 'Eliminar Clientes', 'Permite eliminar clientes', 'clients', 'delete', TRUE),
('clients.view_email_full', 'Ver Email Completo (Clientes)', 'Permite ver el email del cliente sin enmascarar', 'clients', 'view_email_full', TRUE),
('clients.view_phone_full', 'Ver Teléfono Completo (Clientes)', 'Permite ver el teléfono del cliente sin enmascarar', 'clients', 'view_phone_full', TRUE),
('client_email_mask', 'Ver Email Completo (Clientes)', 'Permite ver el email del cliente sin enmascarar', 'clients', 'mask_email', TRUE),
('client_number_mask', 'Ver Teléfono Completo (Clientes)', 'Permite ver el teléfono del cliente sin enmascarar', 'clients', 'mask_phone', TRUE),

-- Permisos de actividades
('activities.view', 'Ver Actividades', 'Permite ver actividades propias', 'activities', 'view', TRUE),
('activities.view_all', 'Ver Todas las Actividades', 'Permite ver actividades de todos los usuarios', 'activities', 'view_all', TRUE),
('activities.create', 'Crear Actividades', 'Permite crear nuevas actividades', 'activities', 'create', TRUE),
('activities.edit', 'Editar Actividades', 'Permite editar actividades propias', 'activities', 'edit', TRUE),
('activities.edit_all', 'Editar Todas las Actividades', 'Permite editar actividades de cualquier usuario', 'activities', 'edit_all', TRUE),
('activities.delete', 'Eliminar Actividades', 'Permite eliminar actividades', 'activities', 'delete', TRUE),

-- Permisos de reportes
('reports.view', 'Ver Reportes', 'Permite ver reportes básicos', 'reports', 'view', TRUE),
('reports.advanced', 'Reportes Avanzados', 'Permite acceder a reportes avanzados', 'reports', 'advanced', TRUE),
('reports.export', 'Exportar Reportes', 'Permite exportar reportes', 'reports', 'export', TRUE),
('reports.create', 'Crear Reportes', 'Permite crear reportes personalizados', 'reports', 'create', TRUE),

-- Permisos de configuración
('settings.view', 'Ver Configuración', 'Permite ver configuraciones del sistema', 'settings', 'view', TRUE),
('settings.edit', 'Editar Configuración', 'Permite modificar configuraciones', 'settings', 'edit', TRUE),
('settings.system', 'Configuración del Sistema', 'Permite acceder a configuraciones críticas', 'settings', 'system', TRUE),

-- Permisos de roles
('roles.view', 'Ver Roles', 'Permite ver la lista de roles', 'roles', 'view', TRUE),
('roles.create', 'Crear Roles', 'Permite crear nuevos roles', 'roles', 'create', TRUE),
('roles.edit', 'Editar Roles', 'Permite editar roles existentes', 'roles', 'edit', TRUE),
('roles.delete', 'Eliminar Roles', 'Permite eliminar roles no críticos', 'roles', 'delete', TRUE),
('roles.assign', 'Asignar Roles', 'Permite asignar roles a usuarios', 'roles', 'assign', TRUE),
('permissions.view', 'Ver Permisos', 'Permite ver la lista de permisos', 'permissions', 'view', TRUE),
('permissions.manage', 'Gestionar Permisos', 'Permite gestionar permisos de roles', 'permissions', 'manage', TRUE),

-- Permisos de dashboard
('dashboard.view', 'Ver Dashboard', 'Permite acceder al dashboard principal', 'dashboard', 'view', TRUE),
('dashboard.analytics', 'Ver Analytics', 'Permite ver métricas y análisis avanzados', 'dashboard', 'analytics', TRUE),

-- Permisos de menú
('menu.dashboard.view', 'Ver Menú Dashboard', 'Permite ver el ítem de Dashboard en el menú', 'menu', 'dashboard.view', TRUE),
('menu.leads.view', 'Ver Menú Leads', 'Permite ver el ítem de Leads en el menú', 'menu', 'leads.view', TRUE),
('menu.activities.view', 'Ver Menú Actividades', 'Permite ver el ítem de Actividades en el menú', 'menu', 'activities.view', TRUE),
('menu.reports.view', 'Ver Menú Reportes', 'Permite ver el ítem de Reportes en el menú', 'menu', 'reports.view', TRUE),
('menu.users.view', 'Ver Menú Usuarios', 'Permite ver el ítem de Usuarios en el menú', 'menu', 'users.view', TRUE),
('menu.roles.view', 'Ver Menú Roles', 'Permite ver el ítem de Roles y Permisos en el menú', 'menu', 'roles.view', TRUE),
('menu.settings.view', 'Ver Menú Configuración', 'Permite ver el ítem de Configuración en el menú', 'menu', 'settings.view', TRUE),
('menu.desks.view', 'Ver Menú Desks', 'Permite ver el ítem de Desks en el menú', 'menu', 'desks.view', TRUE),
('menu.clients.view', 'Ver Menú Clientes', 'Permite ver el ítem de Clientes en el menú', 'menu', 'clients.view', TRUE),
('menu.tickets.view', 'Ver Menú Tickets', 'Permite ver el ítem de Tickets en el menú', 'menu', 'tickets.view', TRUE),

-- Permisos de pagos e integraciones
('payments.configure', 'Configurar Pagos', 'Permite configurar métodos de pago e integraciones', 'payments', 'configure', TRUE),
('integrations.view', 'Ver Integraciones', 'Permite ver la página de integraciones', 'integrations', 'view', TRUE),
('integrations.manage', 'Gestionar Integraciones', 'Permite gestionar todas las integraciones', 'integrations', 'manage', TRUE),
('big4pay.view', 'Ver Big4pay', 'Permite ver la configuración de Big4pay', 'integrations', 'big4pay.view', TRUE),
('big4pay.configure', 'Configurar Big4pay', 'Permite configurar la integración de Big4pay', 'integrations', 'big4pay.configure', TRUE),
('big4pay.test', 'Probar Big4pay', 'Permite ejecutar pruebas de la integración Big4pay', 'integrations', 'big4pay.test', TRUE),
('big4pay.process_payments', 'Procesar Pagos Big4pay', 'Permite procesar pagos a través de Big4pay', 'integrations', 'big4pay.process_payments', TRUE),
('voiso.view', 'Ver Voiso', 'Permite ver la configuración de Voiso', 'integrations', 'voiso.view', TRUE),
('voiso.configure', 'Configurar Voiso', 'Permite configurar la integración de Voiso', 'integrations', 'voiso.configure', TRUE),
('voiso.test', 'Probar Voiso', 'Permite ejecutar pruebas de la integración Voiso', 'integrations', 'voiso.test', TRUE),
('voiso.manage_calls', 'Gestionar Llamadas Voiso', 'Permite gestionar llamadas a través de Voiso', 'integrations', 'voiso.manage_calls', TRUE),
('api.webhooks.manage', 'Gestionar Webhooks', 'Permite gestionar webhooks de integraciones', 'integrations', 'api.webhooks.manage', TRUE),
('api.keys.manage', 'Gestionar API Keys', 'Permite gestionar claves de API', 'integrations', 'api.keys.manage', TRUE),
('api.logs.view', 'Ver Logs de API', 'Permite ver logs de integraciones API', 'integrations', 'api.logs.view', TRUE),

-- Permisos de documentos y tickets
('documents.review', 'Revisar Documentos', 'Permite ver y revisar documentos KYC de clientes', 'documents', 'review', FALSE),
('documents.update_status', 'Actualizar Estado de Documento', 'Permite aprobar o rechazar documentos KYC', 'documents', 'update_status', FALSE),
('tickets.view', 'Ver Tickets de Clientes', 'Permite ver tickets de soporte de clientes', 'tickets', 'view', FALSE),
('tickets.reply', 'Responder Tickets', 'Permite responder en el hilo de mensajes de tickets', 'tickets', 'reply', FALSE)

ON DUPLICATE KEY UPDATE 
    display_name = VALUES(display_name),
    description = VALUES(description),
    module = VALUES(module),
    action = VALUES(action),
    is_system = VALUES(is_system);

-- Insertar usuario administrador por defecto
INSERT INTO users (username, email, first_name, last_name, password_hash, status) VALUES
('admin', 'admin@profixcrm.com', 'Admin', 'User', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'active')
ON DUPLICATE KEY UPDATE 
    email = VALUES(email),
    first_name = VALUES(first_name),
    last_name = VALUES(last_name),
    status = VALUES(status);

-- Insertar escritorio por defecto
INSERT INTO desks (name, description, status) VALUES
('General', 'Escritorio general del sistema', 'active')
ON DUPLICATE KEY UPDATE 
    description = VALUES(description),
    status = VALUES(status);

-- Configuraciones del sistema
INSERT INTO system_config (config_key, config_value, description) VALUES
('company_name', 'ProfixCRM', 'Nombre de la empresa'),
('company_logo', '', 'Logo de la empresa'),
('system_timezone', 'UTC', 'Zona horaria del sistema'),
('email_notifications', '1', 'Activar notificaciones por email'),
('lead_auto_assignment', '0', 'Asignación automática de leads'),
('max_file_upload_size', '10485760', 'Tamaño máximo de archivo en bytes (10MB)'),
('session_timeout', '3600', 'Tiempo de expiración de sesión en segundos'),
('password_min_length', '8', 'Longitud mínima de contraseña'),
('enable_two_factor', '0', 'Activar autenticación de dos factores'),
('maintenance_mode', '0', 'Modo de mantenimiento')
ON DUPLICATE KEY UPDATE 
    config_value = VALUES(config_value),
    description = VALUES(description);

-- =====================================================
-- ASIGNACIÓN DE PERMISOS A ROLES
-- =====================================================

-- Asignar todos los permisos al rol Admin
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p WHERE r.name = 'admin';

-- Asignar permisos al rol Manager (sin configuración del sistema)
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p 
WHERE r.name = 'manager' AND p.name NOT IN ('settings.system', 'users.delete', 'roles.delete', 'api.keys.manage');

-- Asignar permisos básicos al rol User
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p 
WHERE r.name = 'user' AND p.name IN (
    'dashboard.view',
    'menu.dashboard.view',
    'menu.leads.view',
    'menu.activities.view',
    'menu.reports.view',
    'leads.view', 'leads.create', 'leads.edit',
    'activities.view', 'activities.create', 'activities.edit',
    'reports.view'
);

-- Asignar permisos al rol Sales Rep
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p 
WHERE r.name = 'sales_rep' AND p.name IN (
    'dashboard.view', 'dashboard.analytics',
    'menu.dashboard.view', 'menu.leads.view', 'menu.activities.view', 'menu.reports.view',
    'leads.view', 'leads.create', 'leads.edit', 'leads.export',
    'activities.view', 'activities.create', 'activities.edit',
    'reports.view', 'reports.export',
    'big4pay.view', 'big4pay.configure', 'big4pay.test',
    'voiso.view', 'voiso.configure', 'voiso.test',
    'payments.configure'
);

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p 
WHERE r.name = 'sales_rep' AND p.name IN (
    'checkout.key2pay.list_methods',
    'checkout.key2pay.create_checkout',
    'checkout.key2pay.ensure_token',
    'checkout.key2pay.create_payment',
    'checkout.key2pay.create_payment_raw'
);

-- Asignar permisos al rol Marketing
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p 
WHERE r.name = 'marketing' AND p.name IN (
    'dashboard.view', 'dashboard.analytics',
    'menu.dashboard.view', 'menu.leads.view', 'menu.activities.view', 'menu.reports.view',
    'leads.view', 'leads.view_all', 'leads.create', 'leads.import', 'leads.export',
    'activities.view', 'activities.view_all',
    'reports.view', 'reports.advanced', 'reports.export', 'reports.create'
);

-- Asignar permisos al rol Support
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p 
WHERE r.name = 'support' AND p.name IN (
    'documents.review', 'documents.update_status', 'menu.desks.view',
    'tickets.view', 'tickets.reply'
);

-- Asignar rol admin al usuario admin
INSERT IGNORE INTO permissions (name, display_name, description, module, action, is_system) VALUES
('menu.calendar.view','Ver Menú Calendario','Permite ver el ítem de Calendario en el menú','menu','calendar.view', TRUE),
('menu.integrations.view','Ver Menú Integraciones','Permite ver el ítem de Integraciones en el menú','menu','integrations.view', TRUE),
('menu.payments.view','Ver Menú Pagos','Permite ver el ítem de Métodos de Pago','menu','payments.view', TRUE);

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT (SELECT id FROM roles WHERE name = 'admin' LIMIT 1), p.id FROM permissions p WHERE p.name IN ('menu.calendar.view','menu.integrations.view','menu.payments.view');

INSERT IGNORE INTO permissions (name, display_name, description, module, action, is_system) VALUES
('clients.view_all','Ver todos los clientes','Permite ver clientes asignados a cualquier usuario','clients','view_all', TRUE);

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT (SELECT id FROM roles WHERE name = 'admin' LIMIT 1), p.id FROM permissions p WHERE p.name = 'clients.view_all';
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT (SELECT id FROM roles WHERE name = 'manager' LIMIT 1), p.id FROM permissions p WHERE p.name = 'clients.view_all';

INSERT IGNORE INTO permissions (name, display_name, description, module, action, is_system) VALUES
('clients.manual_deposit','Registrar depósito manual','Permite registrar depósitos manuales en cuentas de cliente','clients','manual_deposit', TRUE);

INSERT IGNORE INTO permissions (name, display_name, description, module, action, is_system) VALUES
('clients.create_account','Crear cuenta de cliente','Permite crear cuentas para clientes','clients','create_account', TRUE);
INSERT IGNORE INTO permissions (name, display_name, description, module, action, is_system) VALUES
('clients.impersonate','Acceder como cliente','Permite abrir Trader Room autenticando al cliente','clients','impersonate', TRUE);

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT (SELECT id FROM roles WHERE name = 'admin' LIMIT 1), p.id FROM permissions p WHERE p.name = 'clients.manual_deposit';
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT (SELECT id FROM roles WHERE name = 'admin' LIMIT 1), p.id FROM permissions p WHERE p.name = 'clients.create_account';
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT (SELECT id FROM roles WHERE name = 'admin' LIMIT 1), p.id FROM permissions p WHERE p.name = 'clients.impersonate';

-- Asignar a manager
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT (SELECT id FROM roles WHERE name = 'manager' LIMIT 1), p.id FROM permissions p WHERE p.name = 'clients.manual_deposit';
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT (SELECT id FROM roles WHERE name = 'manager' LIMIT 1), p.id FROM permissions p WHERE p.name = 'clients.create_account';
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT (SELECT id FROM roles WHERE name = 'manager' LIMIT 1), p.id FROM permissions p WHERE p.name = 'clients.impersonate';
INSERT IGNORE INTO permissions (name, display_name, description, module, action, is_system) VALUES
('clients.change_password','Cambiar contraseña de cliente','Permite cambiar la contraseña del portal/trader room','clients','change_password',FALSE),
('clients.manual_deposit','Registrar depósito manual','Permite registrar depósitos manuales en cuentas','clients','manual_deposit',FALSE),
('clients.change_status','Cambiar estado del cliente','Permite actualizar el estado del cliente','clients','change_status',FALSE),
('checkout.key2pay.list_methods','Listar métodos Key2Pay','Permite listar métodos de pago de Key2Pay','checkout.key2pay','list_methods',FALSE),
('checkout.key2pay.create_checkout','Crear checkout Key2Pay','Permite crear página de checkout en Key2Pay','checkout.key2pay','create_checkout',FALSE),
('checkout.key2pay.ensure_token','Asegurar token Key2Pay','Permite generar/asegurar token de acceso','checkout.key2pay','ensure_token',FALSE),
('checkout.key2pay.create_payment','Crear pago Key2Pay','Permite crear pagos a través de Key2Pay','checkout.key2pay','create_payment',FALSE),
('checkout.key2pay.create_payment_raw','Crear pago Key2Pay (raw)','Permite crear pagos raw a través de Key2Pay','checkout.key2pay','create_payment_raw',FALSE),
('checkout.big4pay.initiate_checkout','Iniciar checkout Big4pay','Permite iniciar el checkout de Big4pay','checkout.big4pay','initiate_checkout',FALSE);

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT (SELECT id FROM roles WHERE name = 'admin' LIMIT 1), p.id
FROM permissions p
WHERE p.name IN (
  'clients.change_password',
  'clients.manual_deposit',
  'clients.change_status',
  'checkout.key2pay.list_methods',
  'checkout.key2pay.create_checkout',
  'checkout.key2pay.ensure_token',
  'checkout.key2pay.create_payment',
  'checkout.key2pay.create_payment_raw',
  'checkout.big4pay.initiate_checkout'
);

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT (SELECT id FROM roles WHERE name = 'manager' LIMIT 1), p.id
FROM permissions p
WHERE p.name IN (
  'clients.change_password',
  'clients.manual_deposit',
  'clients.change_status'
);
INSERT IGNORE INTO user_roles (user_id, role_id, assigned_at)
SELECT u.id, r.id, NOW() FROM users u, roles r 
WHERE u.username = 'admin' AND r.name = 'admin';

-- =====================================================
-- SCRIPT COMPLETADO
-- =====================================================
-- Este script ha creado todas las tablas necesarias,
-- insertado los datos iniciales y configurado los permisos.
-- El sistema está listo para usar con:
-- Usuario: admin
-- Contraseña: password
-- =====================================================
