<?php
// index.php - Order Status Manager with Starpago API

header('Content-Type: application/json');
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Database configuration
$db_host = 'localhost';
$db_user = 'bd7lottery538_dbmarufrrrj';
$db_pass = 'bd7lottery538_dbmarufrrrj';
$db_name = 'bd7lottery538_dbmarufrrrj';

// Connect to database
$conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
if (!$conn) {
    die(json_encode([
        'success' => false, 
        'error' => 'Database connection failed: ' . mysqli_connect_error()
    ]));
}

// Set charset
mysqli_set_charset($conn, 'utf8');

// Starpago API Configuration
$appId = '09396ed0b162ba2bb4739b281c7ed790';
$secretKey = '4256db59c4b6d787bd8a4292406f14c9';
$host = 'https://bd.api.starpago.com';
$apiPath = '/api/v2/payment/order/query';

// =============================================
// STEP 1: Check and add created_at column if not exists
// =============================================
$check_column = "SHOW COLUMNS FROM thevani LIKE 'created_at'";
$column_result = mysqli_query($conn, $check_column);

if (mysqli_num_rows($column_result) == 0) {
    // Add created_at column
    $add_column = "ALTER TABLE thevani ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER sthiti";
    mysqli_query($conn, $add_column);
    
    // Update existing records with current timestamp
    $update_timestamp = "UPDATE thevani SET created_at = NOW() WHERE created_at IS NULL";
    mysqli_query($conn, $update_timestamp);
}

// =============================================
// STEP 2: Mark orders as expired after 15 minutes (sthiti = 2)
// =============================================
$expire_sql = "UPDATE thevani 
               SET sthiti = 2 
               WHERE sthiti = 0 
               AND created_at <= DATE_SUB(NOW(), INTERVAL 15 MINUTE)";

mysqli_query($conn, $expire_sql);
$expired_count = mysqli_affected_rows($conn);

// =============================================
// STEP 3: Get active pending orders (less than 15 minutes old)
// =============================================
$pending_orders = [];
$sql = "SELECT dharavahi FROM thevani 
        WHERE sthiti = 0 
        AND created_at >= DATE_SUB(NOW(), INTERVAL 15 MINUTE)
        LIMIT 10"; // Limit to 10 orders per run

$result = mysqli_query($conn, $sql);

if (!$result) {
    die(json_encode([
        'success' => false, 
        'error' => 'SQL Error: ' . mysqli_error($conn)
    ]));
}

if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        $pending_orders[] = $row['dharavahi'];
    }
}

// =============================================
// STEP 4: Generate Starpago signature function
// =============================================
function generateStarpagoSignature($params, $secretKey) {
    ksort($params);
    $signString = '';
    foreach ($params as $key => $value) {
        $signString .= $key . '=' . $value . '&';
    }
    $signString = rtrim($signString, '&');
    $stringToHash = $signString . '&key=' . $secretKey;
    return hash('sha256', $stringToHash);
}

// =============================================
// STEP 5: Process each pending order with Starpago API
// =============================================
$processed_count = 0;
$paid_orders = [];
$failed_orders = [];
$pending_count = 0;

