-- Garante que a tabela mensagens_whatsapp exista e tenha as colunas corretas.

CREATE TABLE IF NOT EXISTS public.mensagens_whatsapp (
    id uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    telefone_cliente character varying,
    cliente_nome character varying,
    mensagem text,
    tipo character varying DEFAULT 'enviada'::character varying,
    status character varying DEFAULT 'enviada'::character varying,
    evolution_message_id character varying,
    venda_id uuid,
    cobranca_id uuid
);

-- Adiciona colunas que podem estar faltando, sem gerar erro se já existirem.

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'public.mensagens_whatsapp'::regclass AND attname = 'venda_id') THEN
        ALTER TABLE public.mensagens_whatsapp ADD COLUMN venda_id uuid;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'public.mensagens_whatsapp'::regclass AND attname = 'cobranca_id') THEN
        ALTER TABLE public.mensagens_whatsapp ADD COLUMN cobranca_id uuid;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'public.mensagens_whatsapp'::regclass AND attname = 'cliente_nome') THEN
        ALTER TABLE public.mensagens_whatsapp ADD COLUMN cliente_nome character varying;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'public.mensagens_whatsapp'::regclass AND attname = 'evolution_message_id') THEN
        ALTER TABLE public.mensagens_whatsapp ADD COLUMN evolution_message_id character varying;
    END IF;
END;
$$;

-- Adiciona relacionamento com a tabela de vendas, se não existir.
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'mensagens_whatsapp_venda_id_fkey') THEN
        ALTER TABLE public.mensagens_whatsapp ADD CONSTRAINT mensagens_whatsapp_venda_id_fkey FOREIGN KEY (venda_id) REFERENCES public.vendas(id) ON DELETE SET NULL;
    END IF;
END;
$$;

COMMENT ON TABLE public.mensagens_whatsapp IS 'Armazena o histórico de mensagens enviadas e recebidas pelo WhatsApp.';