-- Queue for deferred RAG embedding generation (nightly batch processing)
CREATE TABLE IF NOT EXISTS embedding_queue (
  id SERIAL PRIMARY KEY,
  organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  entity_type VARCHAR(20) NOT NULL CHECK (entity_type IN ('form', 'task', 'task_message')),
  entity_id INTEGER NOT NULL,
  operation VARCHAR(20) NOT NULL DEFAULT 'upsert' CHECK (operation IN ('upsert', 'delete')),
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  processed_at TIMESTAMP
);

-- Only one pending operation per entity (deduplication)
CREATE UNIQUE INDEX IF NOT EXISTS embedding_queue_pending_entity_idx
  ON embedding_queue(organization_id, entity_type, entity_id)
  WHERE processed_at IS NULL;

CREATE INDEX IF NOT EXISTS embedding_queue_processed_at_idx
  ON embedding_queue(processed_at) WHERE processed_at IS NULL;

CREATE INDEX IF NOT EXISTS embedding_queue_created_at_idx
  ON embedding_queue(created_at);

-- Enable tenant isolation for embedding queue
ALTER TABLE embedding_queue ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_iso ON embedding_queue;
CREATE POLICY tenant_iso ON embedding_queue USING (
  current_setting('app.is_superadmin', true) = 'true'
  OR organization_id = NULLIF(current_setting('app.current_org_id', true), '')::int
);