CREATE TABLE images (
       id INTEGER PRIMARY KEY AUTOINCREMENT,
       hash TEXT NOT NULL UNIQUE,
       title TEXT,
       description TEXT,
       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

--;;

CREATE TABLE tags (
       id INTEGER PRIMARY KEY AUTOINCREMENT,
       tagname TEXT NOT NULL UNIQUE
);

--;;

CREATE TABLE image_tags (
       id INTEGER PRIMARY KEY AUTOINCREMENT,
       image_id INTEGER NOT NULL,
       tag_id INTEGER NOT NULL
)

--;;

CREATE TABLE comments (
       id INTEGER PRIMARY KEY AUTOINCREMENT,
       image_id INTEGER NOT NULL,
       author TEXT,
       comment TEXT NOT NULL,
       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

--;;

CREATE INDEX hash_index ON images (hash);
CREATE INDEX tagname_index ON tags (tagname);
CREATE INDEX image_tags_index ON image_tags (image_id, tag_id);
CREATE INDEX author_index ON comments (author);