database.sql

��-- ===========================================
-- 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
-- ===========================================