PRAGMA foreign_keys = ON;

BEGIN;

CREATE TABLE login (
	id INTEGER PRIMARY KEY, -- internal use only
	-- case insensitive match for e.g. emails
	user TEXT COLLATE NOCASE UNIQUE NOT NULL,
	pass TEXT NOT NULL
		CHECK ( -- bcrypt
			-- $2<a/b/x/y>$[cost]$[22 character salt][31 character hash]
			length(pass) >= 58 AND
			-- corrected OpenBSD algorithm (from February 2014)
			pass LIKE '$2b$%'
		)
) STRICT;

CREATE TABLE token (
	login_id INTEGER NOT NULL,
	hash BLOB NOT NULL
		CHECK(length(hash) == 32), -- sha256
	issued_at TEXT NOT NULL
		DEFAULT (strftime('%FT%TZ', 'now'))
		CHECK (strftime('%FT%TZ', issued_at) = issued_at), -- ISO-8601
	expires_at TEXT NOT NULL
		DEFAULT (strftime('%FT%TZ', 'now', '+2 hours'))
		CHECK (strftime('%FT%TZ', expires_at) = expires_at),
	redeemed INTEGER NOT NULL DEFAULT (FALSE)
		CHECK (redeemed = FALSE OR redeemed = TRUE), -- boolean
	revoked INTEGER NOT NULL DEFAULT (FALSE)
		CHECK (revoked = FALSE OR revoked = TRUE),

	CHECK (CAST(strftime('%J', expires_at) AS REAL) >
	       CAST(strftime('%J', issued_at) AS REAL)),

	FOREIGN KEY(login_id) REFERENCES login(id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT
) STRICT;

CREATE INDEX token_idx ON token (login_id, hash);

COMMIT;