--- Tables
CREATE TABLE `tags` (
  `id` INTEGER NOT NULL PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `hidden` INTEGER NOT NULL,
  `hidden_thumbnail` VARCHAR(255) DEFAULT "",
  `preview_enabled` INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE `images` (
  `id` INTEGER NOT NULL PRIMARY KEY,
  `uuid` CHAR(36) NOT NULL,
  `filename` VARCHAR(255) NOT NULL,
  `orig_filename` VARCHAR(255) NOT NULL,
  `timestamp` INTEGER NOT NULL
);

CREATE TABLE `imagetags` (
  `image_id` INTEGER NOT NULL,
  `tag_id` INTEGER NOT NULL,
  PRIMARY KEY(`image_id`, `tag_id`)
);

CREATE TABLE `metadata` (
  `id` INTEGER NOT NULL PRIMARY KEY,
  `image_id` INTEGER NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT NOT NULL,
  FOREIGN KEY (`image_id`) REFERENCES `images` (`id`)
);

--- Initial data
INSERT INTO `tags` (`name`, `hidden`) VALUES('spoiler', true);
INSERT INTO `tags` (`name`, `hidden`) VALUES('sensitive', true);

UPDATE `tags` SET `hidden_thumbnail`='thumbnail_hidden.jpg' WHERE `id`=1;
UPDATE `tags` SET `hidden_thumbnail`='thumbnail_sensitive.jpg' WHERE `id`=2;