CREATE TABLE IF NOT EXISTS intake_calls (
  id BIGSERIAL PRIMARY KEY,
  twilio_call_sid TEXT NOT NULL UNIQUE,
  from_number TEXT,
  to_number TEXT,
  caller_name TEXT,
  preferred_language TEXT,
  reason_summary TEXT,
  urgency TEXT,
  additional_notes TEXT,
  transcript_text TEXT,
  conversation_flow JSONB NOT NULL DEFAULT '[]'::jsonb,
  email_status TEXT NOT NULL DEFAULT 'skipped',
  email_error TEXT,
  call_status TEXT NOT NULL DEFAULT 'initiated',
  emailed_at TIMESTAMPTZ,
  metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS call_debug_events (
  id BIGSERIAL PRIMARY KEY,
  twilio_call_sid TEXT NOT NULL REFERENCES intake_calls(twilio_call_sid) ON DELETE CASCADE,
  stage TEXT NOT NULL,
  event_type TEXT NOT NULL,
  message TEXT,
  payload JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS call_transcript_events (
  id BIGSERIAL PRIMARY KEY,
  twilio_call_sid TEXT NOT NULL REFERENCES intake_calls(twilio_call_sid) ON DELETE CASCADE,
  sequence_number INTEGER NOT NULL,
  role TEXT NOT NULL,
  kind TEXT,
  text TEXT NOT NULL,
  source_event_type TEXT NOT NULL,
  captured_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (twilio_call_sid, sequence_number)
);

CREATE INDEX IF NOT EXISTS intake_calls_status_idx ON intake_calls (call_status, created_at DESC);
CREATE INDEX IF NOT EXISTS call_debug_events_sid_idx ON call_debug_events (twilio_call_sid, created_at DESC);
CREATE INDEX IF NOT EXISTS call_transcript_events_sid_idx ON call_transcript_events (twilio_call_sid, sequence_number ASC);
