CREATE TABLE IF NOT EXISTS sales (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        sale_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        subtotal DECIMAL(10, 2),
        tax DECIMAL(10, 2),
        total DECIMAL(10, 2)
    );

    CREATE TABLE IF NOT EXISTS line_items (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        sale_id INTEGER,
        description TEXT,
        price DECIMAL(10, 2),
        quantity INTEGER,
        total DECIMAL(10, 2),
        FOREIGN KEY (sale_id) REFERENCES sales(id)
    );

    -- Remove a line item
    DELETE FROM line_items WHERE id = $delete;

    -- Redirect to refresh the page after deleting a line item
    SELECT 'redirect' AS component, 'cash_register.sql' AS link WHERE $delete IS NOT NULL;

    -- Form to add a new line item
    SELECT 'form' AS component, 'multipart/form-data' AS enctype;
    SELECT 'description' AS name, 'text' AS type, 'Item Description' AS placeholder;
    SELECT 'price' AS name, 'number' AS type, 'Item Price' AS placeholder, '0.00' AS value, 
        0.01 AS step;
    SELECT 'quantity' AS name, 'number' AS type, 'Quantity' AS placeholder, '1' AS value;

    -- Insert line item
    INSERT INTO line_items (description, price, quantity, total)
    SELECT :description, :price, :quantity, :price * :quantity
    WHERE :description IS NOT NULL AND :price IS NOT NULL AND :quantity IS NOT NULL;

    -- Display line items
    SELECT 
        'table' AS component, 
        'Line Items' AS title, 
        'Remove'     AS markdown,
        TRUE AS sort, 
        TRUE AS search;
    SELECT
        description AS Description,
        printf("%.2f", price) AS 'Price per Item',
        quantity AS Quantity,
        printf("%.2f", total) AS 'Total Cost',
        '[Remove](cash_register.sql?delete=' || id || ')' AS Remove
    FROM line_items;

    -- Calculate subtotal, tax, and total
    SELECT 'text' AS component, 'Subtotal: $' || printf("%.2f", SUM(total)) 
        AS contents FROM line_items;

    SELECT 'text' AS component, 'Tax (10%): $' || printf("%.2f", SUM(total) * 0.1) 
        AS contents FROM line_items;

    SELECT 'text' AS component, 'Total: $' || printf("%.2f", SUM(total) * 1.1) 
        AS contents FROM line_items;

    -- Save the transaction
    INSERT INTO sales (subtotal, tax, total)
    SELECT SUM(total), SUM(total) * 0.1, SUM(total) * 1.1 FROM line_items
    WHERE $save IS NOT NULL;

    -- Clear line items after saving
    DELETE FROM line_items WHERE $save IS NOT NULL;

    -- Show a save button
    SELECT 'form' AS component;
    SELECT 'save' AS name, 'submit' AS type, 'Complete Sale' AS value;