PRAGMA journal_mode = WAL;

CREATE TABLE media(
	id INTEGER PRIMARY KEY,
	storage_id TEXT (64) NOT NULL UNIQUE,
	filename TEXT (256) NOT NULL,
	content_type TEXT (256),
	upload_datetime TEXT (32) DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE kind(
	id TEXT (32) PRIMARY KEY,
	name TEXT (256) NOT NULL UNIQUE
) WITHOUT ROWID;

CREATE TABLE tag(
	id INTEGER PRIMARY KEY,
	name TEXT (256) NOT NULL,
	kind_id TEXT (32) REFERENCES kind(id) ON UPDATE CASCADE,
	display TEXT (289) AS (concat_ws(':', kind_id, name)) STORED,
	UNIQUE (name, kind_id)
);
CREATE INDEX tag_name_idx ON tag(name);
CREATE UNIQUE INDEX tag_name_kind_id_idx ON tag(name, kind_id);
CREATE UNIQUE INDEX tag_display_idx ON tag(display);

CREATE TABLE media_tag(
	media_id INTEGER NOT NULL,
	tag_id INTEGER NOT NULL,
	FOREIGN KEY (media_id) REFERENCES media(id) ON DELETE CASCADE,
	FOREIGN KEY (tag_id) REFERENCES tag(id) ON DELETE CASCADE,
	PRIMARY KEY (media_id, tag_id)
);
CREATE INDEX media_tag_media_id_idx ON media_tag(media_id);
CREATE INDEX media_tag_tag_id_idx ON media_tag(tag_id);