-- Table for forum topics
CREATE TABLE IF NOT EXISTS topics (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table for forum messages
CREATE TABLE IF NOT EXISTS messages (
    id INTEGER PRIMARY KEY,
    topic_id INTEGER NOT NULL,
    author TEXT NOT NULL,
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (topic_id) REFERENCES topics(id)
);

-- Insert new topic
INSERT INTO topics (title)
SELECT :title WHERE :title IS NOT NULL;

-- Get the last inserted topic ID
SET $topic_id = (SELECT MAX(id) FROM topics WHERE title = :title);

-- Insert initial message if topic ID is valid
INSERT INTO messages (topic_id, author, message)
SELECT $topic_id, :author, :message WHERE :author IS NOT NULL AND :message IS NOT NULL;

-- Display list of topics
SELECT 'table' AS component, 
    'Forum Topics' AS title, 
    'View'  AS markdown, 
    TRUE AS sort, 
    TRUE AS search;
SELECT
    t.id AS ID,
    t.title AS Topic,
    m.created_at AS 'Last Message',
    m.last_author AS 'Last Poster',
    '[View](topic.sql?topic_id=' || t.id || ')' AS View
FROM topics t
LEFT JOIN (
    SELECT topic_id, MAX(created_at) AS created_at, MAX(author) AS last_author
    FROM messages 
    GROUP BY topic_id
) m ON t.id = m.topic_id
ORDER BY m.created_at DESC;

-- Form for adding a new topic and initial message
SELECT 'form' AS component, 'multipart/form-data' AS enctype;
SELECT 'title' AS name, 'text' AS type, 'Enter topic title' AS placeholder;
SELECT 'author' AS name, 'text' AS type, 'Your name' AS placeholder;
SELECT 'message' AS name, 'textarea' AS type, 'Your message' AS placeholder;