-- XXX: The directory hierarchy should be perhaps kept normalized.
CREATE TABLE IF NOT EXISTS entry(
	path     TEXT NOT NULL,     -- full FS directory path
	basename TEXT NOT NULL,     -- last FS path component
	mtime    INTEGER NOT NULL,  -- Unix time of last modification in seconds
	sha1     TEXT NOT NULL,     -- SHA-1 hash of file in lowercase hexadecimal
	PRIMARY KEY (path, basename)
);

CREATE INDEX IF NOT EXISTS entry_sha1 ON entry(sha1, path, basename);

-- XXX: Shouldn't perhaps "entry.sha1" reference "image.sha1"?
CREATE TABLE IF NOT EXISTS image(
	sha1     TEXT NOT NULL REFERENCES entry(sha1),
	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)
);

CREATE INDEX IF NOT EXISTS image_dhash ON image(dhash, sha1);

CREATE TABLE IF NOT EXISTS image_tag(
	sha1     TEXT NOT NULL REFERENCES image(sha1),
	tag      TEXT NOT NULL,
	PRIMARY KEY (sha1)
);

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

CREATE INDEX IF NOT EXISTS image_autotag_tag ON image_autotag(tag, sha1);