-- =====================================================
-- FollowUps Table - Migrations no Formato Correto
-- =====================================================

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
);

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");

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

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();
