-- Migration 000002: Routing policies
-- Foundation for V2 DB-driven routing engine.
-- Allows per-tenant, per-model routing rules with role-based access control.

-- ============================================================
-- ROUTING POLICIES
-- ============================================================
CREATE TABLE routing_policies (
    id             UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id      UUID        NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    -- model_pattern supports exact match ("gpt-4o") or prefix match ("gpt-4o-mini").
    -- The application applies these in priority order; the first match wins.
    model_pattern  TEXT        NOT NULL,
    -- provider is the upstream adapter name: "openai", "anthropic", "azure", "mistral", "ollama".
    provider       TEXT        NOT NULL CHECK (provider IN ('openai', 'anthropic', 'azure', 'mistral', 'ollama')),
    -- allowed_roles is the list of Keycloak roles permitted to use this rule.
    -- Empty array means all roles can use this routing rule.
    allowed_roles  TEXT[]      NOT NULL DEFAULT '{}',
    -- priority: lower number = higher priority (evaluated first).
    priority       INT         NOT NULL DEFAULT 100,
    is_active      BOOLEAN     NOT NULL DEFAULT TRUE,
    created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    UNIQUE (tenant_id, model_pattern, provider)
);

-- Index for fast per-tenant active policy lookups (ordered by priority).
CREATE INDEX idx_routing_policies_tenant_active
    ON routing_policies (tenant_id, is_active, priority);

-- ============================================================
-- AUTO-UPDATE updated_at TRIGGER
-- ============================================================
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER routing_policies_updated_at
    BEFORE UPDATE ON routing_policies
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- ============================================================
-- ROW LEVEL SECURITY
-- ============================================================
ALTER TABLE routing_policies ENABLE ROW LEVEL SECURITY;

-- Tenants can only read/write their own routing policies.
CREATE POLICY routing_policies_tenant_isolation ON routing_policies
    USING (tenant_id = current_setting('app.current_tenant_id', TRUE)::UUID);