<?php

$response = array();

require_once __DIR__ . '/catchexception.php';
require_once __DIR__ . '/db_config.php';

try {

    // Exchange Rate API URL
    $api_url = "https://v6.exchangerate-api.com/v6/fc85b72357af5313eca251a1/latest/INR";

    // Get API Response
    $api_response = file_get_contents($api_url);

    if ($api_response === FALSE) {

        $response["success"] = 0;
        $response["message"] = "Unable to fetch exchange rate API";

        echo json_encode($response);
        exit;
    }

    $api_data = json_decode($api_response, true);

    if (
        isset($api_data["result"]) &&
        $api_data["result"] == "success" &&
        isset($api_data["conversion_rates"])
    ) {

        $conversion_rates = $api_data["conversion_rates"];

        // Convert UTC Time to Indian Time Zone
        $utc_time = $api_data["time_last_update_utc"];

        $date = new DateTime($utc_time, new DateTimeZone('UTC'));
        $date->setTimezone(new DateTimeZone('Asia/Kolkata'));

        // Save only date in Indian timezone
        $exchange_date = $date->format('Y-m-d');

        // Get valid currencies from tblcurrency
        $currency_query = "
            SELECT currency_short_name
            FROM tblcurrency
        ";

        $currency_result = pg_query($con, $currency_query);

        if (!$currency_result) {

            $response["success"] = 0;
            $response["message"] = "Failed to fetch currency list";

            echo json_encode($response);
            exit;
        }

        $saved_count = 0;

        while ($currency_row = pg_fetch_assoc($currency_result)) {

            $currency_short_name = trim($currency_row["currency_short_name"]);

            // Check currency exists in API response
            if (isset($conversion_rates[$currency_short_name])) {

                $exchange_rate = $conversion_rates[$currency_short_name];

                // Check already exists for same date
                $check_query = "
                    SELECT exchange_id
                    FROM tblexchangerate
                    WHERE currency_short_name = '$currency_short_name'
                    AND DATE(exchange_date) = '$exchange_date'
                ";

                $check_result = pg_query($con, $check_query);

                if (
                    $check_result &&
                    pg_num_rows($check_result) > 0
                ) {

                    // Update existing record
                    $update_query = "
                        UPDATE tblexchangerate
                        SET
                            exchange_rate = '$exchange_rate',
                            updateddate = NOW(),
                            exchange_date = '$exchange_date'
                        WHERE currency_short_name = '$currency_short_name'
                        AND DATE(exchange_date) = '$exchange_date'
                    ";

                    pg_query($con, $update_query);

                } else {

                    // Insert new record
                    $insert_query = "
                        INSERT INTO tblexchangerate
                        (
                            currency_short_name,
                            exchange_rate,
                            createddate,
                            updateddate,
                            exchange_date
                        )
                        VALUES
                        (
                            '$currency_short_name',
                            '$exchange_rate',
                            NOW(),
                            NOW(),
                            '$exchange_date'
                        )
                    ";

                    pg_query($con, $insert_query);
                }

                $saved_count++;
            }
        }

        $response["success"] = 1;
        $response["message"] = "Exchange rate synced successfully";
        $response["exchange_date"] = $exchange_date;
        $response["total_saved"] = $saved_count;

        echo json_encode($response, JSON_UNESCAPED_UNICODE);

    } else {

        $response["success"] = 0;
        $response["message"] = "Invalid API response";

        echo json_encode($response);
    }

    mch_db_release($con);

} catch (Exception $e) {

    mch_db_release($con);

    $response["success"] = 0;
    $response["message"] = $e->getMessage();

    echo json_encode($response);
}
?>