-- ============================================
-- VUES
-- ============================================

-- Vue pour les statistiques des marchands
DROP VIEW IF EXISTS merchant_stats;
CREATE VIEW merchant_stats AS
SELECT 
    u.id as merchant_id,
    u.nom,
    u.prenom,
    u.email,
    COUNT(DISTINCT t.id) as total_transactions,
    SUM(CASE WHEN t.status = 'completed' THEN t.amount ELSE 0 END) as total_revenue,
    SUM(CASE WHEN t.status = 'completed' THEN t.fees ELSE 0 END) as total_fees,
    COUNT(DISTINCT p.id) as total_products,
    AVG(r.rating) as average_rating,
    COUNT(DISTINCT r.id) as total_reviews
FROM users u
LEFT JOIN transactions t ON u.id = t.merchant_id
LEFT JOIN merchant_products p ON u.id = p.merchant_id
LEFT JOIN merchant_reviews r ON u.id = r.merchant_id AND r.status = 'approved'
WHERE u.role = 'merchant' AND u.is_active = TRUE
GROUP BY u.id;

-- Vue pour les rapports journaliers
DROP VIEW IF EXISTS daily_reports;
CREATE VIEW daily_reports AS
SELECT 
    DATE(created_at) as report_date,
    COUNT(*) as total_transactions,
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) as total_amount,
    SUM(CASE WHEN status = 'completed' THEN fees ELSE 0 END) as total_fees,
    COUNT(DISTINCT user_id) as unique_users,
    SUM(CASE WHEN type = 'payment' THEN 1 ELSE 0 END) as payment_count,
    SUM(CASE WHEN type = 'withdraw' THEN 1 ELSE 0 END) as withdraw_count,
    SUM(CASE WHEN type = 'transfer' THEN 1 ELSE 0 END) as transfer_count,
    SUM(CASE WHEN type = 'refund' THEN 1 ELSE 0 END) as refund_count
FROM transactions
GROUP BY DATE(created_at);

-- Vue pour les soldes des utilisateurs
DROP VIEW IF EXISTS user_balances;
CREATE VIEW user_balances AS
SELECT 
    u.id as user_id,
    u.email,
    u.phone,
    u.role,
    w.currency,
    SUM(w.balance) as total_balance,
    SUM(w.available_balance) as available_balance,
    SUM(w.hold_balance) as hold_balance,
    COUNT(w.id) as wallet_count
FROM users u
JOIN wallets w ON u.id = w.user_id
WHERE w.status = 'active'
GROUP BY u.id, w.currency;

-- Vue pour les paiements en attente de webhook
DROP VIEW IF EXISTS pending_webhooks;
CREATE VIEW pending_webhooks AS
SELECT 
    wl.id as webhook_id,
    wl.url,
    wl.events,
    wl.secret_key,
    COUNT(w.id) as pending_count
FROM webhook_endpoints wl
LEFT JOIN webhook_logs w ON wl.id = w.webhook_endpoint_id AND w.status = 'pending'
WHERE wl.is_active = TRUE
GROUP BY wl.id;

-- Vue pour monitoring des sessions actives
DROP VIEW IF EXISTS active_sessions_monitor;
CREATE VIEW active_sessions_monitor AS
SELECT 
    u.id as user_id,
    u.email,
    u.role,
    COUNT(DISTINCT s.id) as active_sessions,
    COUNT(DISTINCT rt.id) as active_refresh_tokens,
    MAX(s.last_activity) as last_activity,
    GROUP_CONCAT(DISTINCT s.ip_address) as ips,
    GROUP_CONCAT(DISTINCT LEFT(s.user_agent, 50)) as devices
FROM users u
LEFT JOIN user_sessions s ON u.id = s.user_id AND s.is_active = TRUE AND s.expires_at > NOW()
LEFT JOIN jwt_refresh_tokens rt ON u.id = rt.user_id AND rt.is_revoked = FALSE AND rt.expires_at > NOW()
GROUP BY u.id
HAVING active_sessions > 0 OR active_refresh_tokens > 0;

-- Vue pour détection d'anomalies de sécurité
DROP VIEW IF EXISTS security_anomalies;
CREATE VIEW security_anomalies AS
SELECT 
    se.*,
    u.email,
    u.phone,
    TIMESTAMPDIFF(MINUTE, se.created_at, NOW()) as minutes_ago,
    CASE 
        WHEN se.event_type = 'login_failed' AND se.risk_level = 'high' THEN 'Bloquer IP'
        WHEN se.event_type = 'suspicious_activity' THEN 'Vérifier manuellement'
        ELSE 'Surveiller'
    END as recommended_action
FROM security_events se
LEFT JOIN users u ON se.user_id = u.id
WHERE se.is_handled = FALSE
ORDER BY se.risk_level DESC, se.created_at DESC;

-- ============================================
-- ÉVÉNEMENTS MYSQL (nettoyage automatique)
-- ============================================
-- ============================================
-- ÉVÉNEMENTS MYSQL (nettoyage automatique)
-- ============================================

-- Activer le planificateur d'événements
SET GLOBAL event_scheduler = ON;

-- Événement pour nettoyer les données expirées quotidiennement
DROP EVENT IF EXISTS daily_cleanup;
CREATE EVENT daily_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS (CURRENT_DATE + INTERVAL 1 DAY)
DO
BEGIN
    -- Nettoyer les tokens expirés
    DELETE FROM jwt_refresh_tokens WHERE expires_at < NOW();
    DELETE FROM password_resets WHERE expires_at < NOW();
    DELETE FROM email_verifications WHERE expires_at < NOW();
    DELETE FROM user_sessions WHERE expires_at < NOW();
    
    -- Nettoyer la blacklist JWT expirée
    DELETE FROM jwt_blacklist WHERE expires_at < NOW();
    
    -- Nettoyer le cache API expiré
    DELETE FROM api_cache WHERE expires_at < NOW();
    
    -- Désactiver les liens de paiement expirés
    UPDATE payment_links 
    SET status = 'expired' 
    WHERE expires_at < NOW() AND status = 'active';
    
    -- Désactiver les QR codes expirés
    UPDATE dynamic_qr_codes 
    SET status = 'expired' 
    WHERE expires_at < NOW() AND status = 'active';
    
    -- Nettoyer les notifications expirées
    DELETE FROM notifications WHERE expires_at < NOW();
END;

-- Événement pour réinitialiser les compteurs d'API quotidiennement
DROP EVENT IF EXISTS reset_api_counters;
CREATE EVENT reset_api_counters
ON SCHEDULE EVERY 1 DAY
STARTS (CURRENT_DATE + INTERVAL 1 DAY)
DO
BEGIN
    UPDATE api_keys 
    SET calls_today = 0, 
        last_reset_date = CURDATE() 
    WHERE last_reset_date < CURDATE() OR last_reset_date IS NULL;
END;