-- ============================================
-- Base de Données - Système WiFi FedaPay
-- Ange Technologies
-- Version avec Synchronisation Automatique MikroTik
-- ============================================

-- NOTE IMPORTANTE :
-- Les "erreurs" d'analyse statique dans phpMyAdmin concernant les procédures stockées
-- sont normales et peuvent être ignorées. Utilisez le bouton "Exécuter" directement.
-- Les messages comme "Unrecognized statement type" sont dus à l'analyseur statique
-- qui ne comprend pas parfaitement la syntaxe DELIMITER $$ mais l'exécution fonctionnera.

-- ============================================
-- Créer la base de données si elle n'existe pas
-- ============================================
CREATE DATABASE IF NOT EXISTS hotspot_tickets
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE hotspot_tickets;

-- ============================================
-- Table : tickets_pool
-- Pool de tickets pré-générés depuis Mikhmon
-- ============================================

CREATE TABLE IF NOT EXISTS tickets_pool (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'ID unique du ticket',
    username VARCHAR(50) UNIQUE NOT NULL COMMENT 'Nom d''utilisateur WiFi',
    password VARCHAR(50) NOT NULL COMMENT 'Mot de passe WiFi',
    profile VARCHAR(20) NOT NULL COMMENT 'Profile MikroTik (1H, 6H, 24H, 7J, 30J)',
    time_limit VARCHAR(20) DEFAULT NULL COMMENT 'Limite de temps (1h, 6h, 1d, 1w, etc.)',
    data_limit VARCHAR(20) DEFAULT NULL COMMENT 'Limite de données',
    comment TEXT DEFAULT NULL COMMENT 'Commentaire du ticket',
    
    -- Gestion de la vente
    is_sold BOOLEAN DEFAULT FALSE COMMENT 'TRUE = vendu, FALSE = disponible',
    sold_at DATETIME DEFAULT NULL COMMENT 'Date et heure de vente',
    transaction_id VARCHAR(100) DEFAULT NULL COMMENT 'ID de la transaction FedaPay',
    customer_phone VARCHAR(20) DEFAULT NULL COMMENT 'Numéro de téléphone du client',
    
    -- Synchronisation MikroTik (Système Auto)
    synced_to_mikrotik BOOLEAN DEFAULT FALSE COMMENT 'Ticket synchronisé avec MikroTik',
    synced_at DATETIME DEFAULT NULL COMMENT 'Date de synchronisation avec MikroTik',
    sync_in_progress BOOLEAN DEFAULT FALSE COMMENT 'Synchronisation en cours',
    sync_started_at DATETIME DEFAULT NULL COMMENT 'Début de la synchronisation',
    sync_completed_at DATETIME DEFAULT NULL COMMENT 'Date de fin de synchronisation',
    sync_error TEXT DEFAULT NULL COMMENT 'Message d erreur de synchronisation',
    sync_attempts INT DEFAULT 0 COMMENT 'Nombre de tentatives de synchronisation',
    last_sync_attempt DATETIME DEFAULT NULL COMMENT 'Dernière tentative de synchronisation',
    auto_created BOOLEAN DEFAULT FALSE COMMENT 'Ticket créé automatiquement par le webhook',
    
    -- Métadonnées
    imported_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Date d''import du ticket',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Date de création',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Dernière mise à jour',
    
    -- Index pour optimiser les recherches
    INDEX idx_profile (profile),
    INDEX idx_is_sold (is_sold),
    INDEX idx_transaction_id (transaction_id),
    INDEX idx_sold_at (sold_at),
    INDEX idx_profile_sold (profile, is_sold) COMMENT 'Index composite pour recherche de tickets disponibles',
    INDEX idx_synced_to_mikrotik (synced_to_mikrotik),
    INDEX idx_sync_in_progress (sync_in_progress),
    INDEX idx_auto_created (auto_created),
    INDEX idx_sold_synced (is_sold, synced_to_mikrotik) COMMENT 'Index pour la synchronisation'
    
) ENGINE=InnoDB 
DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_unicode_ci
COMMENT='Pool de tickets WiFi avec synchronisation automatique MikroTik';

