stuff.txt

TODOs:
    - Guards
        SERVERSIDE
        Client
            on click of elements
            on api call
    - Global messages which last one 1 ctx cycle
    - Need to standardize the func names
    - client side redirect on not loggedin
    - handle infinite loop scenario for comments


QUERIES:

    CREATE TABLE USERS(
        UNQID TEXT PRIMARY KEY,
        EMAIL TEXT NOT NULL UNIQUE,
        PASSWORD TEXT NOT NULL,
        NICKNAME TEXT NOT NULL,
        FLAIR TEXT,
        IS_DELETED BOOL DEFAULT FALSE,
        CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        DELETED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP        
    );

    CREATE TABLE POSTS(
        UNQID TEXT PRIMARY KEY,
        TITLE TEXT NOT NULL,
        LINK TEXT,
        THUMB TEXT,
        CONTENT TEXT,
        AUTHOR_ID TEXT NOT NULL,
        AUTHOR_NICK TEXT,
        IS_DELETED BOOL DEFAULT FALSE,
        CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        DELETED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE COMMENTS(
        UNQID TEXT PRIMARY KEY,
        LEVEL INT NOT NULL,
        POST_ID TEXT,
        PARENT_ID TEXT,
        children_ids TEXT [] DEFAULT '{}',
        CONTENT TEXT,
        AUTHOR_ID TEXT NOT NULL,
        AUTHOR_NICK TEXT,
        IS_DELETED BOOL DEFAULT FALSE,
        CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        DELETED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

    UPDATE users SET children_ids = children_ids || '{unqid}' WHERE unqid = parent_id;

    insert into comments (unqid, level, post_id, parent_id,  children_ids, content, author_id) 
    values ('cid100', 0, 'c47b5af1-a43b-4488-86cd-a42645b66c10' , 'none', '{"cid110", "cid120" }', 'This is a PARENT level comment', 'JRR Tolkien');

    insert into comments (unqid, level, post_id, parent_id,  children_ids, content, author_id) 
    values ('cid110', 1, 'c47b5af1-a43b-4488-86cd-a42645b66c10' , 'cid100', '{"cid112"}', 'This is a CHILD level comment', 'Mark Twain');

    insert into comments (unqid, level, post_id, parent_id,  children_ids, content, author_id) 
    values ('cid120', 1, 'c47b5af1-a43b-4488-86cd-a42645b66c10' , 'cid100', '{}', 'This is a 2nd CHILD level comment', 'JK Rowling');

    insert into comments (unqid, level, post_id, parent_id,  children_ids, content, author_id) 
    values ('cid112', 2, 'c47b5af1-a43b-4488-86cd-a42645b66c10' , 'cid110', '{}', 'This is a GRANDCHILD level comment', 'RL Stephenson');

    insert into comments (unqid, level, post_id, parent_id,  children_ids, content, author_id) 
    values ('cid200', 0, 'c47b5af1-a43b-4488-86cd-a42645b66c10' , 'none', '{}', 'This is 2nd PARENT level comment', 'Dante');