-- Sprint 3: Staff Portal foundation
-- Staff accounts, permissions, revoked tokens, prevention officer support

-- Staff accounts table (links users with STAFF role to their permissions)
CREATE TABLE staff_accounts (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES clubs(id),
    user_id         UUID NOT NULL UNIQUE REFERENCES users(id),
    display_name    VARCHAR(150) NOT NULL,
    is_prevention_officer BOOLEAN NOT NULL DEFAULT FALSE,
    active          BOOLEAN NOT NULL DEFAULT TRUE,
    invited_at      TIMESTAMPTZ,
    activated_at    TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Staff account permissions (element collection table)
CREATE TABLE staff_account_permissions (
    staff_account_id UUID NOT NULL REFERENCES staff_accounts(id) ON DELETE CASCADE,
    permission       VARCHAR(50) NOT NULL,
    PRIMARY KEY (staff_account_id, permission)
);

-- Revoked tokens table for JWT blacklisting
CREATE TABLE revoked_tokens (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    jti         VARCHAR(36) NOT NULL UNIQUE,
    user_id     UUID NOT NULL,
    tenant_id   UUID NOT NULL,
    revoked_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at  TIMESTAMPTZ NOT NULL,
    reason      VARCHAR(100)
);

-- Indexes for revoked tokens
CREATE INDEX idx_revoked_tokens_jti ON revoked_tokens(jti);
CREATE INDEX idx_revoked_tokens_user_id ON revoked_tokens(user_id);
CREATE INDEX idx_revoked_tokens_expires_at ON revoked_tokens(expires_at);

-- Index for staff accounts
CREATE INDEX idx_staff_accounts_tenant_id ON staff_accounts(tenant_id);
CREATE INDEX idx_staff_accounts_user_id ON staff_accounts(user_id);

-- Add max_prevention_officers to clubs table (default 2 per plan)
ALTER TABLE clubs ADD COLUMN max_prevention_officers INTEGER NOT NULL DEFAULT 2;