-- ============================================
-- Table : transactions_log (Optionnel)
-- Log détaillé des transactions
-- ============================================

CREATE TABLE IF NOT EXISTS transactions_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id VARCHAR(100) UNIQUE NOT NULL COMMENT 'ID FedaPay',
    status VARCHAR(20) NOT NULL COMMENT 'approved, declined, pending',
    amount DECIMAL(10, 2) NOT NULL COMMENT 'Montant en XOF',
    currency VARCHAR(3) DEFAULT 'XOF',
    
    -- Client
    customer_firstname VARCHAR(100) DEFAULT NULL,
    customer_lastname VARCHAR(100) DEFAULT NULL,
    customer_email VARCHAR(100) DEFAULT NULL,
    customer_phone VARCHAR(20) DEFAULT NULL,
    
    -- Ticket attribué
    ticket_username VARCHAR(50) DEFAULT NULL,
    ticket_profile VARCHAR(20) DEFAULT NULL,
    
    -- Métadonnées
    webhook_received_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    processed_at DATETIME DEFAULT NULL,
    
    -- Données brutes
    fedapay_raw_data JSON DEFAULT NULL COMMENT 'Données brutes du webhook FedaPay',
    
    INDEX idx_transaction_id (transaction_id),
    INDEX idx_status (status),
    INDEX idx_customer_phone (customer_phone)
    
) ENGINE=InnoDB 
DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_unicode_ci
COMMENT='Log des transactions FedaPay';

-- ============================================
-- Table : sync_logs
-- Logs de synchronisation avec MikroTik
-- ============================================

CREATE TABLE IF NOT EXISTS sync_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sync_date DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Date de synchronisation',
    tickets_synced INT DEFAULT 0 COMMENT 'Nombre de tickets synchronisés',
    tickets_failed INT DEFAULT 0 COMMENT 'Nombre de tickets en erreur',
    duration_seconds DECIMAL(10,2) DEFAULT 0 COMMENT 'Durée en secondes',
    error_message TEXT DEFAULT NULL COMMENT 'Message d erreur',
    status ENUM('success', 'partial', 'failed') DEFAULT 'success' COMMENT 'Statut de la synchronisation',
    
    INDEX idx_sync_date (sync_date),
    INDEX idx_status (status)
    
) ENGINE=InnoDB 
DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_unicode_ci
COMMENT='Logs des synchronisations avec MikroTik';

-- ============================================
-- Table : tickets_stats
-- Statistiques agrégées des tickets
-- ============================================

CREATE TABLE IF NOT EXISTS tickets_stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL COMMENT 'Date des statistiques',
    profile VARCHAR(20) NOT NULL COMMENT 'Profile concerné',
    tickets_created INT DEFAULT 0 COMMENT 'Tickets créés',
    tickets_sold INT DEFAULT 0 COMMENT 'Tickets vendus',
    tickets_auto_created INT DEFAULT 0 COMMENT 'Tickets créés automatiquement',
    revenue DECIMAL(10,2) DEFAULT 0 COMMENT 'Revenu estimé en XOF',
    
    UNIQUE KEY unique_date_profile (date, profile),
    INDEX idx_date (date),
    INDEX idx_profile (profile)
    
) ENGINE=InnoDB 
DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_unicode_ci
COMMENT='Statistiques quotidiennes des tickets';

-- ============================================
-- Vues utiles
-- ============================================

-- Vue : Statistiques par profile
CREATE OR REPLACE VIEW stats_by_profile AS
SELECT 
    profile,
    COUNT(*) as total_tickets,
    SUM(CASE WHEN is_sold = FALSE THEN 1 ELSE 0 END) as disponibles,
    SUM(CASE WHEN is_sold = TRUE THEN 1 ELSE 0 END) as vendus,
    ROUND((SUM(CASE WHEN is_sold = TRUE THEN 1 ELSE 0 END) / COUNT(*)) * 100, 2) as taux_vente_pct
FROM tickets_pool
GROUP BY profile
ORDER BY profile;

-- Vue : Ventes récentes
CREATE OR REPLACE VIEW recent_sales AS
SELECT 
    username,
    password,
    profile,
    transaction_id,
    customer_phone,
    sold_at,
    TIMESTAMPDIFF(MINUTE, sold_at, NOW()) as minutes_ago
FROM tickets_pool
WHERE is_sold = TRUE
ORDER BY sold_at DESC
LIMIT 50;

-- Vue : Alertes stock faible
CREATE OR REPLACE VIEW low_stock_alerts AS
SELECT 
    profile,
    COUNT(*) as stock_disponible
FROM tickets_pool
WHERE is_sold = FALSE
GROUP BY profile
HAVING stock_disponible < 10
ORDER BY stock_disponible ASC;

-- Vue : Tickets en attente de synchronisation
CREATE OR REPLACE VIEW pending_sync_tickets AS
SELECT 
    id,
    username,
    password,
    profile,
    time_limit,
    data_limit,
    comment,
    created_at,
    transaction_id,
    customer_phone,
    sync_attempts,
    last_sync_attempt
FROM tickets_pool
WHERE is_sold = TRUE 
  AND synced_to_mikrotik = FALSE
  AND sync_in_progress = FALSE
  AND (sync_attempts < 3 OR sync_attempts IS NULL)
ORDER BY created_at ASC;

-- Vue : Statistiques quotidiennes
CREATE OR REPLACE VIEW daily_stats AS
SELECT 
    DATE(created_at) as date,
    profile,
    COUNT(*) as total_tickets,
    SUM(CASE WHEN is_sold = TRUE THEN 1 ELSE 0 END) as sold_tickets,
    SUM(CASE WHEN auto_created = TRUE THEN 1 ELSE 0 END) as auto_created_tickets,
    SUM(CASE WHEN synced_to_mikrotik = TRUE THEN 1 ELSE 0 END) as synced_tickets
FROM tickets_pool
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY DATE(created_at), profile
ORDER BY date DESC, profile;

-- ============================================
-- Procédures stockées utiles
-- ============================================

DELIMITER $$

-- Procédure : Obtenir un ticket disponible
DROP PROCEDURE IF EXISTS get_available_ticket$$
CREATE PROCEDURE get_available_ticket(
    IN p_profile VARCHAR(20),
    IN p_transaction_id VARCHAR(100),
    IN p_customer_phone VARCHAR(20),
    OUT p_username VARCHAR(50),
    OUT p_password VARCHAR(50)
)
BEGIN
    -- Déclarer les variables
    DECLARE v_ticket_id INT;
    
    -- Démarrer une transaction
    START TRANSACTION;
    
    -- Récupérer et verrouiller un ticket disponible
    SELECT id, username, password
    INTO v_ticket_id, p_username, p_password
    FROM tickets_pool
    WHERE profile = p_profile 
      AND is_sold = FALSE
    ORDER BY id ASC
    LIMIT 1
    FOR UPDATE;
    
    -- Si un ticket a été trouvé, le marquer comme vendu
    IF v_ticket_id IS NOT NULL THEN
        UPDATE tickets_pool
        SET is_sold = TRUE,
            sold_at = NOW(),
            transaction_id = p_transaction_id,
            customer_phone = p_customer_phone
        WHERE id = v_ticket_id;
        
        COMMIT;
    ELSE
        ROLLBACK;
        SET p_username = NULL;
        SET p_password = NULL;
    END IF;
END$$

-- Procédure : Réinitialiser un ticket (annulation)
DROP PROCEDURE IF EXISTS reset_ticket$$
CREATE PROCEDURE reset_ticket(
    IN p_transaction_id VARCHAR(100)
)
BEGIN
    UPDATE tickets_pool
    SET is_sold = FALSE,
        sold_at = NULL,
        customer_phone = NULL
    WHERE transaction_id = p_transaction_id;
    
    SELECT ROW_COUNT() as rows_affected;
END$$

-- Procédure : Statistiques globales
DROP PROCEDURE IF EXISTS get_global_stats$$
CREATE PROCEDURE get_global_stats()
BEGIN
    SELECT 
        COUNT(*) as total_tickets,
        SUM(CASE WHEN is_sold = FALSE THEN 1 ELSE 0 END) as disponibles,
        SUM(CASE WHEN is_sold = TRUE THEN 1 ELSE 0 END) as vendus,
        COUNT(DISTINCT profile) as nb_profiles,
        MIN(sold_at) as premiere_vente,
        MAX(sold_at) as derniere_vente,
        COUNT(DISTINCT DATE(sold_at)) as jours_actifs
    FROM tickets_pool;
END$$

-- Procédure : Nettoyer les anciens tickets synchronisés
DROP PROCEDURE IF EXISTS cleanup_old_tickets$$
CREATE PROCEDURE cleanup_old_tickets(IN days_to_keep INT)
BEGIN
    -- Supprimer les tickets vendus et synchronisés de plus de X jours
    DELETE FROM tickets_pool
    WHERE is_sold = TRUE
      AND synced_to_mikrotik = TRUE
      AND synced_at < DATE_SUB(NOW(), INTERVAL days_to_keep DAY);
    
    SELECT ROW_COUNT() as deleted_tickets;
END$$

-- Procédure : Réinitialiser les synchronisations bloquées
DROP PROCEDURE IF EXISTS reset_stuck_syncs$$
CREATE PROCEDURE reset_stuck_syncs()
BEGIN
    -- Réinitialiser les synchronisations qui sont "en cours" depuis plus de 10 minutes
    UPDATE tickets_pool
    SET sync_in_progress = FALSE,
        sync_error = 'Synchronisation bloquée - réinitialisée automatiquement',
        sync_attempts = sync_attempts + 1
    WHERE sync_in_progress = TRUE
      AND sync_started_at < DATE_SUB(NOW(), INTERVAL 10 MINUTE);
    
    SELECT ROW_COUNT() as reset_tickets;
END$$

DELIMITER ;

-- ============================================
-- Events programmés (OPTIONNEL - Nécessite privilège SUPER)
-- ============================================

-- IMPORTANT : Les événements nécessitent le privilège SUPER
-- Si vous avez accès root, décommentez les lignes suivantes :

-- Activer le scheduler d'événements (à faire une seule fois)
-- SET GLOBAL event_scheduler = ON;

-- DELIMITER $$

-- Event : Réinitialiser les synchronisations bloquées toutes les 15 minutes
-- CREATE EVENT IF NOT EXISTS reset_stuck_syncs_event
-- ON SCHEDULE EVERY 15 MINUTE
-- STARTS CURRENT_TIMESTAMP
-- DO
-- BEGIN
--     CALL reset_stuck_syncs();
-- END$$

-- DELIMITER ;

-- Alternative sans privilège SUPER :
-- Créez un cron job qui exécute cette commande toutes les 15 minutes :
-- mysql -u [user] -p[password] hotspot_tickets -e "CALL reset_stuck_syncs();"

-- ============================================
-- Utilisateur dédié (optionnel mais recommandé)
-- ============================================

-- Créer un utilisateur dédié pour l'application
-- REMPLACEZ 'votre_password_securise' par un mot de passe fort

-- CREATE USER IF NOT EXISTS 'wifi_app'@'localhost' IDENTIFIED BY 'votre_password_securise';
-- GRANT SELECT, INSERT, UPDATE ON hotspot_tickets.tickets_pool TO 'wifi_app'@'localhost';
-- GRANT SELECT ON hotspot_tickets.stats_by_profile TO 'wifi_app'@'localhost';
-- GRANT SELECT ON hotspot_tickets.recent_sales TO 'wifi_app'@'localhost';
-- GRANT EXECUTE ON PROCEDURE hotspot_tickets.get_available_ticket TO 'wifi_app'@'localhost';
-- FLUSH PRIVILEGES;

-- ============================================
-- Données de test (Optionnel)
-- ============================================

-- Insérer quelques tickets de test
-- Décommentez pour tester

/*
INSERT INTO tickets_pool (username, password, profile, time_limit, comment) VALUES
('test001', 'pass001', '1H', '1h', 'Ticket de test 1H'),
('test002', 'pass002', '6H', '6h', 'Ticket de test 6H'),
('test003', 'pass003', '24H', '1d', 'Ticket de test 24H'),
('test004', 'pass004', '7J', '1w', 'Ticket de test 1 semaine'),
('test005', 'pass005', '30J', '4w2d', 'Ticket de test 1 mois');
*/

-- ============================================
-- Triggers utiles
-- ============================================

-- Trigger : Logger les ventes
DELIMITER $$

DROP TRIGGER IF EXISTS after_ticket_sold$$
CREATE TRIGGER after_ticket_sold
AFTER UPDATE ON tickets_pool
FOR EACH ROW
BEGIN
    -- Si le ticket vient d'être vendu
    IF NEW.is_sold = TRUE AND OLD.is_sold = FALSE THEN
        -- Insérer un log (si la table transactions_log existe)
        INSERT IGNORE INTO transactions_log (
            transaction_id,
            status,
            customer_phone,
            ticket_username,
            ticket_profile,
            processed_at
        ) VALUES (
            NEW.transaction_id,
            'approved',
            NEW.customer_phone,
            NEW.username,
            NEW.profile,
            NOW()
        );
    END IF;
END$$

DELIMITER ;

-- ============================================
-- Requêtes utiles pour l'administration
-- ============================================

-- Voir les tickets disponibles par profile
-- SELECT * FROM stats_by_profile;

-- Voir les 20 dernières ventes
-- SELECT * FROM recent_sales LIMIT 20;

-- Voir les alertes de stock faible
-- SELECT * FROM low_stock_alerts;

-- Ventes du jour
-- SELECT COUNT(*) as ventes_aujourd_hui
-- FROM tickets_pool
-- WHERE DATE(sold_at) = CURDATE() AND is_sold = TRUE;

-- Revenus approximatifs du jour
-- SELECT 
--     profile,
--     COUNT(*) as ventes,
--     CASE profile
--         WHEN '1H' THEN COUNT(*) * 100
--         WHEN '6H' THEN COUNT(*) * 300
--         WHEN '24H' THEN COUNT(*) * 500
--         WHEN '7J' THEN COUNT(*) * 1500
--         WHEN '30J' THEN COUNT(*) * 4000
--         ELSE 0
--     END as revenue_xof
-- FROM tickets_pool
-- WHERE DATE(sold_at) = CURDATE() AND is_sold = TRUE
-- GROUP BY profile;

-- Tickets en attente de synchronisation
-- SELECT * FROM pending_sync_tickets;

-- Tickets en erreur de synchronisation (plus de 3 tentatives)
-- SELECT id, username, profile, sync_attempts, last_sync_attempt, sync_error
-- FROM tickets_pool
-- WHERE synced_to_mikrotik = FALSE AND sync_attempts >= 3
-- ORDER BY last_sync_attempt DESC;

-- Statistiques de synchronisation
-- SELECT 
--     DATE(sync_date) as date,
--     COUNT(*) as nb_syncs,
--     SUM(tickets_synced) as total_synced,
--     SUM(tickets_failed) as total_failed,
--     AVG(duration_seconds) as avg_duration
-- FROM sync_logs
-- WHERE sync_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
-- GROUP BY DATE(sync_date)
-- ORDER BY date DESC;

-- Tickets auto-créés aujourd'hui
-- SELECT COUNT(*) as auto_created_today
-- FROM tickets_pool
-- WHERE auto_created = TRUE AND DATE(created_at) = CURDATE();

-- Taux de synchronisation
-- SELECT 
--     COUNT(*) as total_tickets_vendus,
--     SUM(CASE WHEN synced_to_mikrotik = TRUE THEN 1 ELSE 0 END) as synced,
--     ROUND((SUM(CASE WHEN synced_to_mikrotik = TRUE THEN 1 ELSE 0 END) / COUNT(*)) * 100, 2) as taux_sync_pct
-- FROM tickets_pool
-- WHERE is_sold = TRUE;

-- ============================================
-- Fin du script
-- ============================================

-- Afficher les tables créées
SHOW TABLES;

-- Afficher la structure de la table principale
DESCRIBE tickets_pool;

SELECT '✅ Base de données créée avec succès!' as status;