desk-moloni/scripts/create_phase2_tables.sql

-- =================================================================
-- PHASE 2 DATABASE SETUP SCRIPT - desk-moloni Web Interface
-- =================================================================
-- 
-- Purpose: Create additional database tables required for Phase 2 web interface
-- Target: MySQL 8.0+ / MariaDB 10.6+
-- Encoding: UTF-8
-- Author: Emanuel Almeida
-- Date: 2025-09-12
-- 
-- Prerequisites:
--   - Core desk-moloni database already exists
--   - Core sync tables (sync_mappings, sync_operations, sync_config) already created
--   - User has CREATE, ALTER, INSERT privileges
-- 
-- Usage:
--   mysql -u deskcrm_user -p desk_moloni < scripts/create_phase2_tables.sql
-- 
-- =================================================================

USE desk_moloni;

-- Enable UTF-8 support for all tables
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 1;

-- =================================================================
-- TABLE: sync_dashboard_stats
-- Purpose: Store aggregated statistics for dashboard display
-- =================================================================

CREATE TABLE IF NOT EXISTS sync_dashboard_stats (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    stat_date DATE NOT NULL,
    total_syncs INT UNSIGNED DEFAULT 0 COMMENT 'Total sync operations for the day',
    successful_syncs INT UNSIGNED DEFAULT 0 COMMENT 'Successfully completed sync operations',
    failed_syncs INT UNSIGNED DEFAULT 0 COMMENT 'Failed sync operations',
    avg_response_time DECIMAL(10,3) UNSIGNED DEFAULT 0.000 COMMENT 'Average response time in seconds',
    peak_response_time DECIMAL(10,3) UNSIGNED DEFAULT 0.000 COMMENT 'Highest response time in seconds',
    total_records_processed INT UNSIGNED DEFAULT 0 COMMENT 'Total records processed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes for optimal query performance
    UNIQUE KEY uk_stat_date (stat_date),
    INDEX idx_created_at (created_at),
    INDEX idx_success_rate (successful_syncs, total_syncs)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Dashboard statistics aggregated daily for performance monitoring';

-- =================================================================
-- TABLE: admin_users
-- Purpose: User authentication and authorization for web interface
-- =================================================================

CREATE TABLE IF NOT EXISTS admin_users (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL COMMENT 'Unique username for login',
    email VARCHAR(100) NOT NULL COMMENT 'User email address',
    password_hash VARCHAR(255) NOT NULL COMMENT 'Argon2ID password hash',
    full_name VARCHAR(100) DEFAULT NULL COMMENT 'User display name',
    is_active BOOLEAN DEFAULT TRUE COMMENT 'Account active status',
    is_superuser BOOLEAN DEFAULT FALSE COMMENT 'Super administrator privileges',
    last_login TIMESTAMP NULL DEFAULT NULL COMMENT 'Last successful login timestamp',
    login_attempts INT UNSIGNED DEFAULT 0 COMMENT 'Failed login attempts counter',
    lockout_until TIMESTAMP NULL DEFAULT NULL COMMENT 'Account lockout expiration',
    password_changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Last password change',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Constraints and indexes
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email (email),
    INDEX idx_is_active (is_active),
    INDEX idx_last_login (last_login),
    INDEX idx_lockout_until (lockout_until)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Administrative users for web interface authentication';

-- =================================================================
-- TABLE: user_sessions
-- Purpose: Secure session management for authenticated users
-- =================================================================

CREATE TABLE IF NOT EXISTS user_sessions (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    session_token VARCHAR(128) NOT NULL COMMENT 'Secure session token (SHA-256)',
    session_data TEXT DEFAULT NULL COMMENT 'Serialized session data',
    expires_at TIMESTAMP NOT NULL COMMENT 'Session expiration timestamp',
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ip_address VARCHAR(45) DEFAULT NULL COMMENT 'Client IP address (IPv4/IPv6)',
    user_agent TEXT DEFAULT NULL COMMENT 'Client user agent string',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Constraints and indexes
    UNIQUE KEY uk_session_token (session_token),
    FOREIGN KEY fk_user_sessions_user_id (user_id) REFERENCES admin_users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_expires_at (expires_at),
    INDEX idx_last_activity (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Active user sessions for web interface authentication';

-- =================================================================
-- TABLE: sync_schedules  
-- Purpose: Automated sync scheduling and cron job management
-- =================================================================

CREATE TABLE IF NOT EXISTS sync_schedules (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    schedule_name VARCHAR(100) NOT NULL COMMENT 'Human-readable schedule name',
    description TEXT DEFAULT NULL COMMENT 'Schedule description',
    cron_expression VARCHAR(100) NOT NULL COMMENT 'Cron format schedule expression',
    entity_type VARCHAR(50) NOT NULL COMMENT 'Entity type to sync (customers, invoices, payments)',
    sync_direction ENUM('deskcrm_to_moloni', 'moloni_to_deskcrm', 'bidirectional') DEFAULT 'bidirectional',
    is_active BOOLEAN DEFAULT TRUE COMMENT 'Schedule enabled status',
    batch_size INT UNSIGNED DEFAULT 100 COMMENT 'Records per batch',
    max_retries INT UNSIGNED DEFAULT 3 COMMENT 'Maximum retry attempts on failure',
    last_run TIMESTAMP NULL DEFAULT NULL COMMENT 'Last execution timestamp',
    next_run TIMESTAMP NULL DEFAULT NULL COMMENT 'Next scheduled execution',
    last_success TIMESTAMP NULL DEFAULT NULL COMMENT 'Last successful execution',
    consecutive_failures INT UNSIGNED DEFAULT 0 COMMENT 'Consecutive failure count',
    created_by INT UNSIGNED DEFAULT NULL COMMENT 'User who created this schedule',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Constraints and indexes
    UNIQUE KEY uk_schedule_name (schedule_name),
    FOREIGN KEY fk_sync_schedules_created_by (created_by) REFERENCES admin_users(id) ON SET NULL,
    INDEX idx_entity_type (entity_type),
    INDEX idx_is_active (is_active),
    INDEX idx_next_run (next_run),
    INDEX idx_last_run (last_run)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Automated synchronization schedule management';

-- =================================================================
-- TABLE: alert_config
-- Purpose: Alert and notification configuration management
-- =================================================================

CREATE TABLE IF NOT EXISTS alert_config (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    alert_type VARCHAR(50) NOT NULL COMMENT 'Type of alert (sync_failure, high_error_rate, etc.)',
    alert_name VARCHAR(100) NOT NULL COMMENT 'Human-readable alert name',
    description TEXT DEFAULT NULL COMMENT 'Alert description and purpose',
    is_enabled BOOLEAN DEFAULT TRUE COMMENT 'Alert enabled status',
    
    -- Notification channels
    email_notifications BOOLEAN DEFAULT FALSE COMMENT 'Send email notifications',
    email_addresses TEXT DEFAULT NULL COMMENT 'Comma-separated email list',
    webhook_notifications BOOLEAN DEFAULT FALSE COMMENT 'Send webhook notifications',
    webhook_url VARCHAR(500) DEFAULT NULL COMMENT 'Webhook endpoint URL',
    
    -- Threshold configuration
    threshold_type ENUM('count', 'percentage', 'time', 'rate') DEFAULT 'count',
    threshold_value DECIMAL(10,3) DEFAULT 0.000 COMMENT 'Alert threshold value',
    threshold_period INT UNSIGNED DEFAULT 300 COMMENT 'Evaluation period in seconds',
    
    -- Alert behavior
    cooldown_period INT UNSIGNED DEFAULT 1800 COMMENT 'Cooldown between alerts (seconds)',
    max_alerts_per_day INT UNSIGNED DEFAULT 10 COMMENT 'Maximum alerts per 24 hours',
    
    -- Metadata
    last_triggered TIMESTAMP NULL DEFAULT NULL COMMENT 'Last alert trigger timestamp',
    trigger_count_today INT UNSIGNED DEFAULT 0 COMMENT 'Alerts triggered today',
    created_by INT UNSIGNED DEFAULT NULL COMMENT 'User who created this alert',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Constraints and indexes
    UNIQUE KEY uk_alert_type (alert_type),
    FOREIGN KEY fk_alert_config_created_by (created_by) REFERENCES admin_users(id) ON SET NULL,
    INDEX idx_is_enabled (is_enabled),
    INDEX idx_alert_type (alert_type),
    INDEX idx_last_triggered (last_triggered)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Alert and notification configuration for monitoring';

-- =================================================================
-- TABLE: sync_operation_details
-- Purpose: Extended details for sync operations (supplements existing sync_operations)
-- =================================================================

CREATE TABLE IF NOT EXISTS sync_operation_details (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    operation_id INT UNSIGNED NOT NULL COMMENT 'Reference to sync_operations.id',
    request_data TEXT DEFAULT NULL COMMENT 'Original request data (JSON)',
    response_data TEXT DEFAULT NULL COMMENT 'API response data (JSON)',
    error_details TEXT DEFAULT NULL COMMENT 'Detailed error information',
    stack_trace TEXT DEFAULT NULL COMMENT 'Error stack trace for debugging',
    memory_usage INT UNSIGNED DEFAULT NULL COMMENT 'Memory usage in bytes',
    cpu_time DECIMAL(10,3) DEFAULT NULL COMMENT 'CPU time consumed in seconds',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Constraints and indexes
    UNIQUE KEY uk_operation_id (operation_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Extended details for sync operations - debugging and analysis';

-- =================================================================
-- TABLE: user_activity_log
-- Purpose: Audit trail for administrative actions
-- =================================================================

CREATE TABLE IF NOT EXISTS user_activity_log (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    activity_type VARCHAR(50) NOT NULL COMMENT 'Type of activity (login, config_change, etc.)',
    description VARCHAR(255) NOT NULL COMMENT 'Human-readable activity description',
    resource_type VARCHAR(50) DEFAULT NULL COMMENT 'Type of resource affected',
    resource_id VARCHAR(50) DEFAULT NULL COMMENT 'ID of resource affected',
    old_values JSON DEFAULT NULL COMMENT 'Previous values (for changes)',
    new_values JSON DEFAULT NULL COMMENT 'New values (for changes)',
    ip_address VARCHAR(45) DEFAULT NULL COMMENT 'Client IP address',
    user_agent TEXT DEFAULT NULL COMMENT 'Client user agent string',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Constraints and indexes
    FOREIGN KEY fk_user_activity_user_id (user_id) REFERENCES admin_users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_activity_type (activity_type),
    INDEX idx_created_at (created_at),
    INDEX idx_resource (resource_type, resource_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Audit trail for user activities and administrative actions';

-- =================================================================
-- DEFAULT DATA INSERTION
-- =================================================================

-- Insert default admin user (password: admin123 - MUST BE CHANGED IN PRODUCTION)
-- Password hash generated with: password_hash('admin123', PASSWORD_ARGON2ID)
INSERT IGNORE INTO admin_users 
(username, email, password_hash, full_name, is_active, is_superuser) 
VALUES 
(
    'admin', 
    'admin@descomplicar.pt', 
    '$argon2id$v=19$m=65536,t=4,p=3$WkFHY0w2Qm5QTDJIN2t6OQ$J8qP2KqZ5g8Yc5F8oP1xG7nH3mR2wX8pK1qL4sA5uV0', 
    'System Administrator', 
    TRUE, 
    TRUE
);

-- Insert default alert configurations
INSERT IGNORE INTO alert_config 
(alert_type, alert_name, description, is_enabled, email_notifications, threshold_type, threshold_value, threshold_period) 
VALUES
(
    'sync_failure', 
    'Sync Operation Failures', 
    'Alert when sync operations fail repeatedly', 
    TRUE, 
    TRUE, 
    'count', 
    5.000, 
    300
),
(
    'high_error_rate', 
    'High Error Rate', 
    'Alert when error rate exceeds threshold', 
    TRUE, 
    TRUE, 
    'percentage', 
    10.000, 
    600
),
(
    'performance_degradation', 
    'Performance Issues', 
    'Alert when response times exceed acceptable limits', 
    TRUE, 
    FALSE, 
    'time', 
    5.000, 
    300
),
(
    'low_sync_volume', 
    'Unusually Low Sync Activity', 
    'Alert when sync activity drops below expected levels', 
    FALSE, 
    FALSE, 
    'count', 
    10.000, 
    3600
);

-- Insert default sync schedule (disabled by default)
INSERT IGNORE INTO sync_schedules 
(schedule_name, description, cron_expression, entity_type, sync_direction, is_active, batch_size) 
VALUES
(
    'Hourly Customer Sync', 
    'Synchronize customers from DeskCRM to Moloni every hour', 
    '0 * * * *', 
    'customers', 
    'deskcrm_to_moloni', 
    FALSE, 
    50
),
(
    'Daily Invoice Import', 
    'Import invoices from Moloni to DeskCRM daily at 02:00', 
    '0 2 * * *', 
    'invoices', 
    'moloni_to_deskcrm', 
    FALSE, 
    100
);

-- =================================================================
-- PERFORMANCE OPTIMIZATION
-- =================================================================

-- Create additional indexes for frequently queried data
CREATE INDEX IF NOT EXISTS idx_sync_operations_created_date 
ON sync_operations (DATE(created_at));

CREATE INDEX IF NOT EXISTS idx_sync_operations_status_entity 
ON sync_operations (status, entity_type);

-- Optimize existing sync_config table if needed
ALTER TABLE sync_config 
ADD INDEX IF NOT EXISTS idx_config_key (config_key);

-- =================================================================
-- VIEWS FOR DASHBOARD QUERIES
-- =================================================================

-- Dashboard overview view
CREATE OR REPLACE VIEW v_dashboard_overview AS
SELECT 
    DATE(created_at) as stat_date,
    COUNT(*) as total_operations,
    SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as successful_operations,
    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed_operations,
    ROUND(AVG(execution_time), 3) as avg_execution_time,
    MAX(execution_time) as max_execution_time,
    COUNT(DISTINCT entity_type) as entity_types_processed
FROM sync_operations 
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(created_at)
ORDER BY stat_date DESC;

-- Recent errors view
CREATE OR REPLACE VIEW v_recent_errors AS
SELECT 
    so.id,
    so.operation_type,
    so.entity_type,
    so.entity_id,
    so.error_message,
    so.created_at,
    sod.error_details,
    sod.stack_trace
FROM sync_operations so
LEFT JOIN sync_operation_details sod ON so.id = sod.operation_id
WHERE so.status = 'failed' 
    AND so.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY so.created_at DESC
LIMIT 100;

-- Active schedules view
CREATE OR REPLACE VIEW v_active_schedules AS
SELECT 
    ss.*,
    au.username as created_by_username,
    CASE 
        WHEN ss.next_run <= NOW() AND ss.is_active = 1 THEN 'overdue'
        WHEN ss.is_active = 1 THEN 'scheduled'
        ELSE 'disabled'
    END as schedule_status
FROM sync_schedules ss
LEFT JOIN admin_users au ON ss.created_by = au.id
ORDER BY ss.next_run ASC;

-- =================================================================
-- CLEANUP STORED PROCEDURES
-- =================================================================

DELIMITER //

-- Procedure to clean up old session data
CREATE OR REPLACE PROCEDURE CleanupExpiredSessions()
BEGIN
    DELETE FROM user_sessions 
    WHERE expires_at < NOW();
    
    SELECT ROW_COUNT() as deleted_sessions;
END//

-- Procedure to aggregate daily statistics
CREATE OR REPLACE PROCEDURE AggregatedailyStats(IN target_date DATE)
BEGIN
    INSERT INTO sync_dashboard_stats 
    (stat_date, total_syncs, successful_syncs, failed_syncs, avg_response_time, peak_response_time, total_records_processed)
    SELECT 
        target_date,
        COUNT(*),
        SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END),
        SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END),
        ROUND(AVG(execution_time), 3),
        MAX(execution_time),
        COUNT(DISTINCT entity_id)
    FROM sync_operations 
    WHERE DATE(created_at) = target_date
    ON DUPLICATE KEY UPDATE
        total_syncs = VALUES(total_syncs),
        successful_syncs = VALUES(successful_syncs),
        failed_syncs = VALUES(failed_syncs),
        avg_response_time = VALUES(avg_response_time),
        peak_response_time = VALUES(peak_response_time),
        total_records_processed = VALUES(total_records_processed),
        updated_at = CURRENT_TIMESTAMP;
        
    SELECT 'Daily statistics aggregated successfully' as result;
END//

DELIMITER ;

-- =================================================================
-- VALIDATION AND VERIFICATION
-- =================================================================

-- Show all created tables
SELECT 
    TABLE_NAME,
    TABLE_ROWS,
    DATA_LENGTH,
    CREATE_TIME
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'desk_moloni' 
    AND TABLE_NAME LIKE '%sync%' 
    OR TABLE_NAME LIKE '%admin%'
    OR TABLE_NAME LIKE '%user%'
    OR TABLE_NAME LIKE '%alert%'
ORDER BY TABLE_NAME;

-- Verify foreign key relationships
SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE 
WHERE REFERENCED_TABLE_SCHEMA = 'desk_moloni'
    AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY TABLE_NAME;

-- Show created views
SELECT TABLE_NAME, VIEW_DEFINITION
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'desk_moloni'
ORDER BY TABLE_NAME;

-- Display success message
SELECT 
    '✅ Phase 2 Database Setup Complete!' as status,
    COUNT(*) as tables_created,
    NOW() as completed_at
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'desk_moloni';

-- =================================================================
-- SETUP COMPLETE
-- =================================================================

-- Final notes and recommendations
/*
SETUP COMPLETE! 

Next Steps:
1. Update admin user password: UPDATE admin_users SET password_hash = PASSWORD_HASH('new_secure_password', PASSWORD_ARGON2ID) WHERE username = 'admin';
2. Configure email settings in alert_config table
3. Review and adjust alert thresholds based on your environment
4. Test web interface connectivity to these new tables
5. Schedule regular cleanup of old sessions and logs

Security Reminders:
- Change default admin password immediately
- Review and configure appropriate email addresses for alerts
- Consider implementing additional authentication factors
- Regular backup of these configuration tables is recommended

Performance Notes:
- Statistics are aggregated daily via stored procedure - consider scheduling
- Session cleanup should run regularly (recommend hourly cron job)
- Monitor table growth and implement data retention policies

For questions or issues, refer to the Phase 2 development documentation.
*/