-- 049 — Camada de Receita F1: destravar a Prova de Incremento.
--
-- Ingestão de vendas (integrações + conversões canônicas de 7 campos com
-- idempotência por orderRef), importação com diagnóstico, kanban de handoff
-- (colunas em ai_conversations — a conversa É o card), grupo de controle
-- congelado (trava em todos os caminhos de envio) e rodadas de incremento.
-- Índice parcial único do controle vive em migrations/always/ensure_indexes.sql.
-- (Aplicada em dev via db:push em 28/07/2026; este arquivo leva a prod.)

CREATE TABLE IF NOT EXISTS sales_integrations (
  id varchar PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_user_id varchar NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  adapter_type varchar(30) NOT NULL,        -- csv_recorrente | totvs_moda
  nome varchar(100) NOT NULL,
  credentials_enc text,                     -- AES-256-GCM, WRITE-ONLY (decisão 35)
  config jsonb,                             -- TOTVS: operacoesVenda obrigatória (decisão 37)
  status varchar(20) NOT NULL DEFAULT 'ativa',
  last_sync_at timestamptz,
  last_error text,
  total_read integer NOT NULL DEFAULT 0,
  total_matched integer NOT NULL DEFAULT 0,
  total_discarded integer NOT NULL DEFAULT 0,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE TABLE IF NOT EXISTS conversions (
  id varchar PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_user_id varchar NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  contact_id varchar NOT NULL REFERENCES global_contacts(id) ON DELETE CASCADE,
  integration_id varchar REFERENCES sales_integrations(id) ON DELETE SET NULL,
  order_ref varchar(100) NOT NULL,
  occurred_at timestamptz NOT NULL,
  total_value numeric(12,2) NOT NULL,
  status varchar(20) NOT NULL DEFAULT 'concluida', -- concluida | cancelada | devolvida
  items_summary jsonb,
  store_channel varchar(20),
  source_type varchar(20) NOT NULL,         -- adapter_csv | totvs_moda | kanban | payment_link | api
  blind_to_channel boolean NOT NULL DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now(),
  CONSTRAINT conversions_owner_source_order_uniq UNIQUE (owner_user_id, source_type, order_ref)
);
CREATE INDEX IF NOT EXISTS idx_conversions_contact ON conversions (contact_id);
CREATE INDEX IF NOT EXISTS idx_conversions_owner_occurred ON conversions (owner_user_id, occurred_at);

CREATE TABLE IF NOT EXISTS sales_sync_runs (
  id varchar PRIMARY KEY DEFAULT gen_random_uuid(),
  integration_id varchar NOT NULL REFERENCES sales_integrations(id) ON DELETE CASCADE,
  window_start timestamptz,
  window_end timestamptz,
  read integer NOT NULL DEFAULT 0,
  matched integer NOT NULL DEFAULT 0,
  discarded integer NOT NULL DEFAULT 0,
  errors integer NOT NULL DEFAULT 0,
  detail text,
  created_at timestamptz DEFAULT now()
);

CREATE TABLE IF NOT EXISTS import_batches (
  id varchar PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_user_id varchar NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  declared_origin varchar(30) NOT NULL,     -- obrigatória (decisão 20)
  declared_origin_detail varchar(300),
  file_name varchar(200),
  total_rows integer NOT NULL DEFAULT 0,
  invalid_phones integer NOT NULL DEFAULT 0,
  duplicates_merged integer NOT NULL DEFAULT 0,
  already_opted_out integer NOT NULL DEFAULT 0,
  cpf_discarded integer NOT NULL DEFAULT 0,
  valid_contacts integer NOT NULL DEFAULT 0,
  with_purchase_history integer NOT NULL DEFAULT 0,
  without_consent integer NOT NULL DEFAULT 0,
  status varchar(24) NOT NULL DEFAULT 'processada', -- processada | bloqueada_suspeita
  created_at timestamptz DEFAULT now()
);

CREATE TABLE IF NOT EXISTS control_group_memberships (
  id varchar PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_user_id varchar NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  contact_id varchar NOT NULL REFERENCES global_contacts(id) ON DELETE CASCADE,
  phone_number varchar(20) NOT NULL,        -- desnormalizado p/ trava O(1)
  period_start timestamptz NOT NULL,
  period_end timestamptz NOT NULL,
  status varchar(20) NOT NULL DEFAULT 'ativo',
  drawn_at timestamptz DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_control_group_owner_status ON control_group_memberships (owner_user_id, status);
CREATE INDEX IF NOT EXISTS idx_control_group_owner_phone ON control_group_memberships (owner_user_id, phone_number);

CREATE TABLE IF NOT EXISTS incrementality_runs (
  id varchar PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_user_id varchar NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  period_start timestamptz NOT NULL,
  period_end timestamptz NOT NULL,
  declared_source varchar(20) NOT NULL,     -- integracao | planilha_dia30 (declarada ANTES)
  declared_integration_id varchar,
  min_contacts_ok boolean NOT NULL DEFAULT false,
  min_conversions_ok boolean NOT NULL DEFAULT false,
  result_kind varchar(15),                  -- preciso | direcional
  treatment_contacts integer,
  control_contacts integer,
  treatment_revenue numeric(14,2),
  control_revenue numeric(14,2),
  treatment_per_contact numeric(12,4),
  control_per_contact numeric(12,4),
  incremental_revenue numeric(14,2),
  returns_adjusted numeric(14,2),
  status varchar(20) NOT NULL DEFAULT 'concluida',
  created_at timestamptz DEFAULT now()
);

-- Kanban de handoff: a conversa É o card (§4.7).
ALTER TABLE ai_conversations ADD COLUMN IF NOT EXISTS kanban_column varchar(20);
ALTER TABLE ai_conversations ADD COLUMN IF NOT EXISTS won_value numeric(12,2);
ALTER TABLE ai_conversations ADD COLUMN IF NOT EXISTS lost_reason varchar(200);
ALTER TABLE ai_conversations ADD COLUMN IF NOT EXISTS kanban_moved_at timestamptz;
ALTER TABLE ai_conversations ADD COLUMN IF NOT EXISTS global_contact_id varchar;