-- RateRight Growth Engine - Ideas/Strategy Capture Schema
-- Voice-captured ideas, strategies, and reminders

CREATE TABLE IF NOT EXISTS ideas (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

  -- Content
  content TEXT NOT NULL,
  category VARCHAR(50) NOT NULL,  -- 'idea', 'strategy', 'experiment', 'reminder'

  -- Context
  user_email VARCHAR(255),
  lead_id UUID REFERENCES leads(id),  -- Optional: if idea relates to specific lead

  -- AI-detected metadata
  ai_detected_category BOOLEAN DEFAULT false,  -- Was category auto-detected?
  tags JSONB DEFAULT '[]',  -- AI-extracted keywords/topics

  -- Status tracking
  status VARCHAR(50) DEFAULT 'captured',  -- 'captured', 'reviewed', 'actioned', 'archived'
  priority VARCHAR(20),  -- 'high', 'medium', 'low'

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  actioned_at TIMESTAMPTZ,

  -- Notes
  notes TEXT  -- Follow-up notes after actioning
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_ideas_category ON ideas(category);
CREATE INDEX IF NOT EXISTS idx_ideas_status ON ideas(status);
CREATE INDEX IF NOT EXISTS idx_ideas_user ON ideas(user_email);
CREATE INDEX IF NOT EXISTS idx_ideas_created ON ideas(created_at DESC);
