-- ============================================
-- FONCTIONS
-- ============================================

DELIMITER $$

-- Fonction pour générer un numéro de transaction unique
DROP FUNCTION IF EXISTS generate_transaction_code$$
CREATE FUNCTION generate_transaction_code() RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
    DECLARE new_code VARCHAR(50);
    DECLARE counter INT DEFAULT 1;
    
    WHILE counter <= 10 DO
        SET new_code = CONCAT('TXN', DATE_FORMAT(NOW(), '%Y%m%d'), LPAD(FLOOR(RAND() * 10000), 4, '0'));
        
        IF NOT EXISTS (SELECT 1 FROM transactions WHERE transaction_code = new_code) THEN
            RETURN new_code;
        END IF;
        
        SET counter = counter + 1;
    END WHILE;
    
    -- Fallback: timestamp + random
    RETURN CONCAT('TXN', UNIX_TIMESTAMP(), LPAD(FLOOR(RAND() * 1000), 3, '0'));
END$$

-- Fonction pour vérifier si un token est blacklisté
DROP FUNCTION IF EXISTS is_token_blacklisted$$
CREATE FUNCTION is_token_blacklisted(token_hash VARCHAR(255)) RETURNS BOOLEAN
DETERMINISTIC
BEGIN
    DECLARE token_count INT;
    
    SELECT COUNT(*) INTO token_count
    FROM jwt_blacklist 
    WHERE `jwt_blacklist`.token_hash = token_hash 
      AND expires_at > NOW();
    
    RETURN token_count > 0;
END$$

DELIMITER ;

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

-- ============================================
-- TRIGGERS
-- ============================================

DELIMITER $$

-- Trigger 1: Création automatique du wallet et profil à l'inscription
DROP TRIGGER IF EXISTS after_user_insert$$
CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    -- Créer un wallet par défaut
    INSERT INTO wallets (user_id, currency, wallet_number, wallet_name, wallet_type)
    VALUES (NEW.id, 'XAF', CONCAT('WAL', LPAD(NEW.id, 8, '0')), 'Portefeuille principal', 'personal');
    
    -- Créer un profil utilisateur vide
    INSERT INTO user_profiles (user_id)
    VALUES (NEW.id);
    
    -- Enregistrer l'événement de sécurité
    INSERT INTO security_events (user_id, event_type, risk_level, metadata)
    VALUES (NEW.id, 'login_success', 'low', 
            JSON_OBJECT('registration_type', 'manual', 'timestamp', NOW()));
END$$

