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 REAL NOT NULL
		DEFAULT (unixepoch('now')),
	expires_at REAL NOT NULL
		DEFAULT (unixepoch('now', '+2 hours')),
	redeemed INTEGER NOT NULL DEFAULT (FALSE)
		CHECK (redeemed = FALSE OR redeemed = TRUE), -- boolean

	CHECK (expires_at > issued_at),

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

CREATE INDEX token_idx ON token (login_id, hash);

-- human-readable view of the tokens
CREATE VIEW token_active AS
SELECT user, hex(hash) AS hash,
	strftime('%FT%TZ', datetime(issued_at, 'unixepoch')) as issued_at,
	strftime('%FT%TZ', datetime(expires_at, 'unixepoch')) as expires_at
  FROM token
 INNER JOIN login ON login.id = token.login_id
 WHERE unixepoch('now') < token.expires_at
   AND NOT redeemed;

COMMIT;