<?php
/**
 * 🔧 Setup Script - ایجاد جدول یادآورها
 * این اسکریپت جدول service_expiry_reminders را ایجاد می‌کند
 * 
 * نحوه اجرا:
 * php setup_reminders.php
 */

declare(strict_types=1);
require __DIR__ . '/../src/config.php';

echo "🔧 Setting up Service Expiry Reminders table...\n";
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n\n";

try {
    $pdo = db();
    
    // ایجاد جدول یادآورها
    $sql = "CREATE TABLE IF NOT EXISTS service_expiry_reminders (
        id INT AUTO_INCREMENT PRIMARY KEY,
        sale_id INT NOT NULL,
        user_id BIGINT NOT NULL,
        reminder_type ENUM('2_DAYS', '1_DAY', 'EXPIRED') NOT NULL,
        sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY unique_reminder (sale_id, reminder_type),
        INDEX(sale_id),
        INDEX(user_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
    
    $pdo->exec($sql);
    
    echo "✅ Table 'service_expiry_reminders' created successfully!\n\n";
    
    // نمایش آمار
    $count = $pdo->query("SELECT COUNT(*) FROM service_expiry_reminders")->fetchColumn();
    echo "📊 Current reminders count: {$count}\n\n";
    
    // نمایش سرویس‌های در حال انقضا
    $upcoming = $pdo->query("
        SELECT 
            COUNT(*) as total,
            DATE(expires_at) as expire_date
        FROM sales
        WHERE is_active = 1 
        AND expires_at >= CURDATE()
        AND expires_at <= DATE_ADD(CURDATE(), INTERVAL 7 DAYS)
        GROUP BY DATE(expires_at)
        ORDER BY expire_date ASC
    ")->fetchAll(PDO::FETCH_ASSOC);
    
    if (!empty($upcoming)) {
        echo "📅 Upcoming expiries (next 7 days):\n";
        echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n";
        foreach ($upcoming as $row) {
            echo "  - {$row['expire_date']}: {$row['total']} service(s)\n";
        }
        echo "\n";
    } else {
        echo "ℹ️  No services expiring in the next 7 days\n\n";
    }
    
    echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n";
    echo "✨ Setup completed successfully!\n";
    echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n\n";
    
    echo "🔔 Next steps:\n";
    echo "1. Set up a cron job to run expiry_reminder_worker.php daily\n";
    echo "2. See worker/README.md for detailed instructions\n\n";
    
} catch (PDOException $e) {
    echo "❌ Error: " . $e->getMessage() . "\n";
    exit(1);
}

