-- ============================================================
-- Migration 0001: RCS tables — Task #211
-- Idempotent: uses IF NOT EXISTS / ADD COLUMN IF NOT EXISTS
-- ============================================================

-- Config RCS por serviço (credenciais MKOM/MKChannels)
CREATE TABLE IF NOT EXISTS rcs_configs (
  id            VARCHAR PRIMARY KEY,
  service_id    VARCHAR NOT NULL,
  api_token     VARCHAR NOT NULL,
  base_url      VARCHAR NOT NULL DEFAULT 'https://sms.mkmservice.com',
  cost_centre_id VARCHAR NOT NULL DEFAULT '',
  ativo         BOOLEAN NOT NULL DEFAULT TRUE,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- Senders/agentes RCS por serviço
-- status: PENDING_APPROVAL | APPROVED | INACTIVE
CREATE TABLE IF NOT EXISTS rcs_senders (
  id            VARCHAR PRIMARY KEY,
  service_id    VARCHAR NOT NULL,
  user_id       VARCHAR NOT NULL,
  agent_id      VARCHAR NOT NULL,
  display_name  VARCHAR,
  status        VARCHAR NOT NULL DEFAULT 'PENDING_APPROVAL',
  ativo         BOOLEAN NOT NULL DEFAULT TRUE,
  preferred     BOOLEAN NOT NULL DEFAULT FALSE,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- Mensagens RCS individuais (uma por contato por campanha)
CREATE TABLE IF NOT EXISTS rcs_messages (
  id               VARCHAR PRIMARY KEY,
  service_id       VARCHAR NOT NULL,
  campaign_id      VARCHAR NOT NULL,
  sender_id        VARCHAR NOT NULL,
  mkom_internal_id VARCHAR,
  reference_id     VARCHAR,
  phone            VARCHAR NOT NULL,
  rcs_type         VARCHAR NOT NULL DEFAULT 'basic',
  content_snapshot JSONB,
  status           VARCHAR NOT NULL DEFAULT 'PENDING',
  error_message    TEXT,
  owner_user_id    VARCHAR,
  sent_at          TIMESTAMPTZ DEFAULT NOW(),
  updated_at       TIMESTAMPTZ DEFAULT NOW()
);

-- Coluna RCS na tabela de serviços (subtipo padrão do serviço)
ALTER TABLE services ADD COLUMN IF NOT EXISTS rcs_subtype VARCHAR(20);

-- Colunas RCS na tabela de campanhas
ALTER TABLE campaigns ADD COLUMN IF NOT EXISTS rcs_subtype    VARCHAR(20);
ALTER TABLE campaigns ADD COLUMN IF NOT EXISTS rcs_media_url  TEXT;
ALTER TABLE campaigns ADD COLUMN IF NOT EXISTS rcs_suggestions TEXT;
ALTER TABLE campaigns ADD COLUMN IF NOT EXISTS rcs_cards       TEXT;

-- Índices úteis para lookups frequentes
CREATE INDEX IF NOT EXISTS idx_rcs_messages_mkom_id     ON rcs_messages(mkom_internal_id);
CREATE INDEX IF NOT EXISTS idx_rcs_messages_reference_id ON rcs_messages(reference_id);
CREATE INDEX IF NOT EXISTS idx_rcs_messages_campaign_id  ON rcs_messages(campaign_id);
CREATE INDEX IF NOT EXISTS idx_rcs_senders_service_id    ON rcs_senders(service_id);
CREATE INDEX IF NOT EXISTS idx_rcs_configs_service_id    ON rcs_configs(service_id);