-- 055 — F4: Motor de Rotinas interno + teto de frequência por contato.
--
-- routines: trigger = segmento (o segmento É o gatilho) ou evento; steps
-- LINEARES — sem grafo, por desenho. routine_executions: 1 rotina ativa
-- por contato (índice parcial único em migrations/always/). Teto semanal
-- por contato = regra de PLATAFORMA (contact_send_counters).

CREATE TABLE IF NOT EXISTS routines (
  id varchar PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_user_id varchar NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  nome varchar(100) NOT NULL,
  descricao varchar(300),
  seed_key varchar(30),
  ativo boolean NOT NULL DEFAULT false,
  trigger jsonb NOT NULL,
  steps jsonb NOT NULL,
  exit_on_click boolean NOT NULL DEFAULT true,
  cooldown_days integer NOT NULL DEFAULT 30,
  last_event_cursor bigint NOT NULL DEFAULT 0,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE TABLE IF NOT EXISTS routine_executions (
  id varchar PRIMARY KEY DEFAULT gen_random_uuid(),
  routine_id varchar NOT NULL REFERENCES routines(id) ON DELETE CASCADE,
  owner_user_id varchar NOT NULL,
  contact_id varchar NOT NULL REFERENCES global_contacts(id) ON DELETE CASCADE,
  phone_number varchar(20) NOT NULL,
  current_step integer NOT NULL DEFAULT 0,
  status varchar(20) NOT NULL DEFAULT 'ativa',
  next_action_at timestamptz NOT NULL,
  entered_at timestamptz DEFAULT now(),
  exited_at timestamptz,
  note varchar(300)
);
CREATE INDEX IF NOT EXISTS idx_routine_exec_due ON routine_executions (status, next_action_at);
CREATE INDEX IF NOT EXISTS idx_routine_exec_owner_contact ON routine_executions (owner_user_id, contact_id, status);
CREATE INDEX IF NOT EXISTS idx_routine_exec_routine ON routine_executions (routine_id, status);

CREATE TABLE IF NOT EXISTS contact_send_counters (
  id varchar PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_user_id varchar NOT NULL,
  contact_id varchar NOT NULL REFERENCES global_contacts(id) ON DELETE CASCADE,
  week_start varchar(10) NOT NULL,
  count integer NOT NULL DEFAULT 0,
  CONSTRAINT send_counters_owner_contact_week_uniq UNIQUE (owner_user_id, contact_id, week_start)
);
CREATE INDEX IF NOT EXISTS idx_send_counters_owner_week ON contact_send_counters (owner_user_id, week_start);