<?php
/* PHP 5.6 — Listing + Edition AJAX + Conversion stable en EUR
 * Dépendances DB : tables price_by_country et exchange_rates (voir §2)
 * --------------------------------------------------------------
 * - Recherche par country_name / country_code
 * - Pagination (50/pg)
 * - Colonnes EUR lues via exchange_rates (base EUR)
 */

if (session_id() === '') session_start();

/* ---------- CONFIG DB ---------- */
$db_host = '127.0.0.1';
$db_name = 'YOUR_DB';
$db_user = 'YOUR_USER';
$db_pass = 'YOUR_PASS';

try {
    $pdo = new PDO(
        'mysql:host='.$db_host.';dbname='.$db_name.';charset=utf8',
        $db_user,
        $db_pass,
        array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC)
    );
} catch (Exception $e) {
    http_response_code(500);
    echo "DB connection error.";
    exit;
}

/* ---------- AJAX UPDATE ---------- */
if (isset($_GET['action']) && $_GET['action'] === 'update' && $_SERVER['REQUEST_METHOD'] === 'POST') {
    header('Content-Type: application/json; charset=utf-8');

    // Champs autorisés (whitelist)
    $allowed = array(
        'country_name', 'country_code', 'price_month', 'price_year', 'checked',
        'remise_pourcentage', 'remise_pourcentage_date_debut', 'remise_pourcentage_date_fin',
        'price_month_local', 'price_year_local', 'disponible_sur_stripe', 'currency',
        'price_month_local_checked', 'devise_monetaire_symbole', 'zero_decimal'
    );

    $id = isset($_POST['id']) ? (int)$_POST['id'] : 0;
    if ($id <= 0) {
        echo json_encode(array('ok'=>false,'error'=>'Invalid id'));
        exit;
    }

    // Construction dynamique du UPDATE
    $sets = array(); $vals = array();
    foreach ($allowed as $k) {
        if (array_key_exists($k, $_POST)) {
            $val = $_POST[$k];

            // Normalisations simples
            if (in_array($k, array('checked','disponible_sur_stripe','price_month_local_checked','zero_decimal'), true)) {
                $val = ($val === '1' || $val === 1 || $val === 'true' || $val === 'on') ? 1 : 0;
            }

            // Dates : accepter '' pour NULL
            if (in_array($k, array('remise_pourcentage_date_debut','remise_pourcentage_date_fin'), true)) {
                $val = trim($val);
                if ($val === '') $val = null;
            }

            // Numériques
            if (in_array($k, array('price_month','price_year','price_month_local','price_year_local','remise_pourcentage'), true)) {
                $val = ($val === '' ? null : str_replace(',', '.', $val));
                if (!is_null($val) && !is_numeric($val)) {
                    echo json_encode(array('ok'=>false,'error'=>"Invalid numeric for $k"));
                    exit;
                }
            }

            $sets[] = "`$k` = :$k";
            $vals[":$k"] = $val;
        }
    }

    if (empty($sets)) {
        echo json_encode(array('ok'=>false,'error'=>'No updatable fields received'));
        exit;
    }

    $vals[':id'] = $id;

    try {
        $sql = "UPDATE price_by_country SET ".implode(', ', $sets)." WHERE id = :id LIMIT 1";
        $st  = $pdo->prepare($sql);
        $st->execute($vals);
        echo json_encode(array('ok'=>true));
    } catch (Exception $e) {
        echo json_encode(array('ok'=>false,'error'=>'DB error: '.$e->getMessage()));
    }
    exit;
}

/* ---------- LISTING (GET) ---------- */
$perPage = 50;
$page    = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
$offset  = ($page - 1) * $perPage;

$q = isset($_GET['q']) ? trim($_GET['q']) : '';

$where = " WHERE 1=1 ";
$params = array();
if ($q !== '') {
    $where .= " AND (pc.country_name LIKE :q OR pc.country_code LIKE :q) ";
    $params[':q'] = '%'.$q.'%';
}

/* 
   exchange_rates: base EUR
   Colonne rate_per_eur = combien d’unités de la devise pour 1 EUR (ex: USD ~ 1.07, JPY ~ 160).
   Conversion vers EUR :
      EUR = local / rate_per_eur   (si currency != 'EUR')
*/
$countSql = "SELECT COUNT(*) FROM price_by_country pc ".$where;
$stc = $pdo->prepare($countSql);
$stc->execute($params);
$totalRows = (int)$stc->fetchColumn();

$sql = "
SELECT
  pc.*,
  er.rate_per_eur,
  CASE
    WHEN pc.currency = 'EUR' THEN pc.price_month_local
    WHEN er.rate_per_eur IS NULL OR er.rate_per_eur = 0 THEN NULL
    ELSE pc.price_month_local / er.rate_per_eur
  END AS price_month_eur,
  CASE
    WHEN pc.currency = 'EUR' THEN pc.price_year_local
    WHEN er.rate_per_eur IS NULL OR er.rate_per_eur = 0 THEN NULL
    ELSE pc.price_year_local / er.rate_per_eur
  END AS price_year_eur,
  er.last_updated AS fx_last_updated
FROM price_by_country pc
LEFT JOIN exchange_rates er ON er.currency_code = pc.currency
".$where."
ORDER BY pc.country_name ASC, pc.id ASC
LIMIT :limit OFFSET :offset
";
$st = $pdo->prepare($sql);
foreach ($params as $k=>$v) $st->bindValue($k, $v, PDO::PARAM_STR);
$st->bindValue(':limit',  $perPage, PDO::PARAM_INT);
$st->bindValue(':offset', $offset,  PDO::PARAM_INT);
$st->execute();
$rows = $st->fetchAll();

$pages = max(1, (int)ceil($totalRows / $perPage));
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Price by Country — Listing</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
:root{
  --bg:#0b1220;
  --card:#151a2b;
  --muted:#8892a6;
  --text:#e6e8ef;
  --accent:#5dab00;
  --primary:#03056b;
  --danger:#c62828;
  --ring:rgba(93,171,0,.25);
}
*{box-sizing:border-box}
body{margin:0;background:var(--bg);color:var(--text);font-family:system-ui,-apple-system,Segoe UI,Roboto,Ubuntu,Arial}
.container{max-width:1280px;margin:24px auto;padding:0 16px}
h1{font-size:22px;margin:0 0 12px}
.toolbar{display:flex;gap:8px;align-items:center;margin:0 0 16px;flex-wrap:wrap}
.input{background:var(--card);border:1px solid #293048;color:var(--text);padding:10px 12px;border-radius:10px;outline:none}
.input:focus{border-color:var(--accent);box-shadow:0 0 0 4px var(--ring)}
.btn{border:0;border-radius:10px;padding:10px 14px;cursor:pointer}
.btn-ghost{background:var(--card);color:var(--text);border:1px solid #293048}
.btn-accent{background:var(--accent);color:#fff}
.badge{font-size:12px;padding:2px 8px;border-radius:999px;background:#2a3046;color:#cbd2e1}
.tablewrap{overflow:auto;border:1px solid #293048;border-radius:12px}
table{min-width:1600px;border-collapse:separate;border-spacing:0;width:100%}
th,td{padding:10px 8px;border-bottom:1px solid #293048;vertical-align:middle}
th{position:sticky;top:0;background:#0f1628;z-index:1;font-weight:600;text-align:left}
tr:hover td{background:#111a30}
input.cell, select.cell{width:160px;max-width:100%;background:#10162a;border:1px solid #2b3350;color:var(--text);padding:8px;border-radius:8px}
input.cell:focus, select.cell:focus{border-color:var(--accent);box-shadow:0 0 0 3px var(--ring);outline:none}
.cell-sm{width:90px}
.cell-lg{width:220px}
.row-actions{display:flex;gap:8px}
.state-warn{color:#ffd166}
tfoot td{padding:14px}
.pagination{display:flex;gap:6px;align-items:center;justify-content:flex-end}
.pagination a, .pagination span{padding:8px 10px;border-radius:8px;background:var(--card);border:1px solid #293048;color:var(--text);text-decoration:none}
.pagination .active{background:var(--accent);border-color:var(--accent);color:#fff}
.fx-stale{color:#ffadad}
.modal-backdrop{position:fixed;inset:0;background:rgba(0,0,0,.5);display:none;align-items:center;justify-content:center}
.modal{background:#0f1628;border:1px solid #293048;border-radius:12px;max-width:520px;width:92%;padding:18px}
.modal header{font-weight:700;margin-bottom:8px}
.modal footer{display:flex;justify-content:flex-end;margin-top:12px;gap:8px}
</style>
</head>
<body>
<div class="container">
  <h1>Price by Country <span class="badge"><?= (int)$totalRows ?> rows</span></h1>

  <form method="get" class="toolbar" id="searchForm">
    <input type="text" class="input" name="q" placeholder="Search by country name or code…" value="<?= htmlspecialchars($q, ENT_QUOTES, 'UTF-8') ?>">
    <button class="btn btn-accent" type="submit">Search</button>
    <?php if ($q !== ''): ?>
      <a class="btn btn-ghost" href="?">Clear</a>
    <?php endif; ?>
  </form>

  <div class="tablewrap">
    <table>
      <thead>
        <tr>
          <th>ID</th>
          <th>Country Name</th>
          <th>Code</th>
          <th>Currency</th>
          <th>Symbol</th>
          <th>Checked</th>
          <th>Zero-decimal</th>
          <th>Stripe enabled</th>
          <th>Price Month (USD/EUR/… local)</th>
          <th>Price Month (EUR)</th>
          <th>Price Year (local)</th>
          <th>Price Year (EUR)</th>
          <th>Price Month (global EUR)</th>
          <th>Price Year (global EUR)</th>
          <th>Local checked</th>
          <th>Remise %</th>
          <th>Remise début</th>
          <th>Remise fin</th>
          <th>Actions</th>
          <th class="state-warn">FX last update</th>
        </tr>
      </thead>
      <tbody>
      <?php foreach ($rows as $r): ?>
        <?php
          // Sauvegarde de fallback affichage
          $price_month_eur = is_null($r['price_month_eur']) ? '' : number_format((float)$r['price_month_eur'], 2, '.', '');
          $price_year_eur  = is_null($r['price_year_eur'])  ? '' : number_format((float)$r['price_year_eur'],  2, '.', '');
          // Colonnes “global” EUR au cas où vous souhaitez aussi conserver price_month / price_year en EUR (si vos champs price_month / price_year sont déjà EUR, on les affiche tels quels)
          $price_month_global_eur = is_null($r['price_month']) ? '' : number_format((float)$r['price_month'], 2, '.', '');
          $price_year_global_eur  = is_null($r['price_year'])  ? '' : number_format((float)$r['price_year'],  2, '.', '');
          $fx_stale = empty($r['fx_last_updated']) ? 'fx-stale' : '';
        ?>
        <tr data-id="<?= (int)$r['id'] ?>">
          <td><?= (int)$r['id'] ?></td>
          <td><input class="cell cell-lg" name="country_name" value="<?= htmlspecialchars((string)$r['country_name'], ENT_QUOTES, 'UTF-8') ?>"></td>
          <td><input class="cell cell-sm" name="country_code" value="<?= htmlspecialchars((string)$r['country_code'], ENT_QUOTES, 'UTF-8') ?>"></td>
          <td>
            <input class="cell cell-sm" name="currency" value="<?= htmlspecialchars((string)$r['currency'], ENT_QUOTES, 'UTF-8') ?>">
          </td>
          <td><input class="cell cell-sm" name="devise_monetaire_symbole" value="<?= htmlspecialchars((string)$r['devise_monetaire_symbole'], ENT_QUOTES, 'UTF-8') ?>"></td>
          <td>
            <select class="cell cell-sm" name="checked">
              <option value="0"<?= $r['checked'] ? '' : ' selected'?>>0</option>
              <option value="1"<?= $r['checked'] ? ' selected' : ''?>>1</option>
            </select>
          </td>
          <td>
            <select class="cell cell-sm" name="zero_decimal">
              <option value="0"<?= $r['zero_decimal'] ? '' : ' selected'?>>0</option>
              <option value="1"<?= $r['zero_decimal'] ? ' selected' : ''?>>1</option>
            </select>
          </td>
          <td>
            <select class="cell cell-sm" name="disponible_sur_stripe">
              <option value="0"<?= $r['disponible_sur_stripe'] ? '' : ' selected'?>>0</option>
              <option value="1"<?= $r['disponible_sur_stripe'] ? ' selected' : ''?>>1</option>
            </select>
          </td>
          <td><input class="cell" name="price_month_local" value="<?= htmlspecialchars((string)$r['price_month_local'], ENT_QUOTES, 'UTF-8') ?>"></td>
          <td><input class="cell" value="<?= htmlspecialchars($price_month_eur, ENT_QUOTES, 'UTF-8') ?>" disabled></td>
          <td><input class="cell" name="price_year_local" value="<?= htmlspecialchars((string)$r['price_year_local'], ENT_QUOTES, 'UTF-8') ?>"></td>
          <td><input class="cell" value="<?= htmlspecialchars($price_year_eur, ENT_QUOTES, 'UTF-8') ?>" disabled></td>
          <td><input class="cell" name="price_month" value="<?= htmlspecialchars($price_month_global_eur, ENT_QUOTES, 'UTF-8') ?>"></td>
          <td><input class="cell" name="price_year" value="<?= htmlspecialchars($price_year_global_eur, ENT_QUOTES, 'UTF-8') ?>"></td>
          <td>
            <select class="cell cell-sm" name="price_month_local_checked">
              <option value="0"<?= $r['price_month_local_checked'] ? '' : ' selected'?>>0</option>
              <option value="1"<?= $r['price_month_local_checked'] ? ' selected' : ''?>>1</option>
            </select>
          </td>
          <td><input class="cell cell-sm" name="remise_pourcentage" value="<?= htmlspecialchars((string)$r['remise_pourcentage'], ENT_QUOTES, 'UTF-8') ?>"></td>
          <td><input class="cell" name="remise_pourcentage_date_debut" placeholder="YYYY-MM-DD" value="<?= htmlspecialchars((string)$r['remise_pourcentage_date_debut'], ENT_QUOTES, 'UTF-8') ?>"></td>
          <td><input class="cell" name="remise_pourcentage_date_fin" placeholder="YYYY-MM-DD" value="<?= htmlspecialchars((string)$r['remise_pourcentage_date_fin'], ENT_QUOTES, 'UTF-8') ?>"></td>
          <td class="row-actions">
            <button class="btn btn-accent js-save">Save</button>
            <button class="btn btn-ghost js-reset">Reset</button>
          </td>
          <td class="<?= $fx_stale ?>"><?= $r['fx_last_updated'] ? htmlspecialchars($r['fx_last_updated'], ENT_QUOTES, 'UTF-8') : '—' ?></td>
        </tr>
      <?php endforeach; ?>
      </tbody>
      <tfoot>
        <tr>
          <td colspan="20">
            <div class="pagination">
              <?php if ($page > 1): ?>
                <a href="?<?= $q!==''?'q='.urlencode($q).'&':'' ?>page=1">&laquo;</a>
                <a href="?<?= $q!==''?'q='.urlencode($q).'&':'' ?>page=<?= ($page-1) ?>">&lsaquo;</a>
              <?php else: ?>
                <span>&laquo;</span><span>&lsaquo;</span>
              <?php endif; ?>
              <span class="active"><?= (int)$page ?>/<?= (int)$pages ?></span>
              <?php if ($page < $pages): ?>
                <a href="?<?= $q!==''?'q='.urlencode($q).'&':'' ?>page=<?= ($page+1) ?>">&rsaquo;</a>
                <a href="?<?= $q!==''?'q='.urlencode($q).'&':'' ?>page=<?= (int)$pages ?>">&raquo;</a>
              <?php else: ?>
                <span>&rsaquo;</span><span>&raquo;</span>
              <?php endif; ?>
            </div>
          </td>
        </tr>
      </tfoot>
    </table>
  </div>
</div>

<!-- Modal d'erreur -->
<div class="modal-backdrop" id="modalBackdrop">
  <div class="modal">
    <header>Error while saving</header>
    <div id="modalMessage">An unexpected error occurred.</div>
    <footer>
      <button class="btn btn-accent" id="modalClose">Close</button>
    </footer>
  </div>
</div>

<script>
// jQuery minimal (si déjà chargé ailleurs, supprimer ce bloc IIFE simple)
(function(){
  if (!window.$) {
    var s = document.createElement('script');
    s.src = 'https://code.jquery.com/jquery-3.6.0.min.js';
    s.integrity = 'sha256-/xUj+3OJ+Y6k9G6R8yQZRy6Q8VvCYN3E4z9o9ZC1B0c=';
    s.crossOrigin = 'anonymous';
    document.head.appendChild(s);
  }
})();

(function waitJQ(){
  if (!window.jQuery) return setTimeout(waitJQ, 50);
  var $ = window.jQuery;

  function openModal(msg){
    $('#modalMessage').text(msg || 'An error occurred.');
    $('#modalBackdrop').css('display','flex');
  }
  function closeModal(){ $('#modalBackdrop').hide(); }
  $('#modalClose').on('click', closeModal);
  $('#modalBackdrop').on('click', function(e){ if (e.target === this) closeModal(); });

  // Gestion SAVE / RESET
  $('table').on('click', '.js-save', function(e){
    e.preventDefault();
    var $tr = $(this).closest('tr');
    var id = $tr.data('id');
    if (!id) return;

    var payload = { id: id };
    $tr.find('input[name], select[name]').each(function(){
      var name = $(this).attr('name');
      var val  = $(this).is('select') ? $(this).val() : $(this).val();
      payload[name] = val;
    });

    $.ajax({
      url: '?action=update',
      method: 'POST',
      data: payload,
      dataType: 'json'
    }).done(function(res){
      if (!res || !res.ok) {
        openModal((res && res.error) ? res.error : 'Unknown error.');
      } else {
        // Succès : on peut soit recharger, soit juste surligner la ligne
        $tr.css('outline','2px solid var(--accent)');
        setTimeout(function(){ $tr.css('outline','none'); }, 800);
        // Astuce : recharger pour rafraichir les colonnes EUR recalculées côté SQL
        location.reload();
      }
    }).fail(function(xhr){
      openModal('HTTP error: ' + xhr.status + ' ' + xhr.statusText);
    });
  });

  // Reset = recharger la page pour annuler les edits non sauvegardés de la ligne
  $('table').on('click', '.js-reset', function(e){
    e.preventDefault();
    location.reload();
  });

})();
</script>
</body>
</html>