<?php

ini_set('display_errors', 1);
error_reporting(E_ALL);

/* ========================== DB CONFIG ========================== */
$DB_HOST = '136.112.153.136';
$DB_USER = 'lac';
$DB_PASS = 'kschhiKHSH23243#';
$DB_NAME = 'lac_prod';
$DB_PORT = '3306';

/* ========================== READ RAW PAYLOAD ========================== */

$rawPayload = file_get_contents("php://input");
$payload = json_decode($rawPayload, true);

// Allow only POST requests
if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    http_response_code(405);
    echo "Invalid request method. POST required.";
    exit;
}

// Prevent direct browser hit (no body data)
if (empty(file_get_contents("php://input"))) {
    http_response_code(400);
    echo "Invalid request. No data received.";
    exit;
}

$roku_price     = $payload['price'] ?? '';
$transactionType     = $payload['transactionType'] ?? '';
$customerID     = $payload['customerId'] ?? '';
$TxnId          = $payload['transactionId'] ?? '';
$originalTxnId  = $payload['originalTransactionId'] ?? '';
$startDate      = !empty($payload['eventDate']) ? date("Y-m-d H:i:s", strtotime($payload['eventDate'])) : null;
$expireDate     = !empty($payload['expirationDate']) ? date("Y-m-d H:i:s", strtotime($payload['expirationDate'])) : null;

$eventType = $transactionType ?? 'unknown_event';
$eventId   = $payload['id']   ?? null;

/* ========================== DEFAULT VALUES ========================== */
$subscription_id = null;
$user_id  = null;
$order_id = null;
$package_id = null;
$app_id   = 5013;
$source   = 'ROKU';

/* ========================== 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');
}

/* ========================== 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, org_txn_id)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

$stmt = $conn->prepare($sql);
$stmt->bind_param(
    "sssiiisss",
    $eventType,
    $rawPayload,
    $eventId,
    $user_id,
    $order_id,
    $app_id,
    $source,
    $TxnId,
    $originalTxnId
);
$stmt->execute();
$stmt->close();

/* ============================================================ FETCH ORDER USING originalTxnId ============================================================ */

