-- Automation scheduling and execution logs
CREATE TABLE IF NOT EXISTS automation_jobs (
  id                  BIGSERIAL PRIMARY KEY,
  property_id         VARCHAR(128) NOT NULL,
  schedule            TEXT NOT NULL,
  status              VARCHAR(32) NOT NULL DEFAULT 'active',
  next_run_at         TIMESTAMPTZ,
  metadata            JSONB,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_automation_jobs_property_status
  ON automation_jobs (property_id, status);

CREATE INDEX IF NOT EXISTS idx_automation_jobs_next_run
  ON automation_jobs (next_run_at);

CREATE TABLE IF NOT EXISTS automation_executions (
  id             BIGSERIAL PRIMARY KEY,
  job_id         BIGINT NOT NULL REFERENCES automation_jobs(id) ON DELETE CASCADE,
  run_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  status         VARCHAR(32) NOT NULL,
  stats          JSONB,
  error          TEXT,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_automation_exec_job_status
  ON automation_executions (job_id, status);

CREATE INDEX IF NOT EXISTS idx_automation_exec_run_at
  ON automation_executions (run_at);

