-- =====================================================
-- FollowUps Table - SQL Simplificado (Sem Constraints Iniciais)
-- =====================================================

-- 1. Create table sem foreign keys primeiro
CREATE TABLE "FollowUps" (
  "id" SERIAL PRIMARY KEY,
  "ticketId" INTEGER NOT NULL,
  "companyId" INTEGER NOT NULL,
  "flowNodeId" VARCHAR,
  "actionType" VARCHAR NOT NULL,
  "actionPayload" JSON NOT NULL,
  "scheduledAt" TIMESTAMP NOT NULL,
  "status" VARCHAR CHECK ("status" IN ('pending', 'completed', 'failed', 'cancelled')) NOT NULL DEFAULT 'pending',
  "executedAt" TIMESTAMP,
  "error" TEXT,
  "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 2. Adicionar indexes básicos
CREATE INDEX "FollowUps_ticketId_idx" ON "FollowUps" ("ticketId");
CREATE INDEX "FollowUps_companyId_idx" ON "FollowUps" ("companyId");
CREATE INDEX "FollowUps_status_idx" ON "FollowUps" ("status");
CREATE INDEX "FollowUps_scheduledAt_idx" ON "FollowUps" ("scheduledAt");

-- 3. Adicionar foreign keys (se as tabelas existirem)
-- Descomente estas linhas APÓS verificar que as tabelas existem

-- ALTER TABLE "FollowUps" 
-- ADD CONSTRAINT "FollowUps_ticketId_fkey" 
-- FOREIGN KEY ("ticketId") REFERENCES "Tickets"("id") 
-- ON DELETE CASCADE ON UPDATE CASCADE;

-- ALTER TABLE "FollowUps" 
-- ADD CONSTRAINT "FollowUps_companyId_fkey" 
-- FOREIGN KEY ("companyId") REFERENCES "Companies"("id") 
-- ON DELETE CASCADE ON UPDATE CASCADE;

-- =====================================================
-- Verificação - Execute estes comandos para debug:
-- =====================================================

-- Verificar se a tabela foi criada
SELECT table_name, table_type 
FROM information_schema.tables 
WHERE table_schema = 'public' AND table_name = 'FollowUps';

-- Verificar estrutura da tabela
\d FollowUps

-- Verificar se as tabelas referenciadas existem
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public' 
AND table_name IN ('Tickets', 'Companies');

-- =====================================================
-- Teste de INSERT (após criar tabela)
-- =====================================================

-- INSERT simples para teste
INSERT INTO "FollowUps" (
  "ticketId", 
  "companyId", 
  "actionType", 
  "actionPayload", 
  "scheduledAt"
) VALUES (
  1, 
  1, 
  'sendMessageFlow', 
  '{"message": "Teste"}', 
  CURRENT_TIMESTAMP + INTERVAL '5 minutes'
);

-- Verificar se o INSERT funcionou
SELECT * FROM "FollowUps" ORDER BY "id" DESC LIMIT 1;
