CREATE TABLE IF NOT EXISTS post (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    uuid TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS tag (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS posts_index (
    post_id INTEGER NOT NULL REFERENCES post(id) ON DELETE CASCADE,
    post_time INTEGER NOT NULL,
    tag_id INTEGER NOT NULL REFERENCES tag(id) ON DELETE RESTRICT
);

CREATE INDEX IF NOT EXISTS tag_idx ON tag(name);

CREATE VIEW IF NOT EXISTS posts AS SELECT pu.uuid AS id, p.post_time AS created, t.name AS tag FROM posts_index AS p JOIN tag AS t ON t.id=p.tag_id JOIN post AS pu ON p.post_id=pu.id;

/* The intention is to read this entirely into memory at app startup     */
/* This should not incur significant costs, even with millions of posts. */
CREATE TABLE IF NOT EXISTS routes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    post_id INTEGER NOT NULL REFERENCES post(id) ON DELETE CASCADE,
    route TEXT NOT NULL UNIQUE,
    method_id TEXT NOT NULL REFERENCES methods(id) ON DELETE RESTRICT,
    callback_id TEXT NOT NULL REFERENCES callbacks(id) ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS methods (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    method TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS callbacks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    callback TEXT NOT NULL UNIQUE
);

/* The actual routing table */
CREATE VIEW IF NOT EXISTS all_routes AS SELECT r.id AS id, r.route AS route, m.method AS method, c.callback AS callback FROM routes AS r JOIN methods AS m ON m.id=r.method_id JOIN callbacks AS c ON c.id=r.callback_id;

/* Fill the methods table with the HTTP verbs */
INSERT OR IGNORE INTO methods (method) VALUES ('GET'),('POST'),('DELETE'),('PUT'),('HEAD'),('PATCH'),('CONNECT'),('OPTIONS'),('TRACE');

CREATE TABLE IF NOT EXISTS post_aliases (
    route_id INTEGER NOT NULL REFERENCES routes(id) ON DELETE CASCADE,
    alias TEXT NOT NULL UNIQUE
);

CREATE VIEW IF NOT EXISTS aliases AS SELECT r.route AS actual, a.alias AS alias FROM routes AS r JOIN post_aliases AS a ON r.id=a.route_id;