CREATE TABLE IF NOT EXISTS image(
	sha1     TEXT NOT NULL,  -- SHA-1 hash of file in lowercase hexadecimal
	thumbw   INTEGER,        -- cached thumbnail width, if known
	thumbh   INTEGER,        -- cached thumbnail height, if known
	dhash    INTEGER,        -- uint64 perceptual hash as a signed integer
	PRIMARY KEY (sha1)
) STRICT;

CREATE INDEX IF NOT EXISTS image__dhash ON image(dhash);

--

-- NOTE: This table requires garbage collection. Perhaps as a trigger.
CREATE TABLE IF NOT EXISTS directory(
	id       INTEGER NOT NULL,                  -- unique ID
	name     TEXT NOT NULL,                     -- basename
	parent   INTEGER REFERENCES directory(id),  -- root if NULL
	PRIMARY KEY (id)
) STRICT;

CREATE UNIQUE INDEX IF NOT EXISTS directory__parent_name
ON directory(IFNULL(parent, 0), name);

-- FIXME: I want a nullable parent, but that can't be a primary key.
--  - Perhaps have an INTEGER for the PK of entry, and use a UNIQUE INDEX.
--  - Alternatively, create a directory record for the root.
CREATE TABLE IF NOT EXISTS entry(
	parent   INTEGER NOT NULL REFERENCES directory(id),
	name     TEXT NOT NULL,     -- last FS path component
	mtime    INTEGER NOT NULL,  -- Unix time of last modification in seconds
	sha1     TEXT NOT NULL REFERENCES image(sha1),
	PRIMARY KEY (parent, name)
) STRICT;

CREATE INDEX IF NOT EXISTS entry__sha1 ON entry(sha1);

--

CREATE TABLE IF NOT EXISTS tag_space(
	id          INTEGER NOT NULL,
	name        TEXT NOT NULL,
	description TEXT,
	PRIMARY KEY (id)
) STRICT;

CREATE UNIQUE INDEX IF NOT EXISTS tag_space__name ON tag_space(name);

-- To avoid having to deal with NULLs, always create this special tag space.
INSERT INTO tag_space(id, name, description)
VALUES(0, '', 'User-defined tags')
ON CONFLICT DO NOTHING;

-- NOTE: This table requires garbage collection. Perhaps as a trigger.
CREATE TABLE IF NOT EXISTS tag(
	id       INTEGER NOT NULL,
	space    INTEGER NOT NULL REFERENCES tag_space(id),
	name     TEXT NOT NULL,
	PRIMARY KEY (id)
) STRICT;

CREATE UNIQUE INDEX IF NOT EXISTS tag__space_name ON tag(space, name);

CREATE TABLE IF NOT EXISTS tag_assignment(
	sha1     TEXT NOT NULL REFERENCES image(sha1),
	tag      INTEGER NOT NULL REFERENCES tag(id),
	weight   REAL NOT NULL,     -- 0..1 normalized weight assigned to tag
	PRIMARY KEY (sha1, tag)
) STRICT;

CREATE INDEX IF NOT EXISTS tag_assignment__tag ON tag_assignment(tag);