-- =====================================================
-- FollowUps Table - SQL Completo para Criação Manual
-- =====================================================

-- 1. Create FollowUps table
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. Add foreign key constraints
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;

-- 3. Add performance indexes
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");

-- 4. Composite index for common queries (performance optimization)
CREATE INDEX "FollowUps_company_status_scheduled_idx" ON "FollowUps" ("companyId", "status", "scheduledAt");

-- 5. Configure sequence ownership (PostgreSQL specific)
ALTER TABLE "FollowUps_id_seq" 
OWNED BY "FollowUps"."id";

-- 6. Add trigger for automatic updatedAt (optional)
CREATE OR REPLACE FUNCTION update_follow_ups_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW."updatedAt" = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_follow_ups_updated_at 
BEFORE UPDATE ON "FollowUps" 
FOR EACH ROW EXECUTE FUNCTION update_follow_ups_updated_at();

-- =====================================================
-- Estrutura da Tabela:
-- =====================================================
-- id: SERIAL PRIMARY KEY - Chave primária auto-incremento
-- ticketId: INTEGER NOT NULL - FK para tabela Tickets
-- companyId: INTEGER NOT NULL - FK para tabela Companies  
-- flowNodeId: VARCHAR - ID do nó no Flow Builder (opcional)
-- actionType: VARCHAR NOT NULL - Tipo de ação (sendMessageFlow, addTag, closeTicket, transferFlow)
-- actionPayload: JSON NOT NULL - Dados/configuração da ação
-- scheduledAt: TIMESTAMP NOT NULL - Data/hora agendada para execução
-- status: ENUM('pending', 'completed', 'failed', 'cancelled') - Status do follow-up
-- executedAt: TIMESTAMP - Data/hora real da execução (quando executado)
-- error: TEXT - Mensagem de erro (se falhar)
-- createdAt: TIMESTAMP - Data/hora de criação
-- updatedAt: TIMESTAMP - Data/hora da última atualização

-- =====================================================
-- Exemplos de INSERT:
-- =====================================================

-- Exemplo 1: Follow-up para enviar mensagem após 30 minutos
INSERT INTO "FollowUps" (
  "ticketId", 
  "companyId", 
  "flowNodeId", 
  "actionType", 
  "actionPayload", 
  "scheduledAt"
) VALUES (
  123, 
  1, 
  'node_abc123', 
  'sendMessageFlow', 
  '{"message": "Olá! Alguma novidade sobre sua solicitação?"}', 
  CURRENT_TIMESTAMP + INTERVAL '30 minutes'
);

-- Exemplo 2: Follow-up para adicionar tag após 1 hora
INSERT INTO "FollowUps" (
  "ticketId", 
  "companyId", 
  "actionType", 
  "actionPayload", 
  "scheduledAt"
) VALUES (
  456, 
  1, 
  'addTag', 
  '{"tagName": "Follow-up Realizado", "tagColor": "#ff9800"}', 
  CURRENT_TIMESTAMP + INTERVAL '1 hour'
);

-- =====================================================
-- Exemplos de SELECT:
-- =====================================================

-- Buscar todos os follow-ups pendentes de uma empresa
SELECT * FROM "FollowUps" 
WHERE "companyId" = 1 AND "status" = 'pending' 
ORDER BY "scheduledAt" ASC;

-- Buscar follow-ups que devem ser executados agora
SELECT * FROM "FollowUps" 
WHERE "status" = 'pending' 
AND "scheduledAt" <= CURRENT_TIMESTAMP;

-- Buscar follow-ups de um ticket específico
SELECT * FROM "FollowUps" 
WHERE "ticketId" = 123 
ORDER BY "createdAt" DESC;

-- =====================================================
-- Exemplos de UPDATE:
-- =====================================================

-- Marcar follow-up como executado com sucesso
UPDATE "FollowUps" 
SET "status" = 'completed', "executedAt" = CURRENT_TIMESTAMP 
WHERE "id" = 1;

-- Marcar follow-up como falho com erro
UPDATE "FollowUps" 
SET "status" = 'failed', "executedAt" = CURRENT_TIMESTAMP, "error" = 'Ticket não encontrado' 
WHERE "id" = 2;

-- Cancelar todos os follow-ups pendentes de um ticket
UPDATE "FollowUps" 
SET "status" = 'cancelled' 
WHERE "ticketId" = 123 AND "status" = 'pending';

-- =====================================================
-- Comandos Úteis:
-- =====================================================

-- Verificar estrutura da tabela
\d FollowUps

-- Verificar índices
\d FollowUps

-- Contar follow-ups por status
SELECT "status", COUNT(*) as count 
FROM "FollowUps" 
GROUP BY "status";

-- Limpar tabela (cuidado!)
-- TRUNCATE TABLE "FollowUps" CASCADE;

-- Remover tabela (cuidado!)
-- DROP TABLE "FollowUps" CASCADE;
