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

-- ===========================================