CREATE TABLE tenants (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE tenant_members (
    tenant TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    user_id TEXT NOT NULL REFERENCES users(id),
    owner BOOLEAN NOT NULL DEFAULT false,
    PRIMARY KEY (tenant, user_id)
);

CREATE INDEX idx_tenant_members_user ON tenant_members(user_id);

CREATE TABLE tenant_invitations (
    tenant     TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    email      TEXT NOT NULL,
    token_key  TEXT NOT NULL UNIQUE,
    token_code TEXT NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    PRIMARY KEY (tenant, email)
);

CREATE INDEX idx_tenant_invitations_email ON tenant_invitations(email);