<?php
require_once __DIR__ . '/../../includes/bootstrap.php';
require_login();

header('Content-Type: text/plain; charset=utf-8');

/*
   Daily exchange-rate sync.
   Direction lock: tblcurrency.ROE = Currency per 1 USD.

   Supported response formats:
   1) exchangerate.host / APILayer style:
      {
        "success": true,
        "source": "USD",
        "quotes": { "USDZAR": 18.50, "USDEUR": 0.92 }
      }

   2) ExchangeRate-API style:
      {
        "result": "success",
        "base_code": "USD",
        "conversion_rates": { "ZAR": 18.50, "EUR": 0.92 }
      }

   Suggested cron:
   15 6 * * * /usr/local/bin/php /home/macsoftl/public_html/rooms/modules/payments/currency_rates_sync.php

   SECURITY:
   Prefer getenv('EXCHANGE_RATE_API_KEY') instead of hard-coding the key.
*/

$apiKey = getenv('EXCHANGE_RATE_API_KEY') ?: 'a389529132591409abb9aa2a';
$base = 'USD';

/*
   Choose provider:
   - exchange_rate_api uses https://v6.exchangerate-api.com/v6/{KEY}/latest/USD
   - exchangerate_host uses https://api.exchangerate.host/live?access_key={KEY}&source=USD&currencies=...
*/
$provider = getenv('EXCHANGE_RATE_PROVIDER') ?: 'exchange_rate_api';

if ($apiKey === '') {
    echo "No EXCHANGE_RATE_API_KEY configured. Rates not changed.\n";
    echo "ROE remains locked as Currency per 1 USD.\n";
    exit;
}

try {
    /*
       Ensure optional audit columns exist.
       If your MySQL version does not support IF NOT EXISTS for ADD COLUMN,
       errors are safely ignored.
    */
    try {
        $pdo->exec("ALTER TABLE tblcurrency ADD COLUMN IF NOT EXISTS roe_direction VARCHAR(30) NOT NULL DEFAULT 'CURRENCY_PER_USD'");
    } catch (Throwable $e) {}
    try {
        $pdo->exec("ALTER TABLE tblcurrency ADD COLUMN IF NOT EXISTS roe_updated_at DATETIME NULL");
    } catch (Throwable $e) {}
    try {
        $pdo->exec("ALTER TABLE tblcurrency ADD COLUMN IF NOT EXISTS roe_source VARCHAR(80) NULL");
    } catch (Throwable $e) {}

    $stmt = $pdo->query("
        SELECT CurrencyID, CurrencyCode, Currency, ROE
        FROM tblcurrency
        WHERE status = 1
        ORDER BY CurrencyCode ASC
    ");
    $currencies = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if (!$currencies) {
        echo "No active currencies found.\n";
        exit;
    }

    $codes = [];
    foreach ($currencies as $row) {
        $code = strtoupper(trim((string)($row['CurrencyCode'] ?? '')));
        if ($code !== '' && $code !== $base) {
            $codes[] = $code;
        }
    }

    $codes = array_values(array_unique($codes));

    if (!$codes) {
        echo "Only USD/base currency found. Nothing to sync.\n";
        exit;
    }

    if ($provider === 'exchangerate_host') {
        $url = 'https://api.exchangerate.host/live?access_key=' . urlencode($apiKey)
             . '&source=' . urlencode($base)
             . '&currencies=' . urlencode(implode(',', $codes));
    } else {
        /*
           This is usually the correct endpoint for keys from exchangerate-api.com.
           It returns conversion_rates.
        */
        $url = 'https://v6.exchangerate-api.com/v6/' . rawurlencode($apiKey) . '/latest/' . rawurlencode($base);
        $provider = 'exchange_rate_api';
    }

    $context = stream_context_create([
        'http' => [
            'timeout' => 30,
            'ignore_errors' => true,
            'header' => "Accept: application/json\r\nUser-Agent: RoomZ-PMS-CurrencySync/1.0\r\n",
        ],
    ]);

    $json = @file_get_contents($url, false, $context);
    if ($json === false || trim($json) === '') {
        throw new RuntimeException('Could not fetch rates from provider.');
    }

    $data = json_decode($json, true);
    if (!is_array($data)) {
        throw new RuntimeException('Invalid provider JSON. Raw response: ' . substr($json, 0, 500));
    }

    /*
       Normalize rates into:
       $rates['ZAR'] = 18.50
       meaning 1 USD = 18.50 ZAR
    */
    $rates = [];

    if (!empty($data['quotes']) && is_array($data['quotes'])) {
        foreach ($data['quotes'] as $pair => $rate) {
            $pair = strtoupper((string)$pair);
            if (strpos($pair, $base) === 0) {
                $code = substr($pair, strlen($base));
                $rates[$code] = (float)$rate;
            }
        }
    }

    if (!$rates && !empty($data['conversion_rates']) && is_array($data['conversion_rates'])) {
        foreach ($data['conversion_rates'] as $code => $rate) {
            $rates[strtoupper((string)$code)] = (float)$rate;
        }
    }

    if (!$rates && !empty($data['rates']) && is_array($data['rates'])) {
        foreach ($data['rates'] as $code => $rate) {
            $rates[strtoupper((string)$code)] = (float)$rate;
        }
    }

    if (!$rates) {
        $providerMessage = '';
        if (!empty($data['error-type'])) {
            $providerMessage = ' Provider error: ' . $data['error-type'];
        } elseif (!empty($data['error']['info'])) {
            $providerMessage = ' Provider error: ' . $data['error']['info'];
        } elseif (!empty($data['message'])) {
            $providerMessage = ' Provider message: ' . $data['message'];
        }

        throw new RuntimeException(
            'No rates found in provider response. Expected quotes, conversion_rates, or rates.' .
            $providerMessage .
            "\nRaw response preview: " . substr($json, 0, 800)
        );
    }

    /*
       Use UPDATE that works even if optional columns were not created.
    */
    $hasAuditColumns = false;
    try {
        $chk = $pdo->prepare("
            SELECT COUNT(*)
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'tblcurrency'
              AND COLUMN_NAME IN ('roe_direction','roe_source','roe_updated_at')
        ");
        $chk->execute();
        $hasAuditColumns = ((int)$chk->fetchColumn() >= 3);
    } catch (Throwable $e) {
        $hasAuditColumns = false;
    }

    if ($hasAuditColumns) {
        $update = $pdo->prepare("
            UPDATE tblcurrency
            SET ROE = :roe,
                roe_direction = 'CURRENCY_PER_USD',
                roe_source = :source,
                roe_updated_at = NOW()
            WHERE CurrencyID = :id
        ");
    } else {
        $update = $pdo->prepare("
            UPDATE tblcurrency
            SET ROE = :roe
            WHERE CurrencyID = :id
        ");
    }

    $updated = 0;

    foreach ($currencies as $row) {
        $id = (int)$row['CurrencyID'];
        $code = strtoupper(trim((string)($row['CurrencyCode'] ?? '')));

        if ($code === '') {
            continue;
        }

        if ($code === $base) {
            $rate = 1.00000;
        } else {
            if (!isset($rates[$code])) {
                echo "Skipped {$code}: provider did not return this currency.\n";
                continue;
            }
            $rate = (float)$rates[$code];
        }

        if ($rate <= 0) {
            echo "Skipped {$code}: invalid rate {$rate}.\n";
            continue;
        }

        $params = [
            ':roe' => round($rate, 5),
            ':id'  => $id,
        ];

        if ($hasAuditColumns) {
            $params[':source'] = $provider;
        }

        $update->execute($params);

        echo "Updated {$code}: 1 USD = " . number_format($rate, 5, '.', '') . " {$code}\n";
        $updated++;
    }

    echo "Done. Updated {$updated} currency rate(s).\n";
    echo "Direction locked: ROE = Currency per 1 USD.\n";
} catch (Throwable $e) {
    http_response_code(500);
    echo "Rate sync failed: " . $e->getMessage() . "\n";
}
