-- Ideation feature: shared ideas table for cross-tenant idea submissions
CREATE TABLE IF NOT EXISTS shared.ideas (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  org_id UUID NOT NULL REFERENCES shared.organizations(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES shared.users(id) ON DELETE CASCADE,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  status VARCHAR(20) NOT NULL DEFAULT 'new',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_ideas_org_id ON shared.ideas(org_id);
CREATE INDEX IF NOT EXISTS idx_ideas_status ON shared.ideas(status);
CREATE INDEX IF NOT EXISTS idx_ideas_created_at ON shared.ideas(created_at DESC);