if (!empty($originalTxnId)) {

    $fetchSql = "
        SELECT 
            OD.order_id,
            OD.item_id AS package_id,
            OD.subscriber_id,
            OD.item_name,
            OD.item_price,
            OD.currency,
            C.org_txn_id,
            OD.period,
            OD.period_interval,
            OD.autorenew
        FROM tbl_order_details OD
        JOIN roku_customer_mapping C 
            ON C.uid = OD.subscriber_id
        WHERE C.org_txn_id = ?
        ORDER BY OD.id DESC LIMIT 1
    ";

    $stmtFetch = $conn->prepare($fetchSql);
    $stmtFetch->bind_param("s", $originalTxnId);
    $stmtFetch->execute();
    $resultFetch = $stmtFetch->get_result();

    if ($resultFetch->num_rows > 0) {

        // ✅ ORDER DATA (separate variable)
        $orderRow = $resultFetch->fetch_assoc();

        $order_id   = $orderRow['order_id'];
        $user_id    = $orderRow['subscriber_id'];
        $package_id = $orderRow['package_id'];
        $period     = $orderRow['period'];
        $autorenew  = $orderRow['autorenew'];
        $period_interval = $orderRow['period_interval'];

        $getdeviceSql = "SELECT device_id, currency, currency_id FROM tbl_order WHERE id = '$order_id' LIMIT 1";
        $deviceResult = $conn->query($getdeviceSql);
        $device_id = '';
        $currency = '';
        $currency_id = '';
        if ($deviceResult && $deviceResult->num_rows > 0) {
            $deviceRow = $deviceResult->fetch_assoc();
            $device_id = $deviceRow['device_id'];
            $currency = $deviceRow['currency'];
            $currency_id = $deviceRow['currency_id'];
        }

        /* ========================================== DUPLICATE CHECK ========================================== */

        $checkSql = "SELECT * FROM tbl_customer_subscriptions WHERE order_id = ? AND subscriber_id = ? AND package_id = ? AND status IN ('2','7') ORDER BY id DESC LIMIT 1
        ";

        $stmtCheck = $conn->prepare($checkSql);
        $stmtCheck->bind_param("iii", $order_id, $user_id, $package_id);
        $stmtCheck->execute();
        $resultCheck = $stmtCheck->get_result();

        $status = null;
        $existingId = null;
        $platform = 'webhook';
        $purchaseType = 'PURCHASED';
        $existing_end_date = null;
        
        if ($resultCheck->num_rows > 0) {
            // ✅ SUBSCRIPTION DATA (separate variable)
            $subRow = $resultCheck->fetch_assoc();
            $purchaseType = 'RENEWED';
            $status = $subRow['status'];
            $existingId = $subRow['id'];
            $existing_end_date = $subRow['end_date'];
        }

        $stmtCheck->close();

        /* ========================================== IF ACTIVE → CLOSE OLD SUBSCRIPTION ========================================== */

        if ($status === '2' && $existingId && $existing_end_date < date("Y-m-d H:i:s")) {
            $stmtUpdate = $conn->prepare(" UPDATE tbl_customer_subscriptions SET status = '7', end_date = NOW() WHERE id = ? ");
            $stmtUpdate->bind_param("i", $existingId);
            $stmtUpdate->execute();
            $stmtUpdate->close();
        }

        /* ========================================== INSERT NEW SUBSCRIPTION ========================================== */
        
        if ($eventType == 'Sale') {

        $pg_data = json_encode($payload);

        if($roku_price == 0){
            $device_id = 1;
        }else{
            $device_id = 0;
        }


        $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, purchase_type, autorenew, period, period_interval, currency_id) 
                    VALUES 
                    ('$app_id', '$order_id', '$TxnId', '$TxnId', '$originalTxnId', '".mysqli_real_escape_string($conn,$pg_data)."', 'roku', '$package_id', '".mysqli_real_escape_string($conn,$orderRow['item_name'])."', '$roku_price', '$user_id', '29', 'roku', '$startDate', '$expireDate', '$currency', NOW(), '2', 'webhook', '', '$device_id', 'OTT', '$purchaseType', '$autorenew', '$period', '$period_interval', '$currency_id')";
        

        $checkduplisubs = "SELECT id FROM tbl_customer_subscriptions WHERE subscriber_id = '$user_id' AND order_id ='$order_id' AND package_id = '$package_id' AND status = '2' LIMIT 1";
        $resultCheckDup = $conn->query($checkduplisubs);

        
            if ($resultCheckDup && $resultCheckDup->num_rows > 0) {
                echo "Duplicate subscription found for order_id: $order_id<br>";
            }else{
                    if($conn->query($insertSql)){ 
                        $updateOrderSql = "UPDATE tbl_order SET payment_status='2', order_status='2' WHERE id='$order_id'";
                        if($conn->query($updateOrderSql)){
                            echo "Order status updated successfully<br>";
                        } else {
                            echo "Order update failed: ".$conn->error;
                        }
                        echo "Subscription inserted successfully & update order status to paid<br> "; 
                    } else { 
                        echo "Insert failed: ".$conn->error; 
                    }
            }
            
        }


        // print_r($payload); die('ssssss');

         if ($eventType == 'Cancellation') {

            // Cancel subscription
            $stmt = $conn->prepare("
                UPDATE tbl_customer_subscriptions SET is_cancelled = '1', cancelled_at = NOW() WHERE order_id = ?  AND subscriber_id = ?  AND package_id = ? AND status = '2'
            ");

            $stmt->bind_param("iii", $order_id, $user_id, $package_id);
            $stmt->execute();

            $cancelled = ($stmt->affected_rows > 0);
            $stmt->close();

            // Update order table (using prepared statement for safety)
            $stmt2 = $conn->prepare("
                UPDATE tbl_order SET order_status = '4', payment_status = '4' WHERE id = ?
            ");
            
            $stmt2->bind_param("i", $order_id);
            $stmt2->execute();
            $stmt2->close();

            echo $cancelled ? "Subscription cancelled successfully for order_id: $order_id<br>" : "No active subscription found to cancel for order_id: $order_id<br>";
        }


        if ($eventType == 'Resubscribe') {

            // Resubscribe subscription
            $stmt = $conn->prepare("
                UPDATE tbl_customer_subscriptions SET is_cancelled = '0', cancelled_at = NOW() WHERE order_id = ?  AND subscriber_id = ?  AND package_id = ? AND status = '2'
            ");

            $stmt->bind_param("iii", $order_id, $user_id, $package_id);
            $stmt->execute();

            $cancelled = ($stmt->affected_rows > 0);
            $stmt->close();

            // Update order table (using prepared statement for safety)
            $stmt2 = $conn->prepare("
                UPDATE tbl_order SET order_status = '2', payment_status = '2' WHERE id = ?
            ");
            
            $stmt2->bind_param("i", $order_id);
            $stmt2->execute();
            $stmt2->close();

            echo $cancelled ? "Subscription resubscribed successfully for order_id: $order_id<br>" : "No active subscription found to resubscribe for order_id: $order_id<br>";

        }


    } else {
        echo "No order found for original txn: " . $originalTxnId . " -- ";
    }

    $stmtFetch->close();
}

$conn->close();

http_response_code(200);
echo "Webhook received successfully";