-- 053 — F2: CAPI/Enhanced Conversions + estrutura de link de pagamento.
--
-- ad_platform_connections: credenciais Meta CAPI/Google cifradas WRITE-ONLY.
-- conversion_export_queue: cada conversão concluída → 1 item por conexão
-- ativa; dedupe UNIQUE(connection, conversion) e event_id = conversion.id.
-- payment_*: estrutura pronta; gateways EM ABERTO (decisão do dono).

CREATE TABLE IF NOT EXISTS ad_platform_connections (
  id varchar PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_user_id varchar NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  platform varchar(10) NOT NULL,            -- meta | google
  nome varchar(100) NOT NULL,
  credentials_enc text NOT NULL,            -- AES-256-GCM, write-only
  config jsonb,
  status varchar(20) NOT NULL DEFAULT 'ativa',
  last_error text,
  total_queued integer NOT NULL DEFAULT 0,
  total_sent integer NOT NULL DEFAULT 0,
  total_failed integer NOT NULL DEFAULT 0,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE TABLE IF NOT EXISTS conversion_export_queue (
  id bigserial PRIMARY KEY,
  connection_id varchar NOT NULL REFERENCES ad_platform_connections(id) ON DELETE CASCADE,
  conversion_id varchar NOT NULL REFERENCES conversions(id) ON DELETE CASCADE,
  status varchar(12) NOT NULL DEFAULT 'pending',
  attempt_count integer NOT NULL DEFAULT 0,
  last_error text,
  created_at timestamptz DEFAULT now(),
  processed_at timestamptz,
  CONSTRAINT conversion_export_conn_conv_uniq UNIQUE (connection_id, conversion_id)
);
CREATE INDEX IF NOT EXISTS idx_conversion_export_status ON conversion_export_queue (status, id);

CREATE TABLE IF NOT EXISTS payment_gateway_connections (
  id varchar PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_user_id varchar NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  gateway varchar(30) NOT NULL,
  nome varchar(100) NOT NULL,
  credentials_enc text NOT NULL,
  config jsonb,
  status varchar(20) NOT NULL DEFAULT 'ativa',
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE TABLE IF NOT EXISTS payment_links (
  id varchar PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_user_id varchar NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  gateway_connection_id varchar REFERENCES payment_gateway_connections(id) ON DELETE SET NULL,
  contact_id varchar REFERENCES global_contacts(id) ON DELETE SET NULL,
  conversation_id varchar,
  amount numeric(12,2) NOT NULL,
  description varchar(200),
  external_id varchar,
  url text,
  status varchar(15) NOT NULL DEFAULT 'gerado', -- gerado | pago | expirado | cancelado
  paid_at timestamptz,
  expires_at timestamptz,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_payment_links_owner_status ON payment_links (owner_user_id, status);