.mode list
.separator ""
.output index.html

WITH raw AS (
  SELECT
    value AS country
  FROM json_each(readfile('countries.json'))
),
rows AS (
  SELECT
    printf(
      '<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>',
      json_extract(country, '$.translations.deu.common'),
      json_extract(country, '$.population'),
      json_extract(country, '$.capital[0]'),
      json_extract(country, '$.tld[0]'),
      json_extract(country, '$.timezones')
    ) AS row_html
  FROM raw
  ORDER BY json_extract(country, '$.translations.deu.common')
)
SELECT
  '<!DOCTYPE html>
<html lang="de">
<head>
  <meta charset="UTF-8">
  <title>Countries</title>
  <style>
    body { font-family: sans-serif; padding: 2rem; }
    table { border-collapse: collapse; width: 100%; }
    th, td { border: 1px solid #ccc; padding: 0.5rem; text-align: left; }
    th { background: #f2f2f2; }
    tr:nth-child(even) { background: #fafafa; }
  </style>
</head>
<body>
  <h1>Countries</h1>
  <table>
    <thead>
      <tr><th>Name (DE)</th><th>Population</th><th>Capital</th><th>TLD</th><th>Timezones</th></tr>
    </thead>
    <tbody>'
UNION ALL
SELECT row_html FROM rows
UNION ALL
SELECT
'    </tbody>
  </table>
</body>
</html>';
.output stdout
