<?php
/**
 * Diagnostic script for Apilo integration on the instance DB
 * Temporary — delete after diagnosis
 */

$db_host = 'host700513.hostido.net.pl';
$db_user = 'host700513_pomysloweprezenty-pl';
$db_pass = 'QBVbveHAzR78UN8pc7Um';
$db_name = 'host700513_pomysloweprezenty-pl';

$pdo = new PDO("mysql:host=$db_host;dbname=$db_name;charset=utf8", $db_user, $db_pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

echo "=== 1. APILO SETTINGS ===\n";
$stmt = $pdo->query("SELECT name, LEFT(value, 200) as value FROM pp_shop_apilo_settings ORDER BY name");
foreach ($stmt as $row) {
    echo sprintf("  %-35s = %s\n", $row['name'], $row['value']);
}

echo "\n=== 2. PENDING ORDERS (apilo_order_id IS NULL) ===\n";
$stmt = $pdo->query("SELECT COUNT(*) as cnt FROM pp_shop_orders WHERE apilo_order_id IS NULL");
$row = $stmt->fetch();
echo "  Total with NULL: " . $row['cnt'] . "\n";

$stmt = $pdo->query("SELECT id, date_order, status, apilo_order_id FROM pp_shop_orders WHERE apilo_order_id IS NULL ORDER BY date_order DESC LIMIT 10");
echo "  Last 10 NULL orders:\n";
foreach ($stmt as $row) {
    echo sprintf("    #%s  date=%s  status=%s\n", $row['id'], $row['date_order'], $row['status']);
}

echo "\n=== 3. FAILED ORDERS (apilo_order_id = -1) ===\n";
$stmt = $pdo->query("SELECT COUNT(*) as cnt FROM pp_shop_orders WHERE apilo_order_id = -1");
$row = $stmt->fetch();
echo "  Total with -1: " . $row['cnt'] . "\n";

$stmt = $pdo->query("SELECT id, date_order, status FROM pp_shop_orders WHERE apilo_order_id = -1 ORDER BY date_order DESC LIMIT 10");
echo "  Last 10 failed orders:\n";
foreach ($stmt as $row) {
    echo sprintf("    #%s  date=%s  status=%s\n", $row['id'], $row['date_order'], $row['status']);
}

echo "\n=== 4. SKIPPED ORDERS (apilo_order_id = -2) ===\n";
$stmt = $pdo->query("SELECT COUNT(*) as cnt FROM pp_shop_orders WHERE apilo_order_id = -2");
$row = $stmt->fetch();
echo "  Total with -2: " . $row['cnt'] . "\n";

echo "\n=== 5. RECENT APILO LOGS (pp_log) ===\n";
$stmt = $pdo->query("SELECT id, action, order_id, message, date FROM pp_log WHERE action LIKE '%apilo%' OR action LIKE '%send_order%' OR action LIKE '%token%' OR action LIKE '%keepalive%' ORDER BY id DESC LIMIT 20");
$rows = $stmt->fetchAll();
if (empty($rows)) {
    echo "  No Apilo logs found in pp_log\n";
    // Try broader search
    $stmt = $pdo->query("SELECT id, action, order_id, message, date FROM pp_log ORDER BY id DESC LIMIT 10");
    $rows = $stmt->fetchAll();
    echo "  Last 10 logs (any type):\n";
}
foreach ($rows as $row) {
    echo sprintf("  [%s] #%s action=%s order=%s msg=%s\n", $row['date'], $row['id'], $row['action'], $row['order_id'], substr($row['message'], 0, 120));
}

echo "\n=== 6. CRON JOBS STATUS ===\n";
$stmt = $pdo->query("SELECT job_type, status, COUNT(*) as cnt FROM pp_cron_jobs GROUP BY job_type, status ORDER BY job_type, status");
echo "  Job type / status / count:\n";
foreach ($stmt as $row) {
    echo sprintf("    %-30s  %-12s  %s\n", $row['job_type'], $row['status'], $row['cnt']);
}

echo "\n=== 7. RECENT CRON EXECUTIONS ===\n";
$stmt = $pdo->query("SELECT id, job_type, status, created_at, processed_at FROM pp_cron_jobs ORDER BY id DESC LIMIT 15");
foreach ($stmt as $row) {
    echo sprintf("  #%s type=%s status=%s created=%s processed=%s\n", $row['id'], $row['job_type'], $row['status'], $row['created_at'], $row['processed_at']);
}

echo "\n=== 8. SUCCESSFULLY SENT ORDERS (last 5) ===\n";
$stmt = $pdo->query("SELECT id, date_order, apilo_order_id, status FROM pp_shop_orders WHERE apilo_order_id > 0 ORDER BY date_order DESC LIMIT 5");
foreach ($stmt as $row) {
    echo sprintf("  #%s  date=%s  apilo_id=%s  status=%s\n", $row['id'], $row['date_order'], $row['apilo_order_id'], $row['status']);
}

echo "\nDone.\n";