BEGIN;

CREATE TABLE project_accounts (
    project text NOT NULL,
    id uuid NOT NULL,
    google_subject text NOT NULL,
    email text,
    PRIMARY KEY (project, id),
    UNIQUE (project, google_subject)
);

CREATE TABLE oauth_attempts (
    state_hash text PRIMARY KEY,
    browser_hash text NOT NULL,
    project text NOT NULL,
    frontend_challenge text NOT NULL,
    google_verifier text NOT NULL,
    expires_at timestamptz NOT NULL
);

CREATE TABLE login_grants (
    token_hash text PRIMARY KEY,
    project text NOT NULL,
    account_id uuid NOT NULL,
    frontend_challenge text NOT NULL,
    expires_at timestamptz NOT NULL,
    FOREIGN KEY (project, account_id) REFERENCES project_accounts (project, id) ON DELETE CASCADE
);

CREATE TABLE project_sessions (
    token_hash text PRIMARY KEY,
    project text NOT NULL,
    account_id uuid NOT NULL,
    expires_at timestamptz NOT NULL,
    FOREIGN KEY (project, account_id) REFERENCES project_accounts (project, id) ON DELETE CASCADE
);

CREATE INDEX oauth_attempts_expiry ON oauth_attempts (expires_at);
CREATE INDEX login_grants_expiry ON login_grants (expires_at);
CREATE INDEX project_sessions_expiry ON project_sessions (expires_at);

COMMIT;