-- Self-Improvement System Schema
-- Tables for threshold management and learning

-- System settings table for storing configuration
CREATE TABLE IF NOT EXISTS system_settings (
  key VARCHAR(100) PRIMARY KEY,
  value JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 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);

-- Add status column to detected_friction if not exists
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'detected_friction' AND column_name = 'status'
  ) THEN
    ALTER TABLE detected_friction ADD COLUMN status VARCHAR(50) DEFAULT 'detected';
  END IF;
END $$;

-- Add resolved_at column to detected_friction if not exists
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'detected_friction' AND column_name = 'resolved_at'
  ) THEN
    ALTER TABLE detected_friction ADD COLUMN resolved_at TIMESTAMP WITH TIME ZONE;
  END IF;
END $$;

-- Add success_verdict column to intelligence_suggestions if not exists
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'intelligence_suggestions' AND column_name = 'success_verdict'
  ) THEN
    ALTER TABLE intelligence_suggestions ADD COLUMN success_verdict VARCHAR(50);
  END IF;
END $$;

-- Add measured_impact column to intelligence_suggestions if not exists
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'intelligence_suggestions' AND column_name = 'measured_impact'
  ) THEN
    ALTER TABLE intelligence_suggestions ADD COLUMN measured_impact DECIMAL(10,2);
  END IF;
END $$;

-- Insert default thresholds if not exists
INSERT INTO system_settings (key, value)
VALUES ('intelligence_thresholds', '{
  "rage_clicks_per_minute": 3,
  "errors_per_5min": 10,
  "abandonment_rate_spike": 50,
  "api_failures_per_5min": 5,
  "sms_failure_rate": 20,
  "slow_api_threshold_ms": 5000,
  "min_suggestion_confidence": 0.6,
  "ai_quality_target": 70
}'::jsonb)
ON CONFLICT (key) DO NOTHING;

COMMENT ON TABLE system_settings IS 'Stores system configuration including intelligence thresholds';
COMMENT ON TABLE intelligence_adjustments IS 'Logs automatic threshold adjustments made by the self-improvement system';
