-- add_todo.sql
SELECT 'form' AS component,
    'Add Todo' AS title,
    'create_todo.sql' AS action;

SELECT 'text' AS type, 'Title' AS name, TRUE AS required;
SELECT 'textarea' AS type, 'Description' AS name;
SELECT 'date' AS type, 'Due Date' AS name, 'due_date' AS name;

-- create_todo.sql
INSERT INTO todos (title, description, due_date)
VALUES (:Title, :Description, :due_date)
RETURNING
    'redirect' AS component,
    '/?created=true' AS link;

INSERT INTO todos (title, description)
VALUES (:Title, :Description)
RETURNING
    'redirect' AS component,
    '/todo.sql?id=' || id AS link;

-- index.sql
SELECT 'list' AS component,
    'Todo List' AS title,
    'No todos yet...' AS empty_title;

SELECT
    title,
    description AS description,
    'edit.sql?id=' || id AS edit_link,
    'delete.sql?id=' || id AS delete_link
FROM todos
ORDER BY created_at DESC;

-- view_todo.sql
SELECT 'card' AS component;

SELECT
    title AS title,
    description AS description_md,
    'Edit' AS link,
    'edit.sql?id=' || id AS link
FROM todos
WHERE id = $id;

-- edit_todo.sql
SELECT 'form' AS component,
    'Edit Todo' AS title,
    'update_todo.sql?id=' || $id AS action;

SELECT
    'text' AS type,
    'Title' AS name,
    title AS value,
    TRUE AS required
FROM todos WHERE id = $id;

SELECT
    'textarea' AS type,
    'Description' AS name,
    description AS value
FROM todos WHERE id = $id;

SELECT
    'checkbox' AS type,
    'Completed' AS name,
    completed AS checked
FROM todos WHERE id = $id;

-- update_todo.sql
UPDATE todos
SET
    title = :Title,
    description = :Description,
    completed = :Completed IS NOT NULL,
    updated_at = CURRENT_TIMESTAMP
WHERE id = $id
RETURNING
    'redirect' AS component,
    '/todo.sql?id=' || id AS link;

-- Check if checkbox is checked (it will be NULL if unchecked)
SET is_completed = :Completed IS NOT NULL;

UPDATE todos
SET completed = $is_completed
WHERE id = $id;

-- delete.sql
-- Delete if user confirmed
DELETE FROM todos
WHERE id = $todo_id AND $confirm = 'yes'
RETURNING
    'redirect' AS component,
    '/' AS link;

-- If not confirmed yet, show confirmation
SELECT 'dynamic' AS component, sqlpage.run_sql('shell.sql') AS properties;

SELECT
    'alert' AS component,
    'red' AS color,
    'Confirm deletion' AS title,
    'Are you sure you want to delete: ' || title || '?' AS description,
    '?todo_id=' || $todo_id || '&confirm=yes' AS link,
    'Delete' AS link_text
FROM todos
WHERE id = $todo_id;

UPDATE todos
SET
    deleted_at = CURRENT_TIMESTAMP,
    deleted_by = current_user_id()
WHERE id = $id
RETURNING 'redirect' AS component, '/' AS link;

-- In your list queries, exclude deleted items
SELECT * FROM todos
WHERE deleted_at IS NULL;

SELECT 'form' AS component, 'User Profile' AS title, 'save.sql' AS action;

-- Text input
SELECT 'text' AS type, 'Name' AS name, TRUE AS required;

-- Email input with validation
SELECT 'email' AS type, 'Email' AS name, TRUE AS required;

-- Password input
SELECT 'password' AS type, 'Password' AS name, TRUE AS required;

-- Number input with range
SELECT 'number' AS type, 'Age' AS name, 0 AS min, 120 AS max;

-- Date and time
SELECT 'date' AS type, 'Birth Date' AS name;
SELECT 'datetime-local' AS type, 'Appointment' AS name;

-- Dropdown select
SELECT 'select' AS type, 'Category' AS name, TRUE AS required,
    JSON_ARRAY(
        JSON_OBJECT('label', 'Work', 'value', 'work'),
        JSON_OBJECT('label', 'Personal', 'value', 'personal')
    ) AS options;

-- Textarea
SELECT 'textarea' AS type, 'Notes' AS name, 5 AS rows;

-- Checkbox
SELECT 'checkbox' AS type, 'Subscribe' AS name, 'Subscribe to newsletter' AS label;

-- Radio buttons
SELECT 'radio' AS type, 'Priority' AS name, 'High' AS value, 'High Priority' AS label;
SELECT 'radio' AS type, 'Priority' AS name, 'Low' AS value, 'Low Priority' AS label;

-- File upload
SELECT 'file' AS type, 'Avatar' AS name, 'image/*' AS accept;

-- Hidden field
SELECT 'hidden' AS type, 'user_id' AS name, '123' AS value;

SELECT 'form' AS component, 'Assign Task' AS title, 'assign.sql' AS action;

