CREATE TABLE IF NOT EXISTS channels (
    id VARCHAR(40),
    detail TEXT,
    private BOOL,
    PRIMARY KEY(id)
);

CREATE TABLE IF NOT EXISTS users (
    email VARCHAR(40),
    nickname VARCHAR(40) UNIQUE,
    password VARCHAR(40),
    propic TEXT,
    PRIMARY KEY(email)
);

CREATE TABLE IF NOT EXISTS allowed_users (
    user_email VARCHAR(40),
    channel_id VARCHAR(40),
    PRIMARY KEY(user_email, channel_id),
    FOREIGN KEY(user_email) REFERENCES users(email)
);

CREATE TABLE IF NOT EXISTS messages (
    id VARCHAR(40),
    channel_id VARCHAR(40),
    data TEXT,
    author VARCHAR(40),
    ts timestamp NOT NULL DEFAULT NOW(),
    PRIMARY KEY(id),
    FOREIGN KEY(author) REFERENCES users(email)
);

INSERT INTO
    channels (id, detail, private)
VALUES
    ('Crypto', 'web3 without crypto?', FALSE);

INSERT INTO
    channels (id, detail, private)
VALUES
    ('Random', 'whatever', FALSE);

INSERT INTO
    channels (id, detail, private)
VALUES
    (
        'Brews',
        'Share your best caffeine based concoction',
        FALSE
    );