-- GHL OAuth Token Storage Schema
-- Run against Elestio PostgreSQL: postgresql-genesis-u50607.vm.elestio.app:25432

-- OAuth credentials table (agency + location tokens)
CREATE TABLE IF NOT EXISTS ghl_oauth_tokens (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- What this token is for
    token_type VARCHAR(20) NOT NULL CHECK (token_type IN ('agency', 'location')),
    location_id VARCHAR(50),  -- NULL for agency tokens, GHL locationId for location tokens
    location_name VARCHAR(200),  -- Human-readable name (e.g., "[MASTER] MCTB + DB Reactivation")

    -- OAuth tokens (stored encrypted)
    access_token TEXT NOT NULL,
    refresh_token TEXT,  -- NULL for location tokens (they use agency refresh)

    -- Token metadata
    scopes TEXT[],
    expires_at TIMESTAMPTZ NOT NULL,
    authorized_at TIMESTAMPTZ DEFAULT NOW(),
    last_used_at TIMESTAMPTZ,
    last_refreshed_at TIMESTAMPTZ,

    -- Audit
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    revoked_at TIMESTAMPTZ,

    -- Constraints
    UNIQUE (token_type, location_id)
);

-- Index for fast location token lookup
CREATE INDEX IF NOT EXISTS idx_ghl_tokens_location
    ON ghl_oauth_tokens(location_id) WHERE token_type = 'location' AND revoked_at IS NULL;

-- Index for finding tokens that need refresh
CREATE INDEX IF NOT EXISTS idx_ghl_tokens_expiry
    ON ghl_oauth_tokens(expires_at) WHERE revoked_at IS NULL;

-- Audit log for token operations
CREATE TABLE IF NOT EXISTS ghl_oauth_audit (
    id SERIAL PRIMARY KEY,
    token_id UUID REFERENCES ghl_oauth_tokens(id),
    operation VARCHAR(50) NOT NULL,  -- 'created', 'refreshed', 'exchanged', 'used', 'revoked', 'failed'
    location_id VARCHAR(50),
    details JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for audit queries
CREATE INDEX IF NOT EXISTS idx_ghl_audit_token ON ghl_oauth_audit(token_id);
CREATE INDEX IF NOT EXISTS idx_ghl_audit_operation ON ghl_oauth_audit(operation);

-- Updated_at trigger
CREATE OR REPLACE FUNCTION update_ghl_oauth_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_ghl_oauth_updated ON ghl_oauth_tokens;
CREATE TRIGGER trg_ghl_oauth_updated
    BEFORE UPDATE ON ghl_oauth_tokens
    FOR EACH ROW
    EXECUTE FUNCTION update_ghl_oauth_updated_at();