foreach ($pending_orders as $merOrderNo) {
    // Prepare parameters for Starpago API
    $params = [
        'appId' => $GLOBALS['appId'],
        'merOrderNo' => $merOrderNo
    ];
    
    // Generate signature
    $sign = generateStarpagoSignature($params, $GLOBALS['secretKey']);
    
    // Add sign to params
    $params['sign'] = $sign;
    
    // Build URL with query string
    $fullUrl = $GLOBALS['host'] . $GLOBALS['apiPath'] . '?' . http_build_query($params);
    
    // Call Starpago API
    $ch = curl_init();
    curl_setopt_array($ch, [
        CURLOPT_URL => $fullUrl,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_HEADER => false,
        CURLOPT_SSL_VERIFYPEER => false,
        CURLOPT_SSL_VERIFYHOST => false,
        CURLOPT_TIMEOUT => 30
    ]);
    
    $response = curl_exec($ch);
    $curl_error = curl_error($ch);
    $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);
    
    if ($curl_error) {
        $failed_orders[] = [
            'order_sn' => $merOrderNo,
            'error' => 'CURL Error: ' . $curl_error
        ];
        continue;
    }
    
    // Check HTTP status code
    if ($httpCode == 400) {
        // Order doesn't exist
        $update_sql = "UPDATE thevani SET sthiti = 3 WHERE dharavahi = '" . mysqli_real_escape_string($conn, $merOrderNo) . "'";
        mysqli_query($conn, $update_sql);
        
        $failed_orders[] = [
            'order_sn' => $merOrderNo,
            'status' => 'not_found',
            'message' => "Order doesn't exist in Starpago"
        ];
        continue;
    }
    
    if ($httpCode != 200) {
        $failed_orders[] = [
            'order_sn' => $merOrderNo,
            'error' => 'HTTP Error: ' . $httpCode
        ];
        continue;
    }
    
    // Decode response
    $result = @json_decode($response, true);
    
    if (!$result) {
        $failed_orders[] = [
            'order_sn' => $merOrderNo,
            'error' => 'Invalid JSON response'
        ];
        continue;
    }
    
    // Check Starpago response format
    if (isset($result['code']) && $result['code'] === 0 && isset($result['data'])) {
        $orderData = $result['data'];
        $orderStatus = $orderData['orderStatus'] ?? null;
        
        // orderStatus: 1 = Pending, 2 = Paid/Success, 3 = Failed
        if ($orderStatus == 2) {
            // Payment successful
            // Get order details from database
            $order_sql = "SELECT balakedara, motta, dharavahi FROM thevani 
                         WHERE dharavahi = '" . mysqli_real_escape_string($conn, $merOrderNo) . "'";
            $order_result = mysqli_query($conn, $order_sql);
            
            if (mysqli_num_rows($order_result) > 0) {
                $order = mysqli_fetch_assoc($order_result);
                
                // Create completed table if not exists
                $check_table = "SHOW TABLES LIKE 'thevani_completed'";
                $table_result = mysqli_query($conn, $check_table);
                
                if (mysqli_num_rows($table_result) == 0) {
                    $create_table = "CREATE TABLE IF NOT EXISTS `thevani_completed` (
                        `id` int(11) NOT NULL AUTO_INCREMENT,
                        `balakedara` int(11) NOT NULL,
                        `motta` varchar(200) NOT NULL,
                        `dharavahi` varchar(200) NOT NULL,
                        `payment_amount` decimal(10,2) NOT NULL,
                        `payment_status` varchar(50) NOT NULL,
                        `payment_time` datetime DEFAULT NULL,
                        `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                        PRIMARY KEY (`id`)
                    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci";
                    
                    mysqli_query($conn, $create_table);
                }
                
                // Insert into completed table
                $payment_amount = $orderData['amount'] ?? 0;
                
                $insert_sql = "INSERT INTO thevani_completed 
                              (balakedara, motta, dharavahi, payment_amount, payment_status, payment_time) 
                              VALUES (
                                '" . $order['balakedara'] . "', 
                                '" . mysqli_real_escape_string($conn, $order['motta']) . "', 
                                '" . mysqli_real_escape_string($conn, $order['dharavahi']) . "', 
                                '" . $payment_amount . "', 
                                'paid', 
                                NOW()
                              )";
                
                if (mysqli_query($conn, $insert_sql)) {
                    // Update original order status to 1 (paid/successful)
                    $update_sql = "UPDATE thevani SET sthiti = 1 WHERE dharavahi = '" . mysqli_real_escape_string($conn, $merOrderNo) . "'";
                    mysqli_query($conn, $update_sql);
                    
                    $paid_orders[] = [
                        'order_sn' => $merOrderNo,
                        'amount' => $payment_amount,
                        'status' => 'paid',
                        'message' => 'Payment successful'
                    ];
                    $processed_count++;
                }
            }
        } 
        elseif ($orderStatus == 1) {
            // Still pending
            $pending_count++;
            continue;
        } 
        elseif ($orderStatus == 3) {
            // Payment failed
            $update_sql = "UPDATE thevani SET sthiti = 3 WHERE dharavahi = '" . mysqli_real_escape_string($conn, $merOrderNo) . "'";
            mysqli_query($conn, $update_sql);
            
            $failed_orders[] = [
                'order_sn' => $merOrderNo,
                'status' => 'failed',
                'message' => 'Payment failed'
            ];
        }
        else {
            // Other status
            $failed_orders[] = [
                'order_sn' => $merOrderNo,
                'status' => $orderStatus,
                'message' => 'Unknown status'
            ];
        }
    } else {
        // API returned error
        $error_msg = $result['msg'] ?? 'Unknown API error';
        $failed_orders[] = [
            'order_sn' => $merOrderNo,
            'error' => 'API Error: ' . $error_msg
        ];
    }
}

// =============================================
// STEP 6: Prepare final response
// =============================================
$final_response = [
    'success' => true,
    'status_summary' => [
        'expired_orders' => $expired_count, // Orders marked as expired (sthiti = 2)
        'active_pending' => count($pending_orders), // Orders still pending (<15 min)
        'still_pending_api' => $pending_count, // Orders still pending in Starpago
        'paid_successful' => $processed_count, // Orders paid (sthiti = 1)
        'failed_orders' => count($failed_orders) // Orders failed (sthiti = 3)
    ],
    'expired_orders_count' => $expired_count,
    'paid_orders' => $paid_orders,
    'failed_orders' => $failed_orders,
    'timestamp' => date('Y-m-d H:i:s')
];

// Output JSON response
echo json_encode($final_response, JSON_PRETTY_PRINT);

// Close database connection
mysqli_close($conn);
?>