-- Job queue for long-running operations (feed sync, AI regeneration, bulk imports)
-- Worker process polls for pending jobs

CREATE TABLE IF NOT EXISTS job_queue (
  id           BIGSERIAL PRIMARY KEY,
  type         TEXT NOT NULL,
  payload      JSONB NOT NULL DEFAULT '{}',
  status       TEXT NOT NULL DEFAULT 'pending',
  priority     SMALLINT NOT NULL DEFAULT 0,
  attempts     SMALLINT NOT NULL DEFAULT 0,
  max_attempts SMALLINT NOT NULL DEFAULT 3,
  progress     SMALLINT DEFAULT 0,
  log          TEXT,
  result       JSONB,
  error        TEXT,
  actor_email  TEXT,
  scheduled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  started_at   TIMESTAMPTZ,
  finished_at  TIMESTAMPTZ,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_jobs_status ON job_queue (status, scheduled_at) WHERE status IN ('pending', 'running');
CREATE INDEX IF NOT EXISTS idx_jobs_type ON job_queue (type, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_jobs_created ON job_queue (created_at DESC);

-- Trigger to update updated_at
CREATE OR REPLACE FUNCTION update_job_queue_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_job_queue_updated_at ON job_queue;
CREATE TRIGGER trg_job_queue_updated_at
  BEFORE UPDATE ON job_queue
  FOR EACH ROW EXECUTE FUNCTION update_job_queue_updated_at();