SELECT
    'select' AS type,
    'Assignee' AS name,
    TRUE AS required,
    JSON_GROUP_ARRAY(
        JSON_OBJECT('label', username, 'value', id)
    ) AS options
FROM users
WHERE active = TRUE;

-- Step 1: Basic info
SELECT 'form' AS component, 'Step 1: Basic Info' AS title, 'step2.sql' AS action;
SELECT 'text' AS type, 'Name' AS name, TRUE AS required;
SELECT 'email' AS type, 'Email' AS name, TRUE AS required;

-- step2.sql
SELECT 'form' AS component, 'Step 2: Details' AS title, 'complete.sql' AS action;

-- Carry forward previous values
SELECT 'hidden' AS type, 'Name' AS name, :Name AS value;
SELECT 'hidden' AS type, 'Email' AS name, :Email AS value;

SELECT 'textarea' AS type, 'Bio' AS name;
SELECT 'date' AS type, 'Birth Date' AS name;

-- Required field
SELECT 'text' AS type, 'Username' AS name, TRUE AS required;

-- Pattern matching
SELECT 'text' AS type, 'Phone' AS name,
    '^\d{3}-\d{3}-\d{4}$' AS pattern,
    'Format: 555-555-5555' AS description;

-- Min/max length
SELECT 'text' AS type, 'Bio' AS name,
    10 AS minlength,
    500 AS maxlength;

-- Min/max values for numbers
SELECT 'number' AS type, 'Quantity' AS name,
    1 AS min,
    100 AS max;

-- Validate required field
SELECT 'redirect' AS component, 'form.sql?error=missing_title' AS link
WHERE :Title IS NULL OR :Title = '';

-- Validate format
SELECT 'redirect' AS component, 'form.sql?error=invalid_email' AS link
WHERE :Email NOT LIKE '%@%.%';

-- Proceed with insert if validation passed
INSERT INTO users (name, email) VALUES (:Name, :Email);

-- Create order with items
INSERT INTO orders (customer_id, order_date)
VALUES (:customer_id, CURRENT_DATE)
RETURNING id AS order_id;

-- Insert order items
INSERT INTO order_items (order_id, product_id, quantity)
SELECT
    (SELECT order_id FROM orders ORDER BY id DESC LIMIT 1),
    product_id,
    quantity
FROM JSON_EACH(:items);

-- Assign multiple tags to a post
-- First, create the post
INSERT INTO posts (title, content)
VALUES (:title, :content)
RETURNING id AS post_id;

-- Then create the relationships
DELETE FROM post_tags WHERE post_id = (SELECT post_id FROM posts ORDER BY id DESC LIMIT 1);

INSERT INTO post_tags (post_id, tag_id)
SELECT
    (SELECT post_id FROM posts ORDER BY id DESC LIMIT 1),
    value
FROM JSON_EACH(:selected_tags);

-- Show error message if present in URL
SELECT 'alert' AS component,
    'red' AS color,
    'Validation Error' AS title,
    CASE $error
        WHEN 'missing_title' THEN 'Title is required'
        WHEN 'invalid_email' THEN 'Please enter a valid email address'
        ELSE 'An error occurred'
    END AS description
WHERE $error IS NOT NULL;

-- Display the form
SELECT 'form' AS component, 'Add User' AS title, 'create.sql' AS action;

-- Try to insert, handle duplicate email error
INSERT OR IGNORE INTO users (email, name)
VALUES (:email, :name);

-- Check if insert succeeded
SELECT 'redirect' AS component, '/success.sql' AS link
WHERE CHANGES() > 0;

-- Show error if email already exists
SELECT 'alert' AS component,
    'red' AS color,
    'Error' AS title,
    'This email address is already registered' AS description
WHERE CHANGES() = 0;

INSERT INTO logs (user_id, action, timestamp)
VALUES
    (1, 'login', CURRENT_TIMESTAMP),
    (2, 'logout', CURRENT_TIMESTAMP),
    (3, 'update', CURRENT_TIMESTAMP);

BEGIN TRANSACTION;

INSERT INTO orders (customer_id) VALUES (:customer_id);
INSERT INTO order_items (order_id, product_id) VALUES (last_insert_rowid(), :product_id);
UPDATE inventory SET quantity = quantity - :quantity WHERE product_id = :product_id;

COMMIT;

CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- SQLite
INSERT INTO settings (key, value)
VALUES ('theme', 'dark')
ON CONFLICT(key) DO UPDATE SET value = excluded.value;

-- PostgreSQL
INSERT INTO settings (key, value)
VALUES ('theme', 'dark')
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;

-- MySQL
INSERT INTO settings (key, value)
VALUES ('theme', 'dark')
ON DUPLICATE KEY UPDATE value = VALUES(value);

-- Like a post
UPDATE posts
SET like_count = like_count + 1
WHERE id = $post_id
RETURNING 'redirect' AS component, '/post.sql?id=' || id AS link;

-- Toggle completion status
UPDATE todos
SET completed = NOT completed
WHERE id = $id
RETURNING 'redirect' AS component, '/' AS link;