-- 003_commitments_phase2

CREATE TABLE IF NOT EXISTS commitments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  commitment_type TEXT NOT NULL CHECK (commitment_type IN ('check_in', 'study_followup', 'prayer', 'custom')),
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'paused', 'completed', 'canceled', 'failed')),
  source TEXT NOT NULL DEFAULT 'ai' CHECK (source IN ('ai', 'user_command', 'system')),
  summary_text TEXT NOT NULL,
  intent_raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  schedule_kind TEXT NOT NULL CHECK (schedule_kind IN ('one_off', 'recurring')),
  timezone TEXT NOT NULL DEFAULT 'America/New_York',
  scheduled_at_utc TIMESTAMPTZ,
  local_time TIME,
  recurrence_rule TEXT,
  next_run_at_utc TIMESTAMPTZ NOT NULL,
  last_run_at_utc TIMESTAMPTZ,
  run_count INT NOT NULL DEFAULT 0,
  idempotency_key TEXT NOT NULL,
  scheduler_job_id TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (
    (schedule_kind = 'one_off' AND scheduled_at_utc IS NOT NULL) OR
    (schedule_kind = 'recurring' AND local_time IS NOT NULL)
  )
);

CREATE TABLE IF NOT EXISTS commitment_events (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  commitment_id UUID NOT NULL REFERENCES commitments(id) ON DELETE CASCADE,
  event_type TEXT NOT NULL CHECK (event_type IN (
    'created',
    'scheduled',
    'triggered',
    'sent',
    'canceled',
    'tz_updated',
    'rescheduled',
    'failed'
  )),
  payload JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_commitments_user ON commitments(user_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_commitments_user_idempotency_active
  ON commitments(user_id, idempotency_key)
  WHERE status IN ('active', 'paused');
CREATE INDEX IF NOT EXISTS idx_commitments_user_status_next_run
  ON commitments(user_id, status, next_run_at_utc);
CREATE INDEX IF NOT EXISTS idx_commitments_next_run ON commitments(next_run_at_utc);
CREATE INDEX IF NOT EXISTS idx_commitment_events_commitment_created
  ON commitment_events(commitment_id, created_at DESC);
