<?php
/**
 * SMS সিস্টেম ডাটাবেস সেটআপ - ডিবাগ সংস্করণ
 */

echo "🔍 SMS সিস্টেম সেটআপ শুরু হচ্ছে...\n";
echo "=====================================\n\n";

// ধাপ ১: ডাটাবেস সংযোগ চেক করুন
echo "ধাপ 1️⃣: ডাটাবেস সংযোগ চেক করছি...\n";

try {
    require_once(__DIR__ . '/config/database.php');
    echo "✓ ডাটাবেস সংযোগ সফল!\n\n";
} catch (Exception $e) {
    die("❌ ডাটাবেস সংযোগ ব্যর্থ: " . $e->getMessage() . "\n");
}

// ধাপ ২: টেবিল তৈরি করুন
echo "ধাপ 2️⃣: SMS টেবিল তৈরি করছি...\n";

$tables_created = 0;
$errors = [];

// টেবিল ১: sms_logs
try {
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS sms_logs (
            id INT AUTO_INCREMENT PRIMARY KEY,
            phone_number VARCHAR(20) NOT NULL,
            message LONGTEXT NOT NULL,
            type ENUM('text', 'otp') DEFAULT 'text',
            status ENUM('sent', 'failed', 'pending') DEFAULT 'pending',
            response JSON,
            api_status_code VARCHAR(10),
            student_id INT,
            class_id INT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_phone (phone_number),
            INDEX idx_status (status),
            INDEX idx_created (created_at),
            INDEX idx_student (student_id),
            FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE SET NULL,
            FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE SET NULL
        )
    ");
    echo "  ✓ sms_logs টেবিল তৈরি করা হয়েছে\n";
    $tables_created++;
} catch (PDOException $e) {
    $errors[] = "sms_logs: " . $e->getMessage();
    echo "  ⚠️ sms_logs: " . $e->getMessage() . "\n";
}

// টেবিল ২: sms_templates
try {
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS sms_templates (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) UNIQUE NOT NULL,
            title VARCHAR(255) NOT NULL,
            template_text LONGTEXT NOT NULL,
            description TEXT,
            placeholders JSON,
            active TINYINT(1) DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_active (active)
        )
    ");
    echo "  ✓ sms_templates টেবিল তৈরি করা হয়েছে\n";
    $tables_created++;
} catch (PDOException $e) {
    $errors[] = "sms_templates: " . $e->getMessage();
    echo "  ⚠️ sms_templates: " . $e->getMessage() . "\n";
}

// টেবিল ৩: sms_campaigns
try {
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS sms_campaigns (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            message LONGTEXT NOT NULL,
            target_type ENUM('students', 'class', 'all') DEFAULT 'students',
            target_ids JSON,
            class_id INT,
            status ENUM('draft', 'scheduled', 'sending', 'completed', 'failed') DEFAULT 'draft',
            scheduled_at DATETIME,
            started_at DATETIME,
            completed_at DATETIME,
            total_sent INT DEFAULT 0,
            total_failed INT DEFAULT 0,
            created_by INT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE SET NULL,
            FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
            INDEX idx_status (status),
            INDEX idx_scheduled (scheduled_at)
        )
    ");
    echo "  ✓ sms_campaigns টেবিল তৈরি করা হয়েছে\n";
    $tables_created++;
} catch (PDOException $e) {
    $errors[] = "sms_campaigns: " . $e->getMessage();
    echo "  ⚠️ sms_campaigns: " . $e->getMessage() . "\n";
}

// টেবিল ৪: sms_settings
try {
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS sms_settings (
            id INT AUTO_INCREMENT PRIMARY KEY,
            key_name VARCHAR(100) UNIQUE NOT NULL,
            value LONGTEXT,
            data_type ENUM('string', 'integer', 'boolean', 'json') DEFAULT 'string',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )
    ");
    echo "  ✓ sms_settings টেবিল তৈরি করা হয়েছে\n";
    $tables_created++;
} catch (PDOException $e) {
    $errors[] = "sms_settings: " . $e->getMessage();
    echo "  ⚠️ sms_settings: " . $e->getMessage() . "\n";
}

echo "\n";

// ধাপ ৩: ডিফল্ট সেটিংস যোগ করুন
echo "ধাপ 3️⃣: ডিফল্ট সেটিংস যোগ করছি...\n";

try {
    $stmt = $pdo->prepare("
        INSERT IGNORE INTO sms_settings (key_name, value, data_type)
        VALUES (?, ?, ?)
    ");
    
    $settings = [
        ['sms_enabled', '0', 'boolean'],
        ['api_key', '', 'string'],
        ['caller_id', '1234', 'string'],
        ['send_to_guardian', '1', 'boolean'],
        ['max_retries', '3', 'integer'],
        ['rate_limit', '60', 'integer'],
    ];
    
    $inserted = 0;
    foreach ($settings as $setting) {
        $stmt->execute($setting);
        $inserted++;
    }
    
    echo "  ✓ $inserted টি ডিফল্ট সেটিংস যোগ করা হয়েছে\n\n";
} catch (PDOException $e) {
    echo "  ⚠️ সেটিংস যোগ করতে সমস্যা: " . $e->getMessage() . "\n\n";
    $errors[] = "Settings: " . $e->getMessage();
}

// সারসংক্ষেপ
echo "=====================================\n";
echo "সেটআপ সম্পূর্ণ! ✓\n";
echo "=====================================\n";
echo "তৈরি হওয়া টেবিল: $tables_created/4\n";

if (!empty($errors)) {
    echo "\nসমস্যা:\n";
    foreach ($errors as $error) {
        echo "  • $error\n";
    }
} else {
    echo "\n✓ সব টেবিল সফলভাবে তৈরি হয়েছে!\n";
    echo "✓ আপনার SMS সিস্টেম ব্যবহারের জন্য প্রস্তুত!\n";
    echo "\nপরবর্তী ধাপ:\n";
    echo "1. BulkSMSDhaka থেকে API Key পান\n";
    echo "2. Admin → SMS সেটিংস এ যান\n";
    echo "3. API Key পেস্ট করুন\n";
    echo "4. API সংযোগ টেস্ট করুন\n";
}

echo "\n";
?>
