-- Task #253: Evolução Completa de Gestão de Senders (API only)
-- Cria a tabela sender_assignments (atribuição de sender por cliente)
-- e adiciona a coluna sender_assignment_id em campaigns.
--
-- IDEMPOTENTE: usa CREATE TABLE IF NOT EXISTS e bloco PL/pgSQL para ALTER.

CREATE TABLE IF NOT EXISTS sender_assignments (
  id          TEXT        NOT NULL DEFAULT gen_random_uuid()::TEXT PRIMARY KEY,
  sender_id   TEXT        NOT NULL REFERENCES infobip_senders(id) ON DELETE CASCADE,
  user_id     TEXT        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  internal_name TEXT,
  category    TEXT,
  is_preferred BOOLEAN    NOT NULL DEFAULT FALSE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (sender_id, user_id)
);

-- Idempotente: garante DEFAULT em tabelas já criadas via db:push (sem default)
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'sender_assignments' AND column_name = 'id'
      AND column_default IS NULL
  ) THEN
    ALTER TABLE sender_assignments ALTER COLUMN id SET DEFAULT gen_random_uuid()::TEXT;
  END IF;
END
$$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'campaigns' AND column_name = 'sender_assignment_id'
  ) THEN
    ALTER TABLE campaigns ADD COLUMN sender_assignment_id TEXT;
  END IF;
END
$$;