CREATE TABLE IF NOT EXISTS people (
    id INTEGER PRIMARY KEY,
    name TEXT,
    phone TEXT,
    email TEXT
);

-- Define pagination variables
SET $records_per_page = 100;  -- Number of records per page
SET $page = COALESCE($page, 1);  -- Current page number, defaults to 1 if not provided
SET $offset = ($page - 1) * $records_per_page;  -- Calculate the offset for pagination

-- Insert new record only if all fields are provided and no edit is in progress
INSERT INTO people (name, phone, email)
SELECT :Name, :Phone, :Email  
WHERE :Name IS NOT NULL AND $edit IS NULL AND $add IS NOT NULL;

-- Update the record when editing
UPDATE people 
SET name = :Name, phone = :Phone, email = :Email 
WHERE id = $edit AND :Name IS NOT NULL;

-- Delete the record only if confirmed
DELETE FROM people WHERE id = $delete AND :confirm = 'Yes';

-- Redirect to clear form after insert, update, or deletion confirmation
SELECT 'redirect' AS component, '?' AS link
WHERE 
    ($add IS NOT NULL AND :Name IS NOT NULL)  -- Redirect after adding a new record
    OR ($edit IS NOT NULL AND :Name IS NOT NULL)  -- Redirect after editing a record
    OR ($delete IS NOT NULL AND :confirm IS NOT NULL);  -- Redirect after confirming deletion

-- Conditionally show the form if editing or adding a new entry
SELECT 'form' AS component
WHERE $edit IS NOT NULL OR $add IS NOT NULL;

-- Conditionally show the form for confirmation if a deletion is requested and not yet confirmed
SELECT 'form' AS component WHERE $delete IS NOT NULL AND :confirm IS NULL;
SELECT 'hidden' AS type, 'delete' AS name, $delete AS value WHERE $delete IS NOT NULL AND :confirm IS NULL;
SELECT 'hidden' AS type, 'page' AS name, $page AS value WHERE $delete IS NOT NULL AND :confirm IS NULL;
SELECT 'hidden' AS type, 'filter' AS name, $filter AS value WHERE $delete IS NOT NULL AND :confirm IS NULL;
SELECT 'radio' AS type, 'Yes' AS value, 'confirm' AS name, 'Confirm Deletion' AS label WHERE $delete IS NOT NULL AND :confirm IS NULL;
SELECT 'radio' AS type, 'No' AS value, 'confirm' AS name, 'Do Not Delete' AS label WHERE $delete IS NOT NULL AND :confirm IS NULL;

-- Populate form fields for both adding and editing
SELECT 
    CASE WHEN $edit IS NOT NULL THEN (SELECT name FROM people WHERE id = $edit) ELSE '' END AS value,
    'Name' AS name
WHERE $edit IS NOT NULL OR $add IS NOT NULL;

SELECT 
    CASE WHEN $edit IS NOT NULL THEN (SELECT phone FROM people WHERE id = $edit) ELSE '' END AS value,
    'Phone' AS name
WHERE $edit IS NOT NULL OR $add IS NOT NULL;

SELECT 
    CASE WHEN $edit IS NOT NULL THEN (SELECT email FROM people WHERE id = $edit) ELSE '' END AS value,
    'Email' AS name
WHERE $edit IS NOT NULL OR $add IS NOT NULL;

-- Display the filter form at the top of the page
SELECT 'form' AS component;
SELECT 'text' AS type, 'filter' AS name, 'Filter...' AS placeholder, $filter AS value;  -- Filter form
SELECT 'hidden' AS type, 'page' AS name, '1' AS value;  -- Always reset page to 1 on new filter

-- Handle empty filter and create a filter pattern for SQL LIKE
SET $filter = COALESCE($filter, '');  -- Set filter to an empty string if not provided
SET $filter_pattern = '%' || $filter || '%';  -- Create a filter pattern for SQL LIKE

-- Add "Add New" button to set the $add parameter
SELECT 'button' AS component, 'center' AS justify;
SELECT '?add=1&page=' || $page AS link, 'Add New' AS title;

-- Calculate the total number of pages
SET $total_records = (SELECT COUNT(*) FROM people WHERE name LIKE $filter_pattern OR phone LIKE $filter_pattern OR email LIKE $filter_pattern);
SET $total_pages = ($total_records + $records_per_page - 1) / $records_per_page;  -- Calculate total pages using integer arithmetic

-- Pagination controls: First, Previous, Next, and Last buttons
SELECT 'text' AS component, 
    '[Home](?) | ' ||  -- Home link always appears
    '[First](?page=1&filter=' || $filter || ') | ' ||  -- First link goes to the first page with filter
    CASE
        WHEN $page > 1 THEN '[Previous](?page=' || ($page - 1) || '&filter=' || $filter || ') | ' ELSE ''
    END ||
    CASE
        WHEN (SELECT COUNT(*) FROM people WHERE name LIKE $filter_pattern OR phone LIKE $filter_pattern OR email LIKE $filter_pattern) > ($page * $records_per_page)
        THEN ' [Next](?page=' || ($page + 1) || '&filter=' || $filter || ') | ' ELSE ''
    END ||
    '[Last](?page=' || CAST($total_pages AS INTEGER) || '&filter=' || $filter || ')' AS contents_md;

-- Display the table with actions and apply pagination
SELECT 'table' AS component, 
    'Edit' AS markdown, 
    'Remove' AS markdown, 
    TRUE AS sort, 
    TRUE AS search;

SELECT  
    id AS ID, 
    name AS Name, 
    phone AS Phone, 
    email AS Email,
    '[Edit](?edit=' || id || '&page=' || $page || '&filter=' || $filter || ')' AS Edit,  -- Dynamic link for edit with filter
    '[🗑️](?delete=' || id || '&page=' || $page || '&filter=' || $filter || '&confirm=no)' AS Remove  -- Dynamic link for delete with confirmation
FROM people
WHERE name LIKE $filter_pattern OR phone LIKE $filter_pattern OR email LIKE $filter_pattern  -- Filter records
LIMIT $records_per_page OFFSET $offset;  -- Apply pagination