-- ===========================================
-- SOFTEDGE CORPORATION - BANCO DE DADOS
-- MySQL 8.0+ Professional Schema
-- Versão: 2.0.0 | Data: 2025
-- Compatibilidade: Railway, Render, Docker
-- Segurança: Máxima | Robustez: Empresarial
-- ===========================================

-- Configurações globais de segurança e performance
SET SQL_MODE = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
SET AUTOCOMMIT = 1;
SET GLOBAL innodb_buffer_pool_size = 134217728; -- 128MB
SET GLOBAL innodb_log_file_size = 33554432;     -- 32MB
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL max_connections = 1000;
SET GLOBAL wait_timeout = 28800;
SET GLOBAL interactive_timeout = 28800;

-- ===========================================
-- BANCO DE DADOS PRINCIPAL
-- ===========================================
CREATE DATABASE IF NOT EXISTS softedge_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci
DEFAULT ENCRYPTION = 'Y';

USE softedge_db;

-- ===========================================
-- TABELA: system_config (Configurações do Sistema)
-- ===========================================
CREATE TABLE IF NOT EXISTS system_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    config_key VARCHAR(100) UNIQUE NOT NULL,
    config_value TEXT,
    config_type ENUM('string', 'int', 'boolean', 'json') DEFAULT 'string',
    is_system BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_config_key (config_key),
    INDEX idx_system (is_system)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================
-- TABELA: users (Usuários - Sistema Completo)
-- ===========================================
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) UNIQUE NOT NULL DEFAULT (UUID()),
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255),
    avatar VARCHAR(500),
    bio TEXT,
    phone VARCHAR(20),
    company VARCHAR(255),
    website VARCHAR(255),
    location VARCHAR(255),

    -- Autenticação
    provider ENUM('local', 'google', 'github', 'linkedin') DEFAULT 'local',
    provider_id VARCHAR(255),
    provider_data JSON,

    -- Status e Permissões
    role ENUM('user', 'moderator', 'admin', 'super_admin') DEFAULT 'user',
    status ENUM('active', 'inactive', 'suspended', 'banned') DEFAULT 'active',
    email_verified BOOLEAN DEFAULT FALSE,
    phone_verified BOOLEAN DEFAULT FALSE,

    -- Tokens de Segurança
    verification_token VARCHAR(255),
    reset_token VARCHAR(255),
    reset_expires DATETIME,
    email_change_token VARCHAR(255),
    email_change_expires DATETIME,

    -- Metadados
    last_login DATETIME,
    last_ip VARCHAR(45),
    login_attempts INT DEFAULT 0,
    locked_until DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,

    -- Índices de Performance
    INDEX idx_uuid (uuid),
    INDEX idx_email (email),
    INDEX idx_provider (provider, provider_id),
    INDEX idx_role (role),
    INDEX idx_status (status),
    INDEX idx_email_verified (email_verified),
    INDEX idx_created_at (created_at),
    INDEX idx_deleted_at (deleted_at),
    INDEX idx_last_login (last_login),

    -- Full-text search
    FULLTEXT idx_fulltext_name (name),
    FULLTEXT idx_fulltext_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================
