-- Script para actualizar el esquema existente
-- Agregar nuevas columnas a la tabla roles si no existen

ALTER TABLE roles 
ADD COLUMN IF NOT EXISTS description TEXT,
ADD COLUMN IF NOT EXISTS is_system BOOLEAN DEFAULT FALSE,
ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT TRUE,
ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- Agregar nuevas columnas a la tabla permissions si no existen
ALTER TABLE permissions 
ADD COLUMN IF NOT EXISTS description TEXT,
ADD COLUMN IF NOT EXISTS module VARCHAR(50) NOT NULL DEFAULT 'general',
ADD COLUMN IF NOT EXISTS action VARCHAR(50) NOT NULL DEFAULT 'view',
ADD COLUMN IF NOT EXISTS is_system BOOLEAN DEFAULT FALSE,
ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- Agregar índice si no existe
ALTER TABLE permissions ADD INDEX IF NOT EXISTS idx_module_action (module, action);

-- Actualizar roles existentes para marcarlos como del sistema
UPDATE roles SET is_system = TRUE WHERE name IN ('admin', 'user', 'manager');
UPDATE roles SET is_active = TRUE WHERE is_active IS NULL;

-- Actualizaciones de tabla leads (agregar columnas faltantes si no existen)
ALTER TABLE leads 
ADD COLUMN IF NOT EXISTS city VARCHAR(100) NULL AFTER country;

ALTER TABLE leads 
ADD COLUMN IF NOT EXISTS investment_amount DECIMAL(15,2) DEFAULT 0.00 AFTER trading_experience;

-- Asegurar que el rol Admin tenga TODOS los permisos (incluidos nuevos)
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT (SELECT id FROM roles WHERE name = 'admin' LIMIT 1), p.id FROM permissions p;

-- Usuarios: agregar columna para extensión Voiso si no existe
ALTER TABLE users 
    ADD COLUMN IF NOT EXISTS voiso_extension VARCHAR(50) NULL AFTER updated_at;
-- Clientes: esquema inicial

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,
    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,
    INDEX idx_email (email),
    INDEX idx_status (status)
);

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)
);

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)
);

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)
);

-- Estado 'converted' para leads (no requiere alter si es VARCHAR)
-- Las interfaces deben filtrar leads con status = 'converted'

-- Actualización: añadir métricas financieras y desk en clients
ALTER TABLE clients 
    ADD COLUMN IF NOT EXISTS desk_id INT NULL,
    ADD COLUMN IF NOT EXISTS balance DECIMAL(18,2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS equity DECIMAL(18,2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS margin_percent DECIMAL(7,4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS pnl DECIMAL(18,2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS last_seen DATETIME NULL,
    ADD INDEX idx_clients_desk (desk_id);

ALTER TABLE clients
    ADD CONSTRAINT fk_clients_desk FOREIGN KEY (desk_id) REFERENCES desks(id) ON DELETE SET NULL;

-- Asignado a (cliente): almacenar 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 presencia de clientes
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;

-- 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)
);

-- Tickets de soporte de clientes
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)
);

-- Mensajes de tickets (hilo de conversación)
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)
);

-- Permisos para Documentos (KYC) y rol de Soporte
INSERT IGNORE INTO permissions (name, display_name, description, module, action, is_system)
VALUES
('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);

-- Crear rol "support" si no existe
INSERT IGNORE INTO roles (name, display_name, description, is_system, is_active)
VALUES ('support', 'Soporte', 'Gestión de tickets y revisión de documentos KYC', FALSE, TRUE);

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

-- Permisos para Tickets (staff)
INSERT IGNORE INTO permissions (name, display_name, description, module, action, is_system)
VALUES
('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);

-- Asignar permisos de tickets al rol support
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT (SELECT id FROM roles WHERE name = 'support' LIMIT 1), p.id
FROM permissions p
WHERE p.name IN ('tickets.view','tickets.reply');

-- Permisos de menú para Clientes y Tickets
INSERT IGNORE INTO permissions (name, display_name, description, module, action, is_system)
VALUES
('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);

-- Eliminar visibilidad de menú para rol support (solo admin debe ver)
DELETE FROM role_permissions
WHERE role_id = (SELECT id FROM roles WHERE name = 'support' LIMIT 1)
  AND permission_id IN (SELECT id FROM permissions WHERE name IN ('menu.tickets.view','menu.clients.view'));

-- Añadir columna duration_min en lead_activities si no existe
ALTER TABLE lead_activities 
    ADD COLUMN IF NOT EXISTS duration_min INT NULL AFTER scheduled_at;

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 role_permissions (role_id, permission_id)
SELECT (SELECT id FROM roles WHERE name = 'sales_rep' LIMIT 1), p.id
FROM permissions p
WHERE p.name IN (
  'checkout.key2pay.list_methods',
  'checkout.key2pay.create_checkout',
  'checkout.key2pay.ensure_token',
  'checkout.key2pay.create_payment',
  'checkout.key2pay.create_payment_raw'
);

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';

-- Cuentas bancarias: asegurar columnas opcionales en client_accounts
ALTER TABLE client_accounts 
    ADD COLUMN IF NOT EXISTS iban VARCHAR(64) NULL,
    ADD COLUMN IF NOT EXISTS swift VARCHAR(16) NULL,
    ADD COLUMN IF NOT EXISTS bank_name VARCHAR(128) NULL,
    ADD COLUMN IF NOT EXISTS bank_country VARCHAR(8) NULL;

-- 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;
