CREATE TABLE IF NOT EXISTS fabric_service_channel_route_feedback_observations (
    id UUID PRIMARY KEY,
    cluster_id UUID NOT NULL REFERENCES clusters(id) ON DELETE CASCADE,
    reporter_node_id UUID NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
    route_id TEXT NOT NULL,
    service_class TEXT NOT NULL,
    feedback_status TEXT NOT NULL,
    score_adjustment INTEGER NOT NULL DEFAULT 0,
    reasons TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
    last_error TEXT NOT NULL DEFAULT '',
    consecutive_failures INTEGER NOT NULL DEFAULT 0,
    stall_count INTEGER NOT NULL DEFAULT 0,
    last_send_duration_ms BIGINT NOT NULL DEFAULT 0,
    payload JSONB NOT NULL DEFAULT '{}'::JSONB,
    observed_at TIMESTAMPTZ NOT NULL,
    expires_at TIMESTAMPTZ NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_fsc_route_feedback_observed
    ON fabric_service_channel_route_feedback_observations (cluster_id, reporter_node_id, service_class, observed_at DESC);

CREATE INDEX IF NOT EXISTS idx_fsc_route_feedback_route
    ON fabric_service_channel_route_feedback_observations (cluster_id, route_id, observed_at DESC);

CREATE TABLE IF NOT EXISTS fabric_service_channel_route_feedback_latest (
    cluster_id UUID NOT NULL REFERENCES clusters(id) ON DELETE CASCADE,
    reporter_node_id UUID NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
    route_id TEXT NOT NULL,
    observation_id UUID NOT NULL REFERENCES fabric_service_channel_route_feedback_observations(id) ON DELETE CASCADE,
    service_class TEXT NOT NULL,
    feedback_status TEXT NOT NULL,
    score_adjustment INTEGER NOT NULL DEFAULT 0,
    reasons TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
    last_error TEXT NOT NULL DEFAULT '',
    consecutive_failures INTEGER NOT NULL DEFAULT 0,
    stall_count INTEGER NOT NULL DEFAULT 0,
    last_send_duration_ms BIGINT NOT NULL DEFAULT 0,
    payload JSONB NOT NULL DEFAULT '{}'::JSONB,
    observed_at TIMESTAMPTZ NOT NULL,
    expires_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (cluster_id, reporter_node_id, route_id)
);

CREATE INDEX IF NOT EXISTS idx_fsc_route_feedback_latest_active
    ON fabric_service_channel_route_feedback_latest (cluster_id, reporter_node_id, service_class, expires_at DESC);