-- Dev Queue: Bugs and Feature Requests
-- Stores items that get mirrored to Slack for visibility

CREATE TABLE IF NOT EXISTS dev_queue (
  id SERIAL PRIMARY KEY,
  type TEXT NOT NULL CHECK (type IN ('bug', 'feature')),
  status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'in_progress', 'ready_to_test', 'testing', 'closed')),

  -- The item details
  title TEXT NOT NULL,
  description TEXT,
  page_url TEXT,

  -- For test bugs linked to features
  parent_feature_id INTEGER REFERENCES dev_queue(id),

  -- Tracking
  reported_by TEXT,
  reported_at TIMESTAMPTZ DEFAULT NOW(),

  -- Priority (1=highest)
  priority INTEGER DEFAULT 5,

  -- Device/context info
  device_info TEXT,
  console_errors TEXT,

  -- When CC works on it
  started_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,

  -- Notes from CC
  cc_notes TEXT,

  -- Metadata for flexibility
  metadata JSONB DEFAULT '{}'::jsonb
);

-- Index for quick filtering
CREATE INDEX IF NOT EXISTS idx_dev_queue_type_status ON dev_queue(type, status);
CREATE INDEX IF NOT EXISTS idx_dev_queue_priority ON dev_queue(priority, reported_at);

-- Comments
COMMENT ON TABLE dev_queue IS 'Unified queue for bugs and feature requests - mirrored to Slack';
COMMENT ON COLUMN dev_queue.parent_feature_id IS 'For TEST-BUG type: links to the feature being tested';
