-- Prompt Evolution Schema
-- Part of the 0.1% Self-Improvement Loop
-- Stores prompt versions, tracks performance, and manages A/B testing

-- Prompt versions table
CREATE TABLE IF NOT EXISTS prompt_versions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  prompt_type VARCHAR(100) NOT NULL,
  name VARCHAR(200),
  category VARCHAR(100) DEFAULT 'general',
  template TEXT NOT NULL,
  version VARCHAR(20) NOT NULL DEFAULT '1.0.0',
  is_active BOOLEAN DEFAULT false,

  -- Performance tracking
  usage_count INTEGER DEFAULT 0,
  success_count INTEGER DEFAULT 0,
  failure_count INTEGER DEFAULT 0,

  -- Evolution tracking
  evolution_reason TEXT,
  parent_version_id UUID REFERENCES prompt_versions(id),

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  activated_at TIMESTAMPTZ,
  last_used_at TIMESTAMPTZ,

  -- Unique constraint on active version per type
  CONSTRAINT unique_active_per_type UNIQUE (prompt_type, is_active)
    DEFERRABLE INITIALLY DEFERRED
);

-- Index for quick active prompt lookup
CREATE INDEX IF NOT EXISTS idx_prompt_versions_active
  ON prompt_versions(prompt_type, is_active)
  WHERE is_active = true;

-- Index for performance queries
CREATE INDEX IF NOT EXISTS idx_prompt_versions_type_created
  ON prompt_versions(prompt_type, created_at DESC);

-- Prompt outcomes table (tracks each usage outcome)
CREATE TABLE IF NOT EXISTS prompt_outcomes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  prompt_version_id UUID REFERENCES prompt_versions(id) ON DELETE CASCADE,
  suggestion_id UUID REFERENCES intelligence_suggestions(id) ON DELETE SET NULL,
  success BOOLEAN NOT NULL,
  feedback JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for analyzing prompt performance
CREATE INDEX IF NOT EXISTS idx_prompt_outcomes_version
  ON prompt_outcomes(prompt_version_id, success);

CREATE INDEX IF NOT EXISTS idx_prompt_outcomes_created
  ON prompt_outcomes(created_at DESC);

-- Enable RLS
ALTER TABLE prompt_versions ENABLE ROW LEVEL SECURITY;
ALTER TABLE prompt_outcomes ENABLE ROW LEVEL SECURITY;

-- Service role can do everything
CREATE POLICY prompt_versions_service_all ON prompt_versions
  FOR ALL TO service_role USING (true) WITH CHECK (true);

CREATE POLICY prompt_outcomes_service_all ON prompt_outcomes
  FOR ALL TO service_role USING (true) WITH CHECK (true);

-- Authenticated users can read prompt versions
CREATE POLICY prompt_versions_auth_read ON prompt_versions
  FOR SELECT TO authenticated USING (true);

-- Function to safely increment usage count (avoids race conditions)
CREATE OR REPLACE FUNCTION increment_prompt_usage(version_id UUID)
RETURNS void AS $$
BEGIN
  UPDATE prompt_versions
  SET
    usage_count = usage_count + 1,
    last_used_at = NOW()
  WHERE id = version_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to record prompt outcome
CREATE OR REPLACE FUNCTION record_prompt_outcome(
  version_id UUID,
  was_success BOOLEAN,
  suggestion_uuid UUID DEFAULT NULL,
  feedback_json JSONB DEFAULT '{}'
)
RETURNS void AS $$
BEGIN
  -- Update version stats
  IF was_success THEN
    UPDATE prompt_versions
    SET success_count = success_count + 1, updated_at = NOW()
    WHERE id = version_id;
  ELSE
    UPDATE prompt_versions
    SET failure_count = failure_count + 1, updated_at = NOW()
    WHERE id = version_id;
  END IF;

  -- Insert outcome record
  INSERT INTO prompt_outcomes (prompt_version_id, suggestion_id, success, feedback)
  VALUES (version_id, suggestion_uuid, was_success, feedback_json);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Grant execute on functions
GRANT EXECUTE ON FUNCTION increment_prompt_usage TO authenticated;
GRANT EXECUTE ON FUNCTION increment_prompt_usage TO service_role;
GRANT EXECUTE ON FUNCTION record_prompt_outcome TO authenticated;
GRANT EXECUTE ON FUNCTION record_prompt_outcome TO service_role;

-- Add comment explaining the system
COMMENT ON TABLE prompt_versions IS 'Stores AI prompt versions for the self-improvement loop. Tracks performance metrics to enable automatic prompt evolution.';
COMMENT ON TABLE prompt_outcomes IS 'Records individual outcomes of prompt usage for detailed performance analysis.';