-- TABELA: user_sessions (Sessões de Usuário)
-- ===========================================
CREATE TABLE IF NOT EXISTS user_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(255) UNIQUE NOT NULL,
    user_id INT NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT,
    device_info JSON,
    location_data JSON,
    expires_at DATETIME NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_session_id (session_id),
    INDEX idx_user_id (user_id),
    INDEX idx_expires_at (expires_at),
    INDEX idx_is_active (is_active),
    INDEX idx_last_activity (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================
-- TABELA: user_profiles (Perfis Detalhados)
-- ===========================================
CREATE TABLE IF NOT EXISTS user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNIQUE NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    display_name VARCHAR(255),
    gender ENUM('male', 'female', 'other', 'prefer_not_to_say'),
    date_of_birth DATE,
    timezone VARCHAR(50) DEFAULT 'Africa/Luanda',
    language VARCHAR(10) DEFAULT 'pt-BR',
    preferences JSON,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_display_name (display_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================
-- TABELA: page_visits (Analytics Avançado)
-- ===========================================
CREATE TABLE IF NOT EXISTS page_visits (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    visit_id CHAR(36) NOT NULL DEFAULT (UUID()),
    user_id INT,
    session_id VARCHAR(255),
    page_url VARCHAR(2000) NOT NULL,
    page_title VARCHAR(500),
    referrer_url VARCHAR(2000),
    referrer_domain VARCHAR(255),

    -- Dados Técnicos
    ip_address VARCHAR(45),
    user_agent TEXT,
    device_type ENUM('desktop', 'mobile', 'tablet', 'bot'),
    browser VARCHAR(100),
    os VARCHAR(100),
    screen_resolution VARCHAR(20),

    -- Geolocalização
    country VARCHAR(100),
    region VARCHAR(100),
    city VARCHAR(100),
    latitude DECIMAL(10,8),
    longitude DECIMAL(11,8),

    -- Métricas
    time_on_page INT DEFAULT 0, -- segundos
    scroll_depth DECIMAL(5,2) DEFAULT 0, -- porcentagem
    is_bounce BOOLEAN DEFAULT FALSE,

    -- Marketing
    utm_source VARCHAR(100),
    utm_medium VARCHAR(100),
    utm_campaign VARCHAR(100),
    utm_term VARCHAR(100),
    utm_content VARCHAR(100),

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (session_id) REFERENCES user_sessions(session_id) ON DELETE SET NULL,

    -- Índices de Performance
    INDEX idx_user_id (user_id),
    INDEX idx_session_id (session_id),
    INDEX idx_page_url (page_url(255)),
    INDEX idx_created_at (created_at),
    INDEX idx_ip_address (ip_address),
    INDEX idx_device_type (device_type),
    INDEX idx_country (country),
    INDEX idx_utm_source (utm_source),
    INDEX idx_utm_campaign (utm_campaign),

    -- Índices compostos para queries complexas
    INDEX idx_user_date (user_id, created_at),
    INDEX idx_page_date (page_url(255), created_at),
    INDEX idx_session_date (session_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- ===========================================
-- TABELA: contact_submissions (Formulários de Contato)
-- ===========================================
CREATE TABLE IF NOT EXISTS contact_submissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id CHAR(36) NOT NULL DEFAULT (UUID()),
    user_id INT,

    -- Dados do Contato
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    company VARCHAR(255),
    subject VARCHAR(500),
    message TEXT NOT NULL,
    priority ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',

    -- Metadados
    ip_address VARCHAR(45),
    user_agent TEXT,
    referrer VARCHAR(500),
    source ENUM('website', 'landing_page', 'social', 'referral', 'direct') DEFAULT 'website',

    -- Status e Gestão
    status ENUM('new', 'read', 'in_progress', 'waiting_response', 'replied', 'closed', 'spam') DEFAULT 'new',
    assigned_to INT,
    tags JSON,
    notes TEXT,

    -- Respostas
    response_count INT DEFAULT 0,
    last_response_at DATETIME,
    first_response_at DATETIME,

    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    responded_at TIMESTAMP NULL,
    closed_at TIMESTAMP NULL,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,

    -- Índices
    INDEX idx_submission_id (submission_id),
    INDEX idx_user_id (user_id),
    INDEX idx_email (email),
    INDEX idx_status (status),
    INDEX idx_priority (priority),
    INDEX idx_assigned_to (assigned_to),
    INDEX idx_created_at (created_at),
    INDEX idx_source (source),

    -- Full-text search
    FULLTEXT idx_fulltext_message (message),
    FULLTEXT idx_fulltext_subject (subject)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================
-- TABELA: contact_responses (Respostas aos Contatos)
-- ===========================================
CREATE TABLE IF NOT EXISTS contact_responses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    response_id CHAR(36) NOT NULL DEFAULT (UUID()),
    submission_id INT NOT NULL,
    responder_id INT NOT NULL,
    response TEXT NOT NULL,
    is_internal BOOLEAN DEFAULT FALSE,
    attachments JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (submission_id) REFERENCES contact_submissions(id) ON DELETE CASCADE,
    FOREIGN KEY (responder_id) REFERENCES users(id) ON DELETE CASCADE,

    INDEX idx_submission_id (submission_id),
    INDEX idx_responder_id (responder_id),
    INDEX idx_created_at (created_at),
    INDEX idx_is_internal (is_internal)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================
-- TABELA: email_logs (Logs de Email)
-- ===========================================
CREATE TABLE IF NOT EXISTS email_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    email_id CHAR(36) NOT NULL DEFAULT (UUID()),
    recipient_email VARCHAR(255) NOT NULL,
    recipient_name VARCHAR(255),
    subject VARCHAR(500) NOT NULL,
    template VARCHAR(100),
    status ENUM('sent', 'delivered', 'opened', 'clicked', 'bounced', 'complained', 'failed') DEFAULT 'sent',
    provider VARCHAR(50) DEFAULT 'phpmailer',
    provider_message_id VARCHAR(255),
    error_message TEXT,
    metadata JSON,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    delivered_at TIMESTAMP NULL,
    opened_at TIMESTAMP NULL,
    clicked_at TIMESTAMP NULL,

    INDEX idx_email_id (email_id),
    INDEX idx_recipient_email (recipient_email),
    INDEX idx_status (status),
    INDEX idx_provider (provider),
    INDEX idx_sent_at (sent_at),
    INDEX idx_template (template)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================
-- TABELA: security_logs (Logs de Segurança)
-- ===========================================
CREATE TABLE IF NOT EXISTS security_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    log_id CHAR(36) NOT NULL DEFAULT (UUID()),
    user_id INT,
    event_type ENUM('login', 'logout', 'failed_login', 'password_change', 'email_change', 'suspicious_activity', 'rate_limit', 'csrf_attempt', 'sql_injection_attempt', 'xss_attempt') NOT NULL,
    severity ENUM('low', 'medium', 'high', 'critical') DEFAULT 'low',
    ip_address VARCHAR(45),
    user_agent TEXT,
    location_data JSON,
    event_data JSON,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,

    INDEX idx_user_id (user_id),
    INDEX idx_event_type (event_type),
    INDEX idx_severity (severity),
    INDEX idx_created_at (created_at),
    INDEX idx_ip_address (ip_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================
-- TABELA: rate_limits (Controle de Taxa)
-- ===========================================
CREATE TABLE IF NOT EXISTS rate_limits (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    identifier VARCHAR(255) NOT NULL, -- IP, user_id, email, etc.
    action VARCHAR(100) NOT NULL, -- login, contact_form, api_call, etc.
    attempts INT DEFAULT 1,
    window_start TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    window_end TIMESTAMP NOT NULL,
    blocked_until TIMESTAMP NULL,

    INDEX idx_identifier_action (identifier, action),
    INDEX idx_window_end (window_end),
    INDEX idx_blocked_until (blocked_until),
    INDEX idx_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===========================================
-- DADOS INICIAIS DO SISTEMA
-- ===========================================

-- Configurações do sistema
INSERT IGNORE INTO system_config (config_key, config_value, config_type, is_system) VALUES
('site_name', 'SoftEdge Corporation', 'string', TRUE),
('site_url', 'https://softedge-corporation.up.railway.app', 'string', TRUE),
('admin_email', 'admin@softedge.com', 'string', TRUE),
('support_email', 'support@softedge.com', 'string', TRUE),
('timezone', 'Africa/Luanda', 'string', TRUE),
('language', 'pt-BR', 'string', TRUE),
('maintenance_mode', 'false', 'boolean', TRUE),
('registration_enabled', 'true', 'boolean', TRUE),
('email_verification_required', 'true', 'boolean', TRUE),
('max_login_attempts', '5', 'int', TRUE),
('lockout_duration', '900', 'int', TRUE), -- 15 minutos
('session_lifetime', '86400', 'int', TRUE), -- 24 horas
('rate_limit_login', '5', 'int', TRUE), -- 5 tentativas por hora
('rate_limit_contact', '3', 'int', TRUE), -- 3 formulários por hora
('analytics_retention_days', '365', 'int', TRUE);

-- Usuário administrador principal
INSERT IGNORE INTO users (
    name, email, password, role, status, email_verified,
    phone, company, bio, created_at
) VALUES (
    'Isaac Quarenta',
    'admin@softedge.com',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
    'super_admin',
    'active',
    TRUE,
    '+244 923 456 789',
    'SoftEdge Corporation',
    'CEO e Fundador da SoftEdge Corporation. Especialista em desenvolvimento de software e soluções tecnológicas.',
    NOW()
);

-- Usuário administrador secundário
INSERT IGNORE INTO users (
    name, email, password, role, status, email_verified,
    phone, company, created_at
) VALUES (
    'José Lopes',
    'jose@softedge.com',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
    'admin',
    'active',
    TRUE,
    '+244 923 456 790',
    'SoftEdge Corporation',
    NOW()
);

-- Usuários de teste
INSERT IGNORE INTO users (
    name, email, password, role, status, email_verified, created_at
) VALUES
('Tiago Rodrigues', 'tiago@softedge.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'active', TRUE, NOW()),
('Stefânio Costa', 'stefanio@softedge.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'moderator', 'active', TRUE, NOW()),
('João Silva', 'joao@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'user', 'active', TRUE, NOW()),
('Maria Santos', 'maria@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'user', 'active', TRUE, NOW());

-- Perfis detalhados para usuários principais
INSERT IGNORE INTO user_profiles (
    user_id, first_name, last_name, display_name, timezone, language, preferences
) SELECT
    id,
    SUBSTRING_INDEX(name, ' ', 1),
    SUBSTRING(name, LOCATE(' ', name) + 1),
    name,
    'Africa/Luanda',
    'pt-BR',
    '{"theme": "dark", "notifications": true, "language": "pt-BR"}'
FROM users WHERE role IN ('super_admin', 'admin');

-- ===========================================
-- TRIGGERS DE SEGURANÇA E AUTOMATIZAÇÃO
-- ===========================================

-- Trigger para log de segurança em tentativas de login falhadas
DELIMITER ;;
CREATE TRIGGER IF NOT EXISTS trg_failed_login_log
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF NEW.login_attempts > OLD.login_attempts AND NEW.login_attempts >= 3 THEN
        INSERT INTO security_logs (
            user_id, event_type, severity, ip_address, description, event_data
        ) VALUES (
            NEW.id,
            'failed_login',
            CASE
                WHEN NEW.login_attempts >= 5 THEN 'high'
                ELSE 'medium'
            END,
            NEW.last_ip,
            CONCAT('Múltiplas tentativas de login falhadas: ', NEW.login_attempts),
            JSON_OBJECT('attempts', NEW.login_attempts, 'last_attempt', NOW())
        );
    END IF;
END;;
DELIMITER ;

-- Trigger para atualizar estatísticas de contato
DELIMITER ;;
CREATE TRIGGER IF NOT EXISTS trg_contact_response_update
AFTER INSERT ON contact_responses
FOR EACH ROW
BEGIN
    UPDATE contact_submissions
    SET
        response_count = response_count + 1,
        last_response_at = NOW(),
        first_response_at = COALESCE(first_response_at, NOW()),
        status = CASE
            WHEN status = 'new' THEN 'in_progress'
            WHEN status = 'waiting_response' THEN 'replied'
            ELSE status
        END,
        updated_at = NOW()
    WHERE id = NEW.submission_id;
END;;
DELIMITER ;

-- ===========================================
-- VIEWS PARA DASHBOARD ADMINISTRATIVO
-- ===========================================

-- View: Estatísticas gerais do sistema
CREATE OR REPLACE VIEW system_stats AS
SELECT
    (SELECT COUNT(*) FROM users WHERE status = 'active') as total_users,
    (SELECT COUNT(*) FROM users WHERE DATE(created_at) = CURDATE()) as new_users_today,
    (SELECT COUNT(*) FROM page_visits WHERE DATE(created_at) = CURDATE()) as visits_today,
    (SELECT COUNT(*) FROM contact_submissions WHERE status = 'new') as pending_contacts,
    (SELECT COUNT(*) FROM security_logs WHERE DATE(created_at) = CURDATE() AND severity IN ('high', 'critical')) as security_alerts_today;

-- View: Usuários mais ativos
CREATE OR REPLACE VIEW active_users AS
SELECT
    u.id,
    u.name,
    u.email,
    u.last_login,
    COUNT(pv.id) as total_visits,
    COUNT(DISTINCT DATE(pv.created_at)) as active_days,
    MAX(pv.created_at) as last_activity
FROM users u
LEFT JOIN page_visits pv ON u.id = pv.user_id
WHERE u.status = 'active'
GROUP BY u.id, u.name, u.email, u.last_login
ORDER BY last_activity DESC;

-- View: Analytics de páginas
CREATE OR REPLACE VIEW page_analytics AS
SELECT
    page_url,
    COUNT(*) as total_visits,
    COUNT(DISTINCT user_id) as unique_visitors,
    COUNT(DISTINCT session_id) as sessions,
    AVG(time_on_page) as avg_time_on_page,
    AVG(scroll_depth) as avg_scroll_depth,
    SUM(CASE WHEN is_bounce THEN 1 ELSE 0 END) as bounces,
    ROUND(
        (SUM(CASE WHEN is_bounce THEN 1 ELSE 0 END) / COUNT(*)) * 100, 2
    ) as bounce_rate
FROM page_visits
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY page_url
ORDER BY total_visits DESC;

-- ===========================================
-- PROCEDURES ARMAZENADAS PARA OPERAÇÕES COMUNS
-- ===========================================

DELIMITER ;;

-- Procedure: Limpar sessões expiradas
CREATE PROCEDURE IF NOT EXISTS cleanup_expired_sessions()
BEGIN
    DELETE FROM user_sessions WHERE expires_at < NOW();
    DELETE FROM rate_limits WHERE window_end < NOW();
END;;

-- Procedure: Resetar tentativas de login
CREATE PROCEDURE IF NOT EXISTS reset_login_attempts(IN user_email VARCHAR(255))
BEGIN
    UPDATE users
    SET login_attempts = 0, locked_until = NULL
    WHERE email = user_email;
END;;

-- Procedure: Obter estatísticas do usuário
CREATE PROCEDURE IF NOT EXISTS get_user_stats(IN user_id_param INT)
BEGIN
    SELECT
        u.name,
        u.email,
        u.created_at as member_since,
        COUNT(DISTINCT pv.page_url) as pages_visited,
        COUNT(pv.id) as total_visits,
        MAX(pv.created_at) as last_activity,
        COUNT(cs.id) as contacts_submitted
    FROM users u
    LEFT JOIN page_visits pv ON u.id = pv.user_id
    LEFT JOIN contact_submissions cs ON u.id = cs.user_id
    WHERE u.id = user_id_param
    GROUP BY u.id, u.name, u.email, u.created_at;
END;;

DELIMITER ;

-- ===========================================
-- PERMISSÕES E SEGURANÇA FINAL
-- ===========================================

-- Criar usuário limitado para aplicação (se necessário)
-- GRANT SELECT, INSERT, UPDATE, DELETE ON softedge_db.* TO 'softedge_app'@'localhost' IDENTIFIED BY 'secure_password_here';
-- FLUSH PRIVILEGES;

-- Otimizações finais
ANALYZE TABLE users, user_sessions, page_visits, contact_submissions, security_logs;

-- ===========================================
-- FIM DO SCHEMA - SOFTEDGE CORPORATION
-- Total de Tabelas: 10
-- Total de Índices: 45+
-- Segurança: Máxima
-- Performance: Otimizada
-- Compatibilidade: Empresarial
-- ===========================================