<?php
/**
 * SMART WA REMINDER (OTAK KURIR)
 * Dipanggil oleh cron_registry.php (slug: wa_reminder) setiap jam 09:00
 */
date_default_timezone_set('Asia/Jakarta');

if (php_sapi_name() !== 'cli' && empty($GLOBALS['CRON_MANAGER_EXEC'])) {
    die('Access denied.');
}

require_once __DIR__ . '/../includes/db.php';
require_once __DIR__ . '/../includes/functions.php';

$db = db();
$today = date('Y-m-d');
$h3 = date('Y-m-d', strtotime('+3 days'));
$h1 = date('Y-m-d', strtotime('+1 day'));

echo "=== CRON REMINDER START: " . date('Y-m-d H:i:s') . " ===\n";
echo "DEBUG: Today=$today, H-3=$h3, H-1=$h1\n\n";

// ============================================================================
// 1. FUNGSI AMBIL TEMPLATE DARI DATABASE
// ============================================================================
function getTemplateFromDB($db, $slug) {
    echo "  [DEBUG] Mencari template dengan slug: $slug\n";
    
    try {
        $tableExists = $db->query("SHOW TABLES LIKE 'wa_templates'")->fetch();
        if (!$tableExists) {
            echo "  [DEBUG] ❌ Tabel wa_templates TIDAK ADA!\n";
            return getDefaultTemplate($slug);
        }
        
        $stmt = $db->prepare("
            SELECT message 
            FROM wa_templates 
            WHERE slug = ? AND (branch_id IS NULL OR branch_id = 0)
            ORDER BY branch_id DESC 
            LIMIT 1
        ");
        $stmt->execute([$slug]);
        $msg = $stmt->fetchColumn();
        
        if ($msg) {
            echo "  [DEBUG] ✅ Template ditemukan dari database (" . strlen($msg) . " karakter)\n";
            return $msg;
        } else {
            echo "  [DEBUG] ⚠️ Template tidak ditemukan di database, gunakan default\n";
        }
    } catch (\Throwable $e) {
        echo "  [DEBUG] ❌ Error query: " . $e->getMessage() . "\n";
    }
    
    return getDefaultTemplate($slug);
}

function getDefaultTemplate($slug) {
    $defaults = [
        'reminder_h3'  => "🔔 *PENGINGAT TAGIHAN (H-3)*\n\nYth. [nama],\n\nTagihan Anda akan jatuh tempo dalam 3 hari.\nNo. Pelanggan: [customer_number]\nPaket: [paket]\nNominal: Rp {amount}\n\nLink pembayaran: [Link Pembayaran]\n\nTerima kasih.",
        'reminder_h1'  => "⏰ *PENGINGAT TAGIHAN (H-1)*\n\nYth. [nama],\n\nTagihan Anda akan jatuh tempo BESOK.\nNo. Pelanggan: [customer_number]\nPaket: [paket]\nNominal: Rp {amount}\n\nLink pembayaran: [Link Pembayaran]\n\nTerima kasih.",
        'reminder_due' => "⚠️ *JATUH TEMPO HARI INI*\n\nYth. [nama],\n\nHari ini batas akhir pembayaran.\nNo. Pelanggan: [customer_number]\nPaket: [paket]\nNominal: Rp {amount}\n\nLink pembayaran: [Link Pembayaran]\n\nTerima kasih."
    ];
    return $defaults[$slug] ?? "Halo [nama], tagihan Anda sudah jatuh tempo.";
}

// ============================================================================
// 2. FUNGSI KIRIM WHATSAPP (WABLAS API)
// ============================================================================
function sendWablasCron($phone, $message, $db) {
    try {
        $token = null;
        $baseUrl = null;
        
        $hasBranchCol = false;
        try {
            $checkCol = $db->query("SHOW COLUMNS FROM wa_config LIKE 'branch_id'")->fetch();
            $hasBranchCol = !empty($checkCol);
        } catch (\Throwable $e) {}
        
        if ($hasBranchCol) {
            $stmt = $db->query("
                SELECT setting_key, setting_value 
                FROM wa_config 
                WHERE setting_key IN ('wablas_token', 'wablas_base_url')
                AND (branch_id IS NULL OR branch_id = 0)
                LIMIT 2
            ");
        } else {
            $stmt = $db->query("
                SELECT setting_key, setting_value 
                FROM wa_config 
                WHERE setting_key IN ('wablas_token', 'wablas_base_url')
                LIMIT 2
            ");
        }
        
        while ($row = $stmt->fetch()) {
            if ($row['setting_key'] === 'wablas_token') $token = $row['setting_value'];
            if ($row['setting_key'] === 'wablas_base_url') $baseUrl = rtrim($row['setting_value'], '/');
        }
        
        if (!$token || !$baseUrl) {
            echo "  [SKIP] Wablas belum dikonfigurasi di database.\n";
            return ['success' => false, 'error' => 'Wablas not configured'];
        }

        $url = $baseUrl . '/send-message?' . http_build_query([
            'phone' => $phone, 
            'message' => $message, 
            'token' => $token
        ]);
        
        $curl = curl_init();
        curl_setopt_array($curl, [
            CURLOPT_URL => $url,
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_TIMEOUT => 10,
            CURLOPT_SSL_VERIFYHOST => 0,
            CURLOPT_SSL_VERIFYPEER => 0
        ]);
        $result = curl_exec($curl);
        $httpCode = curl_getinfo($curl, CURLINFO_HTTP_CODE);
        curl_close($curl);
        
        $decoded = json_decode($result, true);
        $success = ($httpCode == 200 && isset($decoded['status']) && $decoded['status'] === true);
        
        return [
            'success' => $success,
            'http_code' => $httpCode,
            'response' => $decoded,
            'error' => $decoded['message'] ?? ($decoded['error'] ?? 'Unknown error')
        ];
    } catch (\Throwable $e) {
        return ['success' => false, 'error' => $e->getMessage()];
    }
}

// ============================================================================
// 3. FUNGSI CATAT LOG KE broadcast_logs
// ============================================================================
function logBroadcastMessage($db, $branchId, $title, $message, $targetType, $totalTargets, $sentCount, $failedCount, $source = 'cron_reminder') {
    echo "  [DEBUG] Mencatat log ke broadcast_logs...\n";
    
    try {
        $columns = $db->query("SHOW COLUMNS FROM broadcast_logs")->fetchAll(PDO::FETCH_COLUMN);
        
        $status = ($failedCount > 0 && $sentCount > 0) ? 'partial' : ($sentCount > 0 ? 'completed' : 'failed');
        
        $insertCols = ['title', 'message', 'target_type', 'total_targets', 'sent_count', 'failed_count', 'status', 'branch_id', 'created_at'];
        $placeholders = ['?', '?', '?', '?', '?', '?', '?', '?', 'NOW()'];
        $params = [
            $title, $message, $targetType,
            $totalTargets, $sentCount, $failedCount,
            $status,
            $branchId ?: null
        ];

        if (in_array('source', $columns)) {
            $insertCols[] = 'source';
            $placeholders[] = '?';
            $params[] = $source;
        }
        if (in_array('total_sent', $columns)) {
            $insertCols[] = 'total_sent';
            $placeholders[] = '?';
            $params[] = $sentCount;
        }
        if (in_array('total_failed', $columns)) {
            $insertCols[] = 'total_failed';
            $placeholders[] = '?';
            $params[] = $failedCount;
        }
        if (in_array('created_by_name', $columns)) {
            $insertCols[] = 'created_by_name';
            $placeholders[] = '?';
            $params[] = 'Sistem (Cron Job)';
        }
        if (in_array('created_by', $columns) && !in_array('created_by', $insertCols)) {
            $insertCols[] = 'created_by';
            $placeholders[] = 'NULL';
        }

        $sql = "INSERT INTO broadcast_logs (" . implode(',', $insertCols) . ") VALUES (" . implode(',', $placeholders) . ")";
        
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        
        echo "  [DEBUG] ✅ Log tercatat (ID: " . $db->lastInsertId() . ") dengan status='$status'\n";
        
    } catch (\Throwable $e) {
        echo "  [LOG ERROR] ❌ Gagal insert log: " . $e->getMessage() . "\n";
    }
}

// ============================================================================
// 3.5 ⭐ FUNGSI BARU: LOG EKSEKUSI CRON KE TABEL cron_logs
// ============================================================================
function logCronExecution($db, $slug, $status, $message, $details = null) {
    try {
        // Cek apakah tabel cron_logs ada
        $tableExists = $db->query("SHOW TABLES LIKE 'cron_logs'")->fetch();
        if (!$tableExists) {
            echo "  [CRON LOG] ⚠️ Tabel cron_logs belum ada. Skip logging.\n";
            return;
        }
        
        $stmt = $db->prepare("
            INSERT INTO cron_logs (slug, status, message, details, executed_at) 
            VALUES (?, ?, ?, ?, NOW())
        ");
        $stmt->execute([
            $slug, 
            $status, 
            $message, 
            is_array($details) ? json_encode($details) : $details
        ]);
        
        echo "  [CRON LOG] ✅ Eksekusi tercatat di cron_logs (status: $status)\n";
    } catch (\Throwable $e) {
        echo "  [CRON LOG] ❌ Gagal mencatat ke cron_logs: " . $e->getMessage() . "\n";
    }
}

// ============================================================================
// 4. LOGIKA UTAMA: CARI PELANGGAN & KIRIM PESAN
// ============================================================================
function processReminders($db, $date, $slug, $label) {
    echo "\n--- Processing $label (due_date: $date, slug: $slug) ---\n";
    
    $template = getTemplateFromDB($db, $slug);
    
    $sql = "SELECT b.due_date, b.amount, b.id as bill_id,
                   c.id as customer_id, c.name, c.phone, c.customer_number, c.branch_id,
                   p.name as package_name
            FROM bills b
            JOIN customers c ON b.customer_id = c.id
            LEFT JOIN packages p ON c.package_id = p.id
            WHERE b.due_date = ? AND b.status = 'unpaid' 
            AND c.phone IS NOT NULL AND c.phone != ''";
            
    $stmt = $db->prepare($sql);
    $stmt->execute([$date]);
    $rows = $stmt->fetchAll();
    
    echo "  [DEBUG] Ditemukan " . count($rows) . " pelanggan untuk $label\n";
    
    if (empty($rows)) {
        echo "  [DEBUG] Tidak ada pelanggan yang perlu di-reminder. Skip.\n";
        return ['sent' => 0, 'failed' => 0];
    }

    $totalSent = 0;
    $totalFailed = 0;
    $groupedByBranch = [];

    foreach ($rows as $row) {
        $phone = preg_replace('/[^0-9]/', '', $row['phone']);
        if (substr($phone, 0, 1) === '0') $phone = '62' . substr($phone, 1);
        elseif (substr($phone, 0, 2) !== '62') $phone = '62' . $phone;
        
        $linkBayar = "https://lintasfiber.my.id/public/pay.php?id=" . urlencode($row['customer_number']); 
        
        $msg = str_replace(
            ['[customer_number]', '[nama]', '[paket]', '[Link Pembayaran]', '{name}', '{date}', '{amount}'],
            [
                $row['customer_number'] ?? '-', 
                $row['name'], 
                $row['package_name'] ?? 'Internet', 
                $linkBayar,
                $row['name'],
                date('d/m/Y', strtotime($row['due_date'])),
                number_format($row['amount'], 0, ',', '.')
            ],
            $template
        );
        
        $result = sendWablasCron($phone, $msg, $db);
        
        $branchId = $row['branch_id'] ?? 0;
        
        if (!isset($groupedByBranch[$branchId])) {
            $groupedByBranch[$branchId] = [
                'sent' => 0,
                'failed' => 0,
                'message_sample' => $msg
            ];
        }

        if ($result['success']) {
            $totalSent++;
            $groupedByBranch[$branchId]['sent']++;
            echo "  [$label] ✅ -> {$row['name']} ($phone)\n";
        } else {
            $totalFailed++;
            $groupedByBranch[$branchId]['failed']++;
            echo "  [$label] ❌ -> {$row['name']} ($phone) - Error: " . $result['error'] . "\n";
        }
        
        usleep(500000);
    }
    
    foreach ($groupedByBranch as $branchId => $stats) {
        $totalTargets = $stats['sent'] + $stats['failed'];
        $title = "🔔 Reminder $label Otomatis ($totalTargets pesan)";
        
        logBroadcastMessage(
            $db, 
            $branchId, 
            $title, 
            $stats['message_sample'], 
            'cron_reminder_' . strtolower(str_replace([' ', '-'], '_', $label)),
            $totalTargets,
            $stats['sent'],
            $stats['failed'],
            'cron_reminder'
        );
    }
    
    return ['sent' => $totalSent, 'failed' => $totalFailed];
}

// ============================================================================
// 5. ⭐ EKSEKUSI DENGAN LOGGING (UPDATED)
// ============================================================================
$startTime = microtime(true);
$totalSent = 0;
$totalFailed = 0;
$summary = [];

try {
    $r1 = processReminders($db, $h3, 'reminder_h3', 'H-3');
    $r2 = processReminders($db, $h1, 'reminder_h1', 'H-1');
    $r3 = processReminders($db, $today, 'reminder_due', 'Hari H');
    
    $totalSent = $r1['sent'] + $r2['sent'] + $r3['sent'];
    $totalFailed = $r1['failed'] + $r2['failed'] + $r3['failed'];
    
    $duration = round(microtime(true) - $startTime, 2);
    
    $summary = [
        'h3' => ['sent' => $r1['sent'], 'failed' => $r1['failed']],
        'h1' => ['sent' => $r2['sent'], 'failed' => $r2['failed']],
        'hari_h' => ['sent' => $r3['sent'], 'failed' => $r3['failed']],
        'duration_seconds' => $duration,
        'total_sent' => $totalSent,
        'total_failed' => $totalFailed
    ];
    
    // Tentukan status akhir
    if ($totalSent > 0 && $totalFailed == 0) {
        $cronStatus = 'success';
    } elseif ($totalSent > 0 && $totalFailed > 0) {
        $cronStatus = 'partial';
    } elseif ($totalSent == 0 && $totalFailed > 0) {
        $cronStatus = 'error';
    } else {
        $cronStatus = 'success'; // Tidak ada yang perlu dikirim
    }
    
    $cronMessage = "H-3: {$r1['sent']}/{$r1['failed']} | H-1: {$r2['sent']}/{$r2['failed']} | HariH: {$r3['sent']}/{$r3['failed']} | Total: {$totalSent} sent, {$totalFailed} failed ({$duration}s)";
    
    // ⭐ Catat ke tabel cron_logs
    logCronExecution($db, 'wa_reminder', $cronStatus, $cronMessage, $summary);
    
    echo "\n=== SELESAI (Durasi: {$duration}s) ===\n";
    echo "H-3: {$r1['sent']} terkirim, {$r1['failed']} gagal\n";
    echo "H-1: {$r2['sent']} terkirim, {$r2['failed']} gagal\n";
    echo "Hari H: {$r3['sent']} terkirim, {$r3['failed']} gagal\n";
    echo "TOTAL: $totalSent terkirim, $totalFailed gagal\n";
    echo "STATUS: $cronStatus\n";
    
} catch (\Throwable $e) {
    $duration = round(microtime(true) - $startTime, 2);
    $errorMessage = 'Fatal error: ' . $e->getMessage();
    
    echo "\n=== FATAL ERROR ===\n";
    echo $errorMessage . "\n";
    echo $e->getTraceAsString() . "\n";
    
    // ⭐ Catat error ke cron_logs
    logCronExecution($db, 'wa_reminder', 'error', $errorMessage, [
        'trace' => $e->getTraceAsString(),
        'duration_seconds' => $duration
    ]);
}