";
print_r($payload);
$rokuDeviceID = $payload['customerId'] ?? '';
$TxnId = $payload['transactionId'] ?? '';
/* ==========================
DB CONNECTION
========================== */
$conn = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME, $DB_PORT);
if ($conn->connect_error) {
http_response_code(500);
exit('DB connection failed');
}
/* ============================================================
FETCH ORDER USING apsflyr_id = rokuDeviceID
============================================================ */
if ($rokuDeviceID != '') {
$fetchSql = "
SELECT
OD.order_id,
OD.item_id AS package_id,
OD.subscriber_id,
OD.start_date,
OD.end_date,
OD.item_name,
OD.item_price,
OD.currency
FROM tbl_order_details OD
JOIN tbl_order O ON O.id = OD.order_id
WHERE OD.apsflyr_id = ?
ORDER BY OD.id DESC
LIMIT 1
";
$stmtFetch = $conn->prepare($fetchSql);
$stmtFetch->bind_param("s", $rokuDeviceID);
$stmtFetch->execute();
$resultFetch = $stmtFetch->get_result();
if ($resultFetch->num_rows > 0) {
$row = $resultFetch->fetch_assoc();
$order_id = $row['order_id'];
$user_id = $row['subscriber_id'];
$package_id = $row['package_id'];
/* ==========================================
DUPLICATE CHECK
========================================== */
$checkSql = "
SELECT id FROM tbl_customer_subscriptions
WHERE order_id = ?
AND subscriber_id = ?
AND package_id = ?
AND status = '2'
LIMIT 1
";
$stmtCheck = $conn->prepare($checkSql);
$stmtCheck->bind_param("iii", $order_id, $user_id, $package_id);
$stmtCheck->execute();
$resultCheck = $stmtCheck->get_result();
if ($resultCheck->num_rows == 0) {
/* ==========================================
INSERT NEW SUBSCRIPTION
========================================== */
$trans_id = "ROKU-" . uniqid();
$insertSql = "
INSERT INTO tbl_customer_subscriptions (
app_id,
order_id,
trans_id,
pg_ref_id,
gateway_subscription_id,
pg_payment_data,
channel,
package_id,
package_title,
price,
subscriber_id,
gateway_type,
pg_name,
start_date,
end_date,
currency,
created,
status,
platform,
purchase_token,
device_id,
package_mode
)
VALUES (
?, ?, ?, ?, ?, ?, 'roku', ?, ?, ?, ?, 'roku', 'roku',
?, ?, ?, NOW(), '2', 'webhook', ?, ?, 'OTT'
)
";
$pg_data = json_encode($payload);
$stmtInsert = $conn->prepare($insertSql);
if (!$stmtInsert) {
die("Prepare failed: " . $conn->error);
}
$stmtInsert->bind_param(
"iissssisdisssss",
$app_id, // i
$order_id, // i
$trans_id, // s
$trans_id, // s
$trans_id, // s
$pg_data, // s
$package_id, // i
$row['item_name'], // s
$row['item_price'], // d
$user_id, // i
$row['start_date'], // s
$row['end_date'], // s
$row['currency'], // s
$rokuDeviceID, // s
$rokuDeviceID // s
);
if ($stmtInsert->execute()) {
echo "Subscription inserted successfully
";
} else {
echo "Insert failed: " . $stmtInsert->error . "
";
}
$stmtInsert->close();
} else {
echo "Subscription already exists
";
}
} else {
echo "No order found for device id: " . $rokuDeviceID . "
";
}
}
/* ==========================
INSERT WEBHOOK LOG
========================== */
$sql = "
INSERT INTO roku_webhook_log
(hook_event, log_data, gateway_ref_id, user_id, order_id, app_id, source,txn_id)
VALUES (?, ?, ?, ?, ?, ?, ?,?)";
$source = 'ROKU';
$stmt = $conn->prepare($sql);
$stmt->bind_param(
"sssissss",
$eventType,
$rawPayload,
$eventId,
$user_id,
$order_id,
$app_id,
$source,
$TxnId
);
$stmt->execute();
$stmt->close();
$conn->close();
/* ==========================
RESPONSE
========================== */
http_response_code(200);
echo 'Webhook received';
?>