

-- Intelligence adjustments log
CREATE TABLE IF NOT EXISTS intelligence_adjustments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  adjustment_type VARCHAR(50) NOT NULL, -- 'threshold', 'prompt', 'pattern'
  changes JSONB NOT NULL DEFAULT '[]',
  reason TEXT,
  applied_by VARCHAR(100) DEFAULT 'system',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Index for efficient queries
CREATE INDEX IF NOT EXISTS idx_intelligence_adjustments_type
ON intelligence_adjustments(adjustment_type);

CREATE INDEX IF NOT EXISTS idx_intelligence_adjustments_created
ON intelligence_adjustments(created_at);
ALTER TABLE intelligence_adjustments ENABLE ROW LEVEL SECURITY;

-- Service role full access
CREATE POLICY "Service role full access system_settings"
  ON system_settings FOR ALL TO service_role
  USING (true) WITH CHECK (true);

CREATE POLICY "Service role full access intelligence_adjustments"
  ON intelligence_adjustments FOR ALL TO service_role
  USING (true) WITH CHECK (true);

-- Authenticated users can read
CREATE POLICY "Authenticated read system_settings"
  ON system_settings FOR SELECT TO authenticated
  USING (true);

CREATE POLICY "Authenticated read intelligence_adjustments"
  ON intelligence_adjustments FOR SELECT TO authenticated
  USING (true);
COMMENT ON TABLE intelligence_adjustments IS 'Logs automatic threshold adjustments made by the self-improvement system';
