<?php

namespace App;

use PDO;
use PDOException;

class SMSHistoryManager
{
    private $pdo;

    public function __construct()
    {
        $this->pdo = new PDO('sqlite:' . DB_PATH);
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    /**
     * Enregistre un nouveau SMS dans l'historique
     */
    public function recordSMS($twilioSid, $toNumber, $message, $signature = null, $status = 'sent')
    {
        $stmt = $this->pdo->prepare("
            INSERT INTO sms_history (twilio_sid, to_number, message, signature, status) 
            VALUES (?, ?, ?, ?, ?)
        ");
        return $stmt->execute([$twilioSid, $toNumber, $message, $signature, $status]);
    }

    /**
     * Met à jour le statut d'un SMS
     */
    public function updateSMSStatus($twilioSid, $status)
    {
        $stmt = $this->pdo->prepare("
            UPDATE sms_history 
            SET status = ?, updated_at = CURRENT_TIMESTAMP 
            WHERE twilio_sid = ?
        ");
        return $stmt->execute([$status, $twilioSid]);
    }

    /**
     * Récupère l'historique complet des SMS
     */
    public function getAllSMSHistory($limit = 100, $offset = 0)
    {
        $stmt = $this->pdo->prepare("
            SELECT * FROM sms_history 
            ORDER BY sent_at DESC 
            LIMIT ? OFFSET ?
        ");
        $stmt->execute([$limit, $offset]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Récupère un SMS par son SID Twilio
     */
    public function getSMSByTwilioSid($twilioSid)
    {
        $stmt = $this->pdo->prepare("SELECT * FROM sms_history WHERE twilio_sid = ?");
        $stmt->execute([$twilioSid]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    /**
     * Récupère les statistiques des SMS
     */
    public function getSMSStats()
    {
        $stats = [];
        
        // Total des SMS
        $stmt = $this->pdo->query("SELECT COUNT(*) FROM sms_history");
        $stats['total'] = $stmt->fetchColumn();
        
        // SMS par statut
        $stmt = $this->pdo->query("
            SELECT status, COUNT(*) as count 
            FROM sms_history 
            GROUP BY status
        ");
        $stats['by_status'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // SMS aujourd'hui
        $stmt = $this->pdo->query("
            SELECT COUNT(*) FROM sms_history 
            WHERE DATE(sent_at) = DATE('now')
        ");
        $stats['today'] = $stmt->fetchColumn();
        
        // SMS ce mois
        $stmt = $this->pdo->query("
            SELECT COUNT(*) FROM sms_history 
            WHERE strftime('%Y-%m', sent_at) = strftime('%Y-%m', 'now')
        ");
        $stats['this_month'] = $stmt->fetchColumn();
        
        return $stats;
    }

    /**
     * Recherche dans l'historique
     */
    public function searchSMSHistory($query, $limit = 50)
    {
        $searchTerm = '%' . $query . '%';
        $stmt = $this->pdo->prepare("
            SELECT * FROM sms_history 
            WHERE to_number LIKE ? OR message LIKE ? OR signature LIKE ?
            ORDER BY sent_at DESC 
            LIMIT ?
        ");
        $stmt->execute([$searchTerm, $searchTerm, $searchTerm, $limit]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}
