CREATE TABLE IF NOT EXISTS users (
	id TEXT PRIMARY KEY, -- github user ID
	username TEXT NOT NULL, -- login
	email TEXT NOT NULL,
	real_name TEXT NOT NULL,
	pronouns TEXT NOT NULL,
	avatar_url TEXT NOT NULL,
	joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	is_owner BOOLEAN NOT NULL DEFAULT FALSE,
	preferences BLOB NOT NULL DEFAULT '{}'
);

CREATE TABLE IF NOT EXISTS tiers (
	id TEXT PRIMARY KEY, -- github tier ID
	name TEXT NOT NULL,
	price INTEGER NOT NULL, -- cents
	description TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS user_tiers (
	user_id TEXT UNIQUE NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	tier_id TEXT REFERENCES tiers(id) ON DELETE SET NULL,
	price INTEGER NOT NULL, -- cents
	is_one_time BOOLEAN NOT NULL DEFAULT FALSE,
	is_custom_amount BOOLEAN NOT NULL DEFAULT FALSE,
	started_at TIMESTAMP NOT NULL,
	renewed_at TIMESTAMP NOT NULL
);

-- name: User :one
SELECT
	users.id, users.username, users.email, users.real_name, users.pronouns, users.avatar_url, users.joined_at,
	user_tiers.started_at AS tier_started_at,
	user_tiers.renewed_at AS tier_renewed_at,
	tiers.id AS tier_id,
	tiers.name AS tier_name,
	tiers.price AS tier_price,
	tiers.description AS tier_description
FROM users
LEFT JOIN user_tiers ON users.id = user_tiers.user_id
LEFT JOIN tiers ON user_tiers.tier_id = tiers.id
WHERE users.id = ?;