-- ============================================
-- SYSTÈME D'ORDONNANCEMENT DES SCRAPERS
-- Base: appros_dm
-- ============================================

-- Table principale : configuration des scrapers
CREATE TABLE IF NOT EXISTS dm_scraper_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    scraper_name VARCHAR(100) NOT NULL UNIQUE COMMENT 'Nom unique du scraper',
    scraper_path VARCHAR(500) NOT NULL COMMENT 'Chemin complet du fichier PHP',
    target_table VARCHAR(100) NOT NULL COMMENT 'Table de destination des données',
    description TEXT COMMENT 'Description du scraper',
    
    -- Ordonnancement
    schedule_time TIME NULL COMMENT 'Heure de lancement (ex: 08:30:00)',
    schedule_frequency ENUM('daily', 'hourly', 'every_5min', 'every_15min', 'every_30min', 'custom') DEFAULT 'daily' COMMENT 'Fréquence d''exécution',
    schedule_cron VARCHAR(50) NULL COMMENT 'Expression cron custom si nécessaire',
    
    -- Jours d'exécution
    run_monday TINYINT(1) DEFAULT 1,
    run_tuesday TINYINT(1) DEFAULT 1,
    run_wednesday TINYINT(1) DEFAULT 1,
    run_thursday TINYINT(1) DEFAULT 1,
    run_friday TINYINT(1) DEFAULT 1,
    run_saturday TINYINT(1) DEFAULT 0 COMMENT 'Exécuter le weekend',
    run_sunday TINYINT(1) DEFAULT 0,
    
    -- État
    is_active TINYINT(1) DEFAULT 1 COMMENT 'Scraper actif ou en pause',
    priority INT DEFAULT 50 COMMENT 'Priorité d''exécution (1-100, 100 = urgent)',
    timeout_seconds INT DEFAULT 300 COMMENT 'Timeout max en secondes',
    
    -- Métadonnées
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by VARCHAR(50) DEFAULT 'system',
    
    INDEX idx_active (is_active),
    INDEX idx_schedule (schedule_time, schedule_frequency),
    INDEX idx_target_table (target_table)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Configuration centralisée des scrapers';

-- Table des exécutions : log de chaque run
CREATE TABLE IF NOT EXISTS dm_scraper_execution_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    scraper_id INT NOT NULL,
    scraper_name VARCHAR(100) NOT NULL,
    
    -- Timestamps
    started_at DATETIME NOT NULL COMMENT 'Heure de démarrage',
    ended_at DATETIME NULL COMMENT 'Heure de fin',
    duration_seconds INT NULL COMMENT 'Durée en secondes',
    
    -- Résultat
    status ENUM('running', 'success', 'error', 'timeout', 'skipped') DEFAULT 'running',
    exit_code INT NULL COMMENT 'Code de retour du script PHP',
    
    -- Compteurs
    records_before INT NULL COMMENT 'Nombre de records avant scraping',
    records_after INT NULL COMMENT 'Nombre de records après scraping',
    records_inserted INT NULL COMMENT 'Nouveaux records insérés',
    records_updated INT NULL COMMENT 'Records mis à jour',
    records_deleted INT NULL COMMENT 'Records supprimés',
    
    -- Détails
    output_log TEXT COMMENT 'Sortie standard du script',
    error_log TEXT COMMENT 'Erreurs capturées',
    api_calls INT NULL COMMENT 'Nombre d''appels API effectués',
    
    -- Métriques
    memory_peak_mb DECIMAL(10,2) NULL COMMENT 'Pic mémoire utilisé',
    cpu_time_seconds DECIMAL(10,2) NULL,
    
    -- Métadonnées
    triggered_by ENUM('scheduler', 'manual', 'api', 'retry') DEFAULT 'scheduler',
    triggered_by_user VARCHAR(50) NULL,
    server_hostname VARCHAR(100) NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (scraper_id) REFERENCES dm_scraper_config(id) ON DELETE CASCADE,
    
    INDEX idx_scraper_status (scraper_id, status),
    INDEX idx_started_at (started_at),
    INDEX idx_status (status),
    INDEX idx_scraper_name (scraper_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Historique des exécutions de scrapers';

-- Table des alertes et notifications
CREATE TABLE IF NOT EXISTS dm_scraper_alerts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    scraper_id INT NOT NULL,
    execution_log_id INT NULL,
    
    alert_type ENUM('error', 'warning', 'timeout', 'no_data', 'success_after_retry') NOT NULL,
    severity ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    
    message TEXT NOT NULL,
    details JSON NULL COMMENT 'Détails additionnels en JSON',
    
    is_resolved TINYINT(1) DEFAULT 0,
    resolved_at DATETIME NULL,
    resolved_by VARCHAR(50) NULL,
    resolution_note TEXT NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (scraper_id) REFERENCES dm_scraper_config(id) ON DELETE CASCADE,
    FOREIGN KEY (execution_log_id) REFERENCES dm_scraper_execution_log(id) ON DELETE SET NULL,
    
    INDEX idx_unresolved (is_resolved, severity),
    INDEX idx_scraper (scraper_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Alertes et erreurs des scrapers';

-- Vue : état actuel des scrapers
CREATE OR REPLACE VIEW vw_scraper_status AS
SELECT 
    c.id,
    c.scraper_name,
    c.target_table,
    c.is_active,
    c.schedule_time,
    c.schedule_frequency,
    c.priority,
    
    -- Dernière exécution
    (SELECT started_at FROM dm_scraper_execution_log 
     WHERE scraper_id = c.id 
     ORDER BY started_at DESC LIMIT 1) as last_run,
    
    (SELECT status FROM dm_scraper_execution_log 
     WHERE scraper_id = c.id 
     ORDER BY started_at DESC LIMIT 1) as last_status,
    
    (SELECT records_inserted FROM dm_scraper_execution_log 
     WHERE scraper_id = c.id 
     ORDER BY started_at DESC LIMIT 1) as last_records_inserted,
    
    -- Statistiques 24h
    (SELECT COUNT(*) FROM dm_scraper_execution_log 
     WHERE scraper_id = c.id 
     AND started_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)) as runs_last_24h,
    
    (SELECT COUNT(*) FROM dm_scraper_execution_log 
     WHERE scraper_id = c.id 
     AND status = 'error' 
     AND started_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)) as errors_last_24h,
    
    -- Alertes non résolues
    (SELECT COUNT(*) FROM dm_scraper_alerts 
     WHERE scraper_id = c.id 
     AND is_resolved = 0) as unresolved_alerts,
    
    -- Prochaine exécution estimée
    CASE 
        WHEN c.schedule_frequency = 'daily' THEN 
            DATE_ADD(CURDATE(), INTERVAL 1 DAY) + INTERVAL HOUR(c.schedule_time) HOUR + INTERVAL MINUTE(c.schedule_time) MINUTE
        WHEN c.schedule_frequency = 'hourly' THEN 
            DATE_ADD(NOW(), INTERVAL 1 HOUR)
        WHEN c.schedule_frequency = 'every_5min' THEN 
            DATE_ADD(NOW(), INTERVAL 5 MINUTE)
        ELSE NULL
    END as next_scheduled_run
    
FROM dm_scraper_config c
WHERE c.is_active = 1
ORDER BY c.priority DESC, c.scraper_name;

-- Vue : synthèse des exécutions du jour
CREATE OR REPLACE VIEW vw_scraper_daily_summary AS
SELECT 
    DATE(started_at) as execution_date,
    scraper_name,
    COUNT(*) as total_runs,
    SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as success_count,
    SUM(CASE WHEN status = 'error' THEN 1 ELSE 0 END) as error_count,
    SUM(CASE WHEN status = 'timeout' THEN 1 ELSE 0 END) as timeout_count,
    SUM(records_inserted) as total_records_inserted,
    SUM(records_updated) as total_records_updated,
    AVG(duration_seconds) as avg_duration_seconds,
    MAX(duration_seconds) as max_duration_seconds,
    SUM(api_calls) as total_api_calls
FROM dm_scraper_execution_log
WHERE started_at >= CURDATE()
GROUP BY DATE(started_at), scraper_name
ORDER BY execution_date DESC, scraper_name;
