<?php

$response = array();

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

try {

    // ── Step 1: Fetch all currency short names 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 from tblcurrency";

        echo json_encode($response);
        exit;
    }

    $currencies = [];

    while ($row = pg_fetch_assoc($currency_result)) {
        $currencies[] = trim($row["currency_short_name"]);
    }

    if (empty($currencies)) {

        $response["success"] = 0;
        $response["message"] = "No currencies found in tblcurrency";

        echo json_encode($response);
        exit;
    }

    $saved_count  = 0;
    $failed       = [];
    $api_key      = "fc85b72357af5313eca251a1";

    // ── Step 2: Loop each currency — call API with that currency as base ──
    // API: latest/{currency_short_name} → response contains INR rate
    // e.g. latest/USD → conversion_rates["INR"] = how many INR per 1 USD

    foreach ($currencies as $currency_short_name) {

        // Build URL: base currency = currency_short_name
        $api_url = "https://v6.exchangerate-api.com/v6/{$api_key}/latest/{$currency_short_name}";

        $api_response = file_get_contents($api_url);

        if ($api_response === FALSE) {
            $failed[] = [
                "currency" => $currency_short_name,
                "reason"   => "API request failed"
            ];
            continue;
        }

        $api_data = json_decode($api_response, true);

        // Validate response
        if (
            !isset($api_data["result"]) ||
            $api_data["result"] !== "success" ||
            !isset($api_data["conversion_rates"]["INR"])
        ) {
            $failed[] = [
                "currency" => $currency_short_name,
                "reason"   => isset($api_data["error-type"])
                                ? $api_data["error-type"]
                                : "Invalid API response or INR rate not found"
            ];
            continue;
        }

        // ── INR value for 1 unit of this currency ──
        $exchange_rate = $api_data["conversion_rates"]["INR"];

        // Convert API update time (UTC) → Indian timezone date
        $utc_time = $api_data["time_last_update_utc"];
        $date     = new DateTime($utc_time, new DateTimeZone('UTC'));
        $date->setTimezone(new DateTimeZone('Asia/Kolkata'));
        $exchange_date = $date->format('Y-m-d');

        // Escape values for PostgreSQL
        $currency_short_name_esc = pg_escape_string($con, $currency_short_name);
        $exchange_rate_esc       = pg_escape_string($con, $exchange_rate);
        $exchange_date_esc       = pg_escape_string($con, $exchange_date);

        // ── Step 3: Upsert into tblexchangerate ──
        $check_query = "
            SELECT exchange_id
            FROM tblexchangerate
            WHERE currency_short_name = '{$currency_short_name_esc}'
            AND DATE(exchange_date)   = '{$exchange_date_esc}'
        ";

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

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

            // Record exists for today — update it
            $upsert_query = "
                UPDATE tblexchangerate
                SET
                    exchange_rate = '{$exchange_rate_esc}',
                    updateddate   = NOW(),
                    exchange_date = '{$exchange_date_esc}'
                WHERE currency_short_name = '{$currency_short_name_esc}'
                AND DATE(exchange_date)   = '{$exchange_date_esc}'
            ";

        } else {

            // No record for today — insert
            $upsert_query = "
                INSERT INTO tblexchangerate
                (
                    currency_short_name,
                    exchange_rate,
                    createddate,
                    updateddate,
                    exchange_date
                )
                VALUES
                (
                    '{$currency_short_name_esc}',
                    '{$exchange_rate_esc}',
                    NOW(),
                    NOW(),
                    '{$exchange_date_esc}'
                )
            ";
        }

        $upsert_result = pg_query($con, $upsert_query);

        if ($upsert_result) {
            $saved_count++;
        } else {
            $failed[] = [
                "currency" => $currency_short_name,
                "reason"   => "DB upsert failed: " . pg_last_error($con)
            ];
        }
    }

    // ── Step 4: Return result ──
    $response["success"]      = 1;
    $response["message"]      = "Exchange rate sync completed";
    $response["total_saved"]  = $saved_count;
    $response["total_failed"] = count($failed);

    if (!empty($failed)) {
        $response["failed_currencies"] = $failed;
    }

    echo json_encode($response, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);

    mch_db_release($con);

} catch (Exception $e) {

    mch_db_release($con);

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

    echo json_encode($response);
}
?>