-- Clear the table at the beginning of the script
DELETE FROM users_parsed;

-- Create table for parsed data
CREATE TABLE IF NOT EXISTS users_parsed (
    id INTEGER PRIMARY KEY,
    name TEXT,
    username TEXT,
    email TEXT,
    company TEXT
);

-- Fetch and parse JSON data
WITH raw_data AS (
    SELECT sqlpage.fetch('https://jsonplaceholder.typicode.com/users/') AS json
),
parsed AS (
    SELECT 
        json_extract(value, '$.id') AS id,
        json_extract(value, '$.name') AS name,
        json_extract(value, '$.username') AS username,
        json_extract(value, '$.email') AS email,
        json_extract(value, '$.company.name') AS company
    FROM raw_data, json_each(raw_data.json)
)
INSERT INTO users_parsed (id, name, username, email, company)
SELECT id, name, username, email, company FROM parsed;

SELECT 
    'text' as component, 
    '**Parsed from jsonplaceholder.typicode.com/users/**' as contents_md;

-- Display parsed data
SELECT 
    'table'             AS component, 
    'Parsed User Data'  AS title,
    TRUE                AS sort, 
    TRUE                AS search;
SELECT 
    name AS 'Name', 
    username AS 'Username', 
    email AS 'Email', 
    company AS 'Company' 
FROM users_parsed;