-- RCS MKOM v2 — payload oficial, sender exclusivo por cliente, fallback SMS,
-- webhooks por token na URL e registro de respostas (MO).

-- rcs_configs: token dos webhooks (URL passada ao suporte MKOM)
ALTER TABLE rcs_configs ADD COLUMN IF NOT EXISTS webhook_token varchar;
UPDATE rcs_configs SET webhook_token = replace(gen_random_uuid()::text, '-', '') WHERE webhook_token IS NULL;

-- rcs_senders: rcs_sender_id numérico da MKOM + regra "um sender por cliente"
ALTER TABLE rcs_senders ADD COLUMN IF NOT EXISTS mkom_sender_id varchar;
-- Dados atuais são de teste: remove duplicatas antes do índice único (mantém o mais recente)
DELETE FROM rcs_senders a USING rcs_senders b
  WHERE a.service_id = b.service_id AND a.user_id = b.user_id
    AND a.created_at < b.created_at;
CREATE UNIQUE INDEX IF NOT EXISTS rcs_senders_service_user_uniq
  ON rcs_senders (service_id, user_id);

-- campaigns: fallback SMS opcional
ALTER TABLE campaigns ADD COLUMN IF NOT EXISTS rcs_fallback_enabled boolean NOT NULL DEFAULT false;
ALTER TABLE campaigns ADD COLUMN IF NOT EXISTS rcs_fallback_text text;

-- rcs_messages: id do lote (mailing.id) + lookup de webhook por reference
ALTER TABLE rcs_messages ADD COLUMN IF NOT EXISTS mkom_mailing_id varchar;
CREATE INDEX IF NOT EXISTS rcs_messages_reference_id_idx ON rcs_messages (reference_id);

-- Respostas recebidas (webhook /webhooks/rcs/reply/:token)
CREATE TABLE IF NOT EXISTS rcs_replies (
  id varchar PRIMARY KEY DEFAULT gen_random_uuid(),
  service_id varchar REFERENCES services(id) ON DELETE CASCADE,
  campaign_id varchar REFERENCES campaigns(id),
  rcs_message_id varchar REFERENCES rcs_messages(id) ON DELETE SET NULL,
  phone varchar NOT NULL,
  message text NOT NULL,
  mkom_internal_id varchar,
  reference_id varchar,
  owner_user_id varchar,
  received_at timestamptz DEFAULT now()
);
CREATE INDEX IF NOT EXISTS rcs_replies_campaign_id_idx ON rcs_replies (campaign_id);