-- Trigger 2: Mise à jour des soldes après transaction complétée
DROP TRIGGER IF EXISTS after_transaction_completed$$
CREATE TRIGGER after_transaction_completed
AFTER UPDATE ON transactions
FOR EACH ROW
BEGIN
    IF OLD.status != 'completed' AND NEW.status = 'completed' THEN
        -- Transaction de type PAYMENT: créditer le wallet du marchand
        IF NEW.type = 'payment' AND NEW.receiver_wallet_id IS NOT NULL THEN
            UPDATE wallets 
            SET balance = balance + NEW.net_amount,
                available_balance = available_balance + NEW.net_amount,
                last_transaction_at = NOW(),
                updated_at = NOW()
            WHERE id = NEW.receiver_wallet_id;
        END IF;
        
        -- Transaction de type TRANSFER: débiter l'expéditeur, créditer le destinataire
        IF NEW.type = 'transfer' THEN
            -- Débiter l'expéditeur
            IF NEW.sender_wallet_id IS NOT NULL THEN
                UPDATE wallets 
                SET balance = balance - NEW.amount,
                    available_balance = available_balance - NEW.amount,
                    last_transaction_at = NOW(),
                    updated_at = NOW()
                WHERE id = NEW.sender_wallet_id;
            END IF;
            
            -- Créditer le destinataire
            IF NEW.receiver_wallet_id IS NOT NULL THEN
                UPDATE wallets 
                SET balance = balance + NEW.net_amount,
                    available_balance = available_balance + NEW.net_amount,
                    last_transaction_at = NOW(),
                    updated_at = NOW()
                WHERE id = NEW.receiver_wallet_id;
            END IF;
        END IF;
        
        -- Transaction de type WITHDRAW: débiter le wallet
        IF NEW.type = 'withdraw' AND NEW.sender_wallet_id IS NOT NULL THEN
            UPDATE wallets 
            SET balance = balance - NEW.amount,
                available_balance = available_balance - NEW.amount,
                last_transaction_at = NOW(),
                updated_at = NOW()
            WHERE id = NEW.sender_wallet_id;
        END IF;
        
        -- Transaction de type DEPOSIT: créditer le wallet
        IF NEW.type = 'deposit' AND NEW.receiver_wallet_id IS NOT NULL THEN
            UPDATE wallets 
            SET balance = balance + NEW.net_amount,
                available_balance = available_balance + NEW.net_amount,
                last_transaction_at = NOW(),
                updated_at = NOW()
            WHERE id = NEW.receiver_wallet_id;
        END IF;
        
        -- Mettre à jour le compteur de succès du payment link
        IF NEW.link_id IS NOT NULL THEN
            UPDATE payment_links 
            SET success_count = success_count + 1,
                current_uses = current_uses + 1,
                updated_at = NOW()
            WHERE id = NEW.link_id;
        END IF;
        
        -- Mettre à jour le compteur de ventes pour les items
        IF NEW.product_id IS NOT NULL THEN
            UPDATE items 
            SET quantity_sold = quantity_sold + 1,
                updated_at = NOW()
            WHERE id = NEW.product_id;
        END IF;
        
        -- Créer une notification
        INSERT INTO notifications (user_id, type, title, message, data)
        VALUES (NEW.user_id, 'transaction', 
                CONCAT('Transaction ', UPPER(NEW.type), ' complétée'),
                CONCAT('Votre transaction #', NEW.transaction_code, ' de ', NEW.amount, ' ', NEW.currency, ' a été complétée avec succès.'),
                JSON_OBJECT(
                    'transaction_id', NEW.id,
                    'transaction_code', NEW.transaction_code,
                    'amount', NEW.amount,
                    'currency', NEW.currency,
                    'type', NEW.type,
                    'net_amount', NEW.net_amount,
                    'fees', NEW.fees
                ));
    END IF;
END$$

-- Trigger 3: Blacklist automatique après changement de mot de passe
DROP TRIGGER IF EXISTS after_user_password_change$$
CREATE TRIGGER after_user_password_change
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF OLD.password_hash != NEW.password_hash THEN
        -- Enregistrer l'événement de sécurité
        INSERT INTO security_events (user_id, event_type, risk_level, metadata)
        VALUES (NEW.id, 'password_change', 'medium', 
                JSON_OBJECT('change_type', 'password_update', 'timestamp', NOW()));
        
        -- Révoquer tous les refresh tokens de l'utilisateur
        UPDATE jwt_refresh_tokens 
        SET is_revoked = TRUE, 
            revoked_at = NOW(),
            revoked_reason = 'password_change'
        WHERE user_id = NEW.id 
          AND is_revoked = FALSE;
        
        -- Blacklist les tokens via application (côté Laravel)
        INSERT INTO audit_logs (user_id, action, entity_type, entity_id, metadata)
        VALUES (NEW.id, 'user.password_changed', 'users', NEW.id,
                JSON_OBJECT('tokens_revoked', 'all', 'reason', 'password_change'));
    END IF;
END$$

-- Trigger 4: Sécurité après KYC rejeté
DROP TRIGGER IF EXISTS after_kyc_rejected$$
CREATE TRIGGER after_kyc_rejected
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF OLD.kyc_status != 'rejected' AND NEW.kyc_status = 'rejected' THEN
        -- Enregistrer l'événement de sécurité
        INSERT INTO security_events (user_id, event_type, risk_level, metadata)
        VALUES (NEW.id, 'suspicious_activity', 'high', 
                JSON_OBJECT('reason', 'kyc_rejected', 'timestamp', NOW()));
        
        -- Révoquer tous les tokens de l'utilisateur
        UPDATE jwt_refresh_tokens 
        SET is_revoked = TRUE, 
            revoked_at = NOW(),
            revoked_reason = 'security_breach'
        WHERE user_id = NEW.id 
          AND is_revoked = FALSE;
        
        -- Geler le wallet principal
        UPDATE wallets 
        SET status = 'frozen',
            updated_at = NOW()
        WHERE user_id = NEW.id 
          AND is_primary = TRUE 
          AND status = 'active';
    END IF;
END$$

-- Trigger 5: Audit des modifications utilisateur
DROP TRIGGER IF EXISTS before_user_update$$
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    -- Ne pas enregistrer si seule la date de mise à jour change
    IF NOT (OLD.updated_at != NEW.updated_at AND 
           OLD.nom <=> NEW.nom AND
           OLD.prenom <=> NEW.prenom AND
           OLD.email <=> NEW.email AND
           OLD.phone <=> NEW.phone AND
           OLD.password_hash <=> NEW.password_hash AND
           OLD.role <=> NEW.role AND
           OLD.email_verified <=> NEW.email_verified AND
           OLD.kyc_status <=> NEW.kyc_status AND
           OLD.is_active <=> NEW.is_active) THEN
        
        INSERT INTO audit_logs (user_id, action, entity_type, entity_id, old_values, new_values, changes, ip_address)
        VALUES (
            NEW.id,
            'user.updated',
            'users',
            NEW.id,
            JSON_OBJECT(
                'email', OLD.email,
                'phone', OLD.phone,
                'role', OLD.role,
                'kyc_status', OLD.kyc_status,
                'is_active', OLD.is_active,
                'two_factor_enabled', OLD.two_factor_enabled
            ),
            JSON_OBJECT(
                'email', NEW.email,
                'phone', NEW.phone,
                'role', NEW.role,
                'kyc_status', NEW.kyc_status,
                'is_active', NEW.is_active,
                'two_factor_enabled', NEW.two_factor_enabled
            ),
            JSON_OBJECT(
                'email_changed', IF(OLD.email != NEW.email, TRUE, FALSE),
                'phone_changed', IF(OLD.phone != NEW.phone, TRUE, FALSE),
                'role_changed', IF(OLD.role != NEW.role, TRUE, FALSE),
                'kyc_status_changed', IF(OLD.kyc_status != NEW.kyc_status, TRUE, FALSE),
                'active_status_changed', IF(OLD.is_active != NEW.is_active, TRUE, FALSE),
                '2fa_changed', IF(OLD.two_factor_enabled != NEW.two_factor_enabled, TRUE, FALSE)
            ),
            NULL  -- IP address sera rempli par l'application
        );
    END IF;
END$$

-- Trigger 6: Mise à jour du stock après vente
DROP TRIGGER IF EXISTS after_item_sold$$
CREATE TRIGGER after_item_sold
AFTER UPDATE ON items
FOR EACH ROW
BEGIN
    IF NEW.quantity_sold > OLD.quantity_sold THEN
        -- Vérifier le stock bas
        IF NEW.quantity_available IS NOT NULL AND NEW.quantity_available <= NEW.low_stock_threshold THEN
            -- Créer une notification pour le marchand
            INSERT INTO notifications (user_id, type, title, message, data)
            SELECT p.user_id, 'alert', 'Stock faible',
                   CONCAT('Le produit "', NEW.title, '" est en stock faible (', NEW.quantity_available, ' restants)'),
                   JSON_OBJECT('product_id', NEW.id, 'product_name', NEW.title, 
                               'current_stock', NEW.quantity_available, 'low_threshold', NEW.low_stock_threshold)
            FROM payment_links p
            WHERE p.id = NEW.payment_link_id;
        END IF;
    END IF;
END$$

-- Trigger 7: Nettoyage automatique des sessions expirées
DROP TRIGGER IF EXISTS cleanup_expired_sessions$$
CREATE TRIGGER cleanup_expired_sessions
BEFORE INSERT ON user_sessions
FOR EACH ROW
BEGIN
    -- Supprimer les sessions expirées pour cet utilisateur
    DELETE FROM user_sessions 
    WHERE user_id = NEW.user_id 
      AND expires_at < NOW();
END$$

DELIMITER ;

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

-- É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 TIMESTAMP(CURRENT_DATE, '23:59:59')
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 TIMESTAMP(CURRENT_DATE, '00:00:00')
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;

-- ============================================
-- PROCÉDURES STOCKÉES (optionnelles)
-- ============================================

DELIMITER $$

-- Procédure pour calculer les commissions mensuelles
DROP PROCEDURE IF EXISTS calculate_monthly_commissions$$
CREATE PROCEDURE calculate_monthly_commissions(IN month_year DATE)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE merchant_id_val INT;
    DECLARE total_amount_val DECIMAL(18,2);
    DECLARE commission_rate_val DECIMAL(5,2);
    DECLARE cur CURSOR FOR 
        SELECT merchant_id, SUM(amount), 1.5 -- Taux par défaut
        FROM transactions 
        WHERE MONTH(created_at) = MONTH(month_year) 
          AND YEAR(created_at) = YEAR(month_year)
          AND status = 'completed'
          AND type = 'payment'
        GROUP BY merchant_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO merchant_id_val, total_amount_val, commission_rate_val;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Insérer la commission
        INSERT INTO commissions (merchant_id, platform_fee, merchant_amount, commission_rate, status)
        VALUES (
            merchant_id_val,
            total_amount_val * (commission_rate_val / 100),
            total_amount_val * (1 - commission_rate_val / 100),
            commission_rate_val,
            'pending'
        );
    END LOOP;
    
    CLOSE cur;
    
    SELECT 'Commissions mensuelles calculées avec succès' as message;
END$$

-- Procédure pour générer un rapport mensuel
DROP PROCEDURE IF EXISTS generate_monthly_report$$
CREATE PROCEDURE generate_monthly_report(IN month_year DATE)
BEGIN
    SELECT 
        DATE_FORMAT(month_year, '%Y-%m') as month,
        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,
        COUNT(DISTINCT merchant_id) as unique_merchants,
        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
    FROM transactions
    WHERE MONTH(created_at) = MONTH(month_year) 
      AND YEAR(created_at) = YEAR(month_year);
END$$

-- Procédure pour purger les anciennes données
DROP PROCEDURE IF EXISTS purge_old_data$$
CREATE PROCEDURE purge_old_data(IN days_to_keep INT)
BEGIN
    -- Purger les logs de plus de X jours
    DELETE FROM audit_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL days_to_keep DAY);
    DELETE FROM api_request_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL days_to_keep DAY);
    DELETE FROM email_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL days_to_keep DAY);
    DELETE FROM sms_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL days_to_keep DAY);
    DELETE FROM webhook_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL days_to_keep DAY);
    
    SELECT CONCAT('Données purgées avec succès (conservées: ', days_to_keep, ' jours)') as message;
END$$

DELIMITER ;

-- ============================================
-- MESSAGE DE CONFIRMATION
-- ============================================

SELECT '✅ OBJETS DE BASE DE DONNÉES CRÉÉS AVEC SUCCÈS!' as message;
SELECT CONCAT('📊 Vues créées: ', '6 vues') as views_status;
SELECT CONCAT('🔧 Fonctions créées: ', '2 fonctions') as functions_status;
SELECT CONCAT('⚡ Triggers créés: ', '7 triggers') as triggers_status;
SELECT CONCAT('⏰ Événements créés: ', '2 événements') as events_status;
SELECT CONCAT('📋 Procédures créées: ', '3 procédures') as procedures_status;