-- Schema para Simple CRM
-- Base de datos: simple_crm

CREATE DATABASE IF NOT EXISTS simple_crm CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE simple_crm;

-- 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,
    failed_login_attempts INT DEFAULT 0,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_status (status)
);

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

-- Permissions table
CREATE TABLE IF NOT EXISTS permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    display_name VARCHAR(150) NOT NULL,
    description TEXT,
    module VARCHAR(50) NOT NULL,
    action VARCHAR(50) NOT NULL,
    is_system BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_module_action (module, action)
);

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

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

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

-- Tabla de relación escritorios-usuarios
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)
);

-- 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,
    position INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_desk_status (desk_id, status),
    FOREIGN KEY (desk_id) REFERENCES desks(id) ON DELETE CASCADE
);

-- Tabla de leads (actualizada para Forex/CFD)
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),
    country VARCHAR(50),
    city VARCHAR(100),
    company VARCHAR(100),
    job_title VARCHAR(100),
    source VARCHAR(50),
    status VARCHAR(50) DEFAULT 'new',
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    assigned_to INT NULL,
    desk_id INT NULL,
    notes TEXT,
    
    -- Campos específicos para Forex/CFD
    trading_experience ENUM('none', 'beginner', 'intermediate', 'advanced', 'professional') DEFAULT 'none',
    investment_amount DECIMAL(15,2) DEFAULT 0.00,
    risk_tolerance ENUM('low', 'medium', 'high') DEFAULT 'medium',
    preferred_instruments VARCHAR(255), -- JSON: ["forex", "indices", "commodities", "crypto"]
    account_type ENUM('demo', 'live', 'vip', 'professional') DEFAULT 'demo',
    deposit_amount DECIMAL(15,2) DEFAULT 0.00,
    deposit_currency VARCHAR(3) DEFAULT 'USD',
    last_contact_date DATETIME NULL,
    next_followup_date DATETIME NULL,
    conversion_probability INT DEFAULT 0, -- 0-100%
    lead_score INT DEFAULT 0, -- 0-1000 points
    utm_source VARCHAR(100),
    utm_medium VARCHAR(100),
    utm_campaign VARCHAR(100),
    referral_code VARCHAR(50),
    ip_address VARCHAR(45),
    user_agent TEXT,
    
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    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_priority (priority),
    INDEX idx_assigned_to (assigned_to),
    INDEX idx_desk_id (desk_id),
    INDEX idx_created_at (created_at),
    INDEX idx_trading_experience (trading_experience),
    INDEX idx_account_type (account_type),
    INDEX idx_lead_score (lead_score),
    INDEX idx_next_followup (next_followup_date)
);

-- 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 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 (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),
    INDEX idx_created_at (created_at)
);

-- Insertar datos iniciales

-- Roles básicos
INSERT INTO roles (name, display_name, description) VALUES
('admin', 'Administrador', 'Acceso completo al sistema'),
('manager', 'Manager', 'Gestión de escritorios y usuarios'),
('agent', 'Agente', 'Gestión de leads asignados'),
('viewer', 'Visualizador', 'Solo lectura del sistema');

-- Permisos básicos
INSERT INTO permissions (name, display_name, module, action) VALUES
('users.view', 'Ver Usuarios', 'users', 'view'),
('users.create', 'Crear Usuarios', 'users', 'create'),
('users.edit', 'Editar Usuarios', 'users', 'edit'),
('users.delete', 'Eliminar Usuarios', 'users', 'delete'),
('leads.view', 'Ver Leads', 'leads', 'view'),
('leads.create', 'Crear Leads', 'leads', 'create'),
('leads.edit', 'Editar Leads', 'leads', 'edit'),
('leads.delete', 'Eliminar Leads', 'leads', 'delete'),
('desks.view', 'Ver Escritorios', 'desks', 'view'),
('desks.create', 'Crear Escritorios', 'desks', 'create'),
('desks.edit', 'Editar Escritorios', 'desks', 'edit'),
('desks.delete', 'Eliminar Escritorios', 'desks', 'delete'),
('dashboard.view', 'Ver Dashboard', 'dashboard', 'view');

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

-- Manager: gestión de leads, desks y dashboard
INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p 
WHERE r.name = 'manager' AND p.module IN ('leads', 'desks', 'dashboard');

-- Agent: gestión de leads y dashboard
INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p 
WHERE r.name = 'agent' AND p.module IN ('leads', 'dashboard') AND p.action IN ('view', 'create', 'edit');

-- Viewer: solo lectura
INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p 
WHERE r.name = 'viewer' AND p.action = 'view';

-- Usuario administrador por defecto
INSERT INTO users (username, email, first_name, last_name, password_hash, status, created_at) VALUES
('admin', 'admin@simplecrm.com', 'Admin', 'Sistema', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'active', NOW());

-- Asignar rol admin al usuario admin
INSERT INTO user_roles (user_id, role_id)
SELECT u.id, r.id FROM users u, roles r WHERE u.username = 'admin' AND r.name = 'admin';

-- Escritorio por defecto
INSERT INTO desks (name, description, status, created_at) VALUES
('General', 'Escritorio general del sistema', 'active', NOW());