<?php
// 1. Tell Clickpost we are returning JSON
header('Content-Type: application/json');
// Only accept POST requests
if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    http_response_code(405);
    echo json_encode(["status" => "error", "message" => "Only POST requests are allowed"]);
    exit;
}
// 2. Read the raw data sent by Clickpost
$json = file_get_contents('php://input');
// ======= DEBUGGING LINE ADDED HERE =======
// This saves every single raw request from Clickpost into a file named clickpost_debug_log.txt
file_put_contents('clickpost_debug_log.txt', date('[Y-m-d H:i:s] ') . $json . PHP_EOL, FILE_APPEND);
// =========================================
$payload = json_decode($json, true);
if (!$payload) {
    // If the JSON is invalid, log that specific error too
    file_put_contents('clickpost_debug_log.txt', date('[Y-m-d H:i:s] ERROR: Invalid JSON payload received.') . PHP_EOL, FILE_APPEND);
    
    http_response_code(400);
    echo json_encode(["status" => "error", "message" => "Invalid JSON payload received"]);
    exit;
}
// 3. Extract the Clickpost Data
$waybill = $payload['waybill'] ?? 'N/A';
$orderId = 'N/A';
if (!empty($payload['additional']['order_id'])) {
    $orderId = $payload['additional']['order_id'];
} elseif (!empty($payload['additional']['latest_status']['reference_number'])) {
    $orderId = $payload['additional']['latest_status']['reference_number'];
}
$cpId = $payload['cp_id'] ?? 'N/A';
$accountCode = $payload['account_code'] ?? 'N/A';
$accountName = $accountCode;
$courierName = 'N/A';
if ($accountCode !== 'N/A') {
    $parts = explode(' ', $accountCode);
    $courierName = $parts[0]; // Gets the first word (e.g., "Bluedart")
}
$status = $payload['clickpost_status_description'] ?? $payload['remark'] ?? 'Status Updated';

// ======= NDR FIELDS ADDED HERE (additive only) =======
// On a Failed Delivery event, Clickpost includes the NDR reason/remark in the same payload
// we were already receiving (visible in clickpost_debug_log.txt) — we just weren't storing it.
// IMPORTANT: 'remark' and 'status' are general fields present on EVERY event (Manifested,
// Delivered, In Transit, etc all carry some remark text like "Manifest uploaded"). Only
// additional.ndr_status_code / ndr_status_description are genuinely NDR-specific (null on
// non-NDR events) — gate the capture on those being present, so a routine tracking update
// occurring after a Failed Delivery doesn't overwrite the real NDR reason with generic text.
$ndrRemark = '';
$ndrStatusCode = '';
$ndrReason = '';
$isNdrEvent = isset($payload['additional']['ndr_status_code']) || !empty($payload['additional']['ndr_status_description']);
if ($isNdrEvent) {
    if (isset($payload['additional']['ndr_status_code'])) {
        $ndrStatusCode = (string)$payload['additional']['ndr_status_code'];
    }
    if (!empty($payload['additional']['ndr_status_description'])) {
        $ndrReason = $payload['additional']['ndr_status_description'];
    }
    if (!empty($payload['remark'])) {
        $ndrRemark = $payload['remark'];
    }
}
// =======================================================

// 4. CONNECT TO POSTGRESQL
$host = 'database.xhost.co.in'; 
$db   = 'db_iqpj6ze2cf';
$user = 'db_iqpj6ze2cf';
$pass = 'O*9Mp;%5PPDyv8oD';
$dsn  = "pgsql:host=$host;port=5432;dbname=$db";
try {
    $pdo = new PDO($dsn, $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);

    // Ensure the new NDR columns exist (additive, idempotent — safe to run on every request)
    $pdo->exec("ALTER TABLE clickpost_webhook
        ADD COLUMN IF NOT EXISTS ndr_remark VARCHAR(500) DEFAULT '',
        ADD COLUMN IF NOT EXISTS ndr_status_code VARCHAR(20) DEFAULT '',
        ADD COLUMN IF NOT EXISTS ndr_reason VARCHAR(255) DEFAULT ''");

    // 5. UPSERT Query
    // NDR columns use COALESCE(NULLIF(...)) so a later non-NDR status update (e.g. Delivered)
    // does not blank out the NDR reason/remark captured from an earlier Failed Delivery event.
    $sql = "
        INSERT INTO clickpost_webhook 
            (updated_at, order_id, waybill, account_name, cp_id, courier_name, status, ndr_remark, ndr_status_code, ndr_reason)
        VALUES 
            (CURRENT_TIMESTAMP, :order_id, :waybill, :account_name, :cp_id, :courier_name, :status, :ndr_remark, :ndr_status_code, :ndr_reason)
        ON CONFLICT (waybill) 
        DO UPDATE SET 
            updated_at = CURRENT_TIMESTAMP,
            order_id = EXCLUDED.order_id,
            account_name = EXCLUDED.account_name,
            cp_id = EXCLUDED.cp_id,
            courier_name = EXCLUDED.courier_name,
            status = EXCLUDED.status,
            ndr_remark = COALESCE(NULLIF(EXCLUDED.ndr_remark, ''), clickpost_webhook.ndr_remark),
            ndr_status_code = COALESCE(NULLIF(EXCLUDED.ndr_status_code, ''), clickpost_webhook.ndr_status_code),
            ndr_reason = COALESCE(NULLIF(EXCLUDED.ndr_reason, ''), clickpost_webhook.ndr_reason);
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([
        ':order_id' => $orderId,
        ':waybill' => $waybill,
        ':account_name' => $accountName,
        ':cp_id' => $cpId,
        ':courier_name' => $courierName,
        ':status' => $status,
        ':ndr_remark' => $ndrRemark,
        ':ndr_status_code' => $ndrStatusCode,
        ':ndr_reason' => $ndrReason
    ]);
    // 6. Return success to Clickpost
    http_response_code(200);
    echo json_encode(["status" => "success", "waybill" => $waybill]);
} catch (Exception $e) {
    // Return error to Clickpost if something fails
    http_response_code(500);
    echo json_encode(["status" => "error", "message" => $e->getMessage()]);
    
    // Log the error to your server for debugging
    error_log("Clickpost DB Error: " . $e->getMessage());
}
?>