-- createTables.sql:
--   write:
		CREATE TABLE IF NOT EXISTS "cracha_users" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
			uuid         BLOB    NOT NULL UNIQUE,
			email        TEXT    NOT NULL UNIQUE,
			salt         BLOB    NOT NULL UNIQUE,
			pwhash       BLOB    NOT NULL
		) STRICT;

		CREATE TABLE IF NOT EXISTS "cracha_confirmation_attempts" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
			-- uuid         BLOB    NOT NULL UNIQUE,
			user_id      INTEGER NOT NULL REFERENCES "cracha_users"(id),
			token        TEXT    NOT NULL UNIQUE
		) STRICT;

		CREATE TABLE IF NOT EXISTS "cracha_user_confirmations" (
			id           INTEGER PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
			user_id      INTEGER NOT NULL
				REFERENCES "cracha_users"(id) UNIQUE,
			attempt_id   INTEGER NOT NULL
				REFERENCES "cracha_confirmation_attempts"(id) UNIQUE
		) STRICT;

		CREATE TABLE IF NOT EXISTS "cracha_user_changes" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
			user_id      INTEGER NOT NULL REFERENCES "cracha_users"(id),
			attribute    TEXT    NOT NULL,
			value        TEXT    NOT NULL,
			op           INT     NOT NULL CHECK(op IN (0, 1))
		) STRICT;

		-- CREATE TABLE IF NOT EXISTS "cracha_tokens" (
			-- id        INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			-- timestamp    TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
			-- uuid         BLOB    NOT NULL UNIQUE,
			-- type         TEXT    NOT NULL
		-- ) STRICT;

		CREATE TABLE IF NOT EXISTS "cracha_roles" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			user_id      INTEGER NOT NULL REFERENCES "cracha_users"(id),
			role         TEXT    NOT NULL,
			UNIQUE (user_id, role)
		) STRICT;

		CREATE TABLE IF NOT EXISTS "cracha_role_changes" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
			user_id      INTEGER NOT NULL REFERENCES "cracha_roles"(id),
			role         TEXT    NOT NULL,
			op           INT     NOT NULL CHECK(op IN (0, 1))
		) STRICT;

		CREATE TABLE IF NOT EXISTS "cracha_sessions" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
			uuid         BLOB    NOT NULL UNIQUE,
			user_id      INTEGER NOT NULL REFERENCES "cracha_users"(id)
			-- type         TEXT    NOT NULL,
			-- revoked_at   TEXT,
			-- revoker_id   INTEGER          REFERENCES "cracha_users"(id),
			-- FIXME: add provenance: login, refresh, confirmation, etc.
		) STRICT;

		CREATE TABLE IF NOT EXISTS "cracha_attempts" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
			user_id      INTEGER          REFERENCES "cracha_users"(id),
			session_id   INTEGER          REFERENCES "cracha_sessions"(id)
		) STRICT;

		CREATE TABLE IF NOT EXISTS "cracha_audit" (
			id           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
			timestamp    TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')),
			uuid         BLOB    NOT NULL UNIQUE,
			attribute    TEXT    NOT NULL,
			value        TEXT    NOT NULL,
			op           INT     NOT NULL CHECK(op IN (0, 1))
		) STRICT;
	

--    read:

-- register.sql:
--   write:
		INSERT INTO "cracha_users" (uuid, email, salt, pwhash)
		VALUES (?, ?, ?, ?) RETURNING id, timestamp;
	

--    read:
		SELECT id, timestamp from "cracha_users"
		WHERE uuid = ?;
	

-- sendToken.sql:
--   write:
		INSERT INTO "cracha_confirmation_attempts" (user_id, token)
		VALUES (
			(SELECT id FROM "cracha_users" WHERE uuid = ?),
			?
		)
	

--    read:

-- confirm.sql:
--   write:
		INSERT INTO "cracha_user_confirmations" (user_id, attempt_id)
		VALUES (?, ?);
	

--    read:
		SELECT
			"cracha_confirmation_attempts".id,
			"cracha_confirmation_attempts".user_id,
			"cracha_users".uuid
		FROM "cracha_confirmation_attempts"
		JOIN "cracha_users" ON
			"cracha_confirmation_attempts".user_id = "cracha_users".id
		WHERE token = ?;
	

-- byEmail.sql:
--   write:

--    read:
		SELECT id, timestamp, uuid, salt, pwhash, (
			CASE WHEN EXISTS (
				SELECT id FROM "cracha_user_confirmations"
				WHERE user_id = (
					SELECT id FROM  "cracha_users"
					WHERE email = ?
				)
			) THEN 1
			ELSE   0
			END
		) as confirmed
		FROM "cracha_users" WHERE email = ?;
	

-- userByUUID.sql:
--   write:

--    read:
		SELECT id, timestamp, email, salt, pwhash, (
			CASE WHEN EXISTS (
				SELECT id FROM "cracha_user_confirmations"
				WHERE user_id = (
					SELECT id FROM "cracha_users"
					WHERE uuid = ?
				)
			) THEN 1
			ELSE   0
			END
		) as confirmed
		FROM "cracha_users" WHERE uuid = ?;
	

-- login.sql:
--   write:

--    read:

-- refresh.sql:
--   write:
		INSERT INTO "cracha_sessions" (uuid, user_id)
		VALUES (
			?,
			(SELECT user_id FROM "cracha_sessions" WHERE uuid = ?)
		) RETURNING id, timestamp, (
			SELECT "cracha_users".uuid FROM "cracha_users"
			JOIN "cracha_sessions" ON
				"cracha_users".id = "cracha_sessions".user_id
			WHERE "cracha_sessions".uuid = ?
		) AS userID;
	

--    read:

-- reset.sql:
--   write:
		-- INSERT SOMETHING cracha
	

--    read:

-- change.sql:
--   write:
		-- INSERT SOMETHING cracha
	

--    read:

-- byUUID.sql:
--   write:

--    read:
		-- INSERT SOMETHING cracha
	

-- logout.sql:
--   write:
		-- INSERT SOMETHING cracha
	

--    read:

-- outOthers.sql:
--   write:
		-- INSERT SOMETHING cracha
	

--    read:

-- outAll.sql:
--   write:
		-- INSERT SOMETHING cracha
	

--    read: