B2BCall-dialer/packages/database/prisma/migrations/20260828151027_plans_campaigns_leads/migration.sql

-- CreateEnum
CREATE TYPE "campaign_status" AS ENUM ('DRAFT', 'READY', 'WAITING_SCHEDULE', 'RUNNING', 'PAUSED', 'DRAINING', 'STOPPED', 'COMPLETED', 'ERROR');

-- CreateEnum
CREATE TYPE "lead_status" AS ENUM ('NEW', 'READY', 'RESERVED', 'ORIGINATING', 'RINGING', 'ANSWERED', 'QUEUEING', 'CONNECTED_AGENT', 'BUSY', 'NO_ANSWER', 'FAILED', 'VOICEMAIL', 'CALLBACK', 'COMPLETED', 'DO_NOT_CALL', 'MAX_ATTEMPTS');

-- CreateTable: plans (catalogo compartilhado entre tenants, nao e' tenant-scoped, sem RLS)
CREATE TABLE "plans" (
    "id" UUID NOT NULL,
    "key" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "max_extensions" INTEGER,
    "max_agents" INTEGER,
    "max_trunks" INTEGER,
    "max_queues" INTEGER,
    "max_campaigns" INTEGER,
    "max_cps" INTEGER,
    "max_concurrent_calls" INTEGER,
    "max_daily_calls" INTEGER,
    "max_monthly_calls" INTEGER,
    "max_recording_storage_gb" INTEGER,
    "recording_enabled" BOOLEAN NOT NULL DEFAULT true,
    "ai_enabled" BOOLEAN NOT NULL DEFAULT false,
    "ai_transcription_enabled" BOOLEAN NOT NULL DEFAULT false,
    "ai_analysis_enabled" BOOLEAN NOT NULL DEFAULT false,
    "api_access_enabled" BOOLEAN NOT NULL DEFAULT true,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "plans_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "plans_key_key" ON "plans"("key");

-- Seed: todo tenant precisa de um plano (agente.md secao 56-57: nunca
-- "if plan == PRO" espalhado, so' leitura de limites; null num campo =
-- sem limite). "trial" e' o plano padrao pra tenants existentes/novos ate'
-- a fase Billing introduzir um fluxo de escolha de plano de verdade.
INSERT INTO "plans" (
  "id", "key", "name",
  "max_extensions", "max_agents", "max_trunks", "max_queues", "max_campaigns",
  "max_cps", "max_concurrent_calls", "max_daily_calls", "max_monthly_calls",
  "max_recording_storage_gb",
  "recording_enabled", "ai_enabled", "ai_transcription_enabled", "ai_analysis_enabled", "api_access_enabled",
  "updated_at"
) VALUES (
  gen_random_uuid(), 'trial', 'Trial',
  5, 5, 2, 3, 2,
  3, 5, 200, 4000,
  1,
  true, false, false, false, true,
  CURRENT_TIMESTAMP
);

-- AlterTable: adiciona plan_id nullable primeiro (ha' tenants existentes),
-- faz o backfill, so' depois torna NOT NULL + FK.
ALTER TABLE "tenants" ADD COLUMN "plan_id" UUID;

UPDATE "tenants" SET "plan_id" = (SELECT "id" FROM "plans" WHERE "key" = 'trial');

ALTER TABLE "tenants" ALTER COLUMN "plan_id" SET NOT NULL;

-- AddForeignKey
ALTER TABLE "tenants" ADD CONSTRAINT "tenants_plan_id_fkey" FOREIGN KEY ("plan_id") REFERENCES "plans"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- CreateTable
CREATE TABLE "campaigns" (
    "id" UUID NOT NULL,
    "tenant_id" UUID NOT NULL,
    "name" TEXT NOT NULL,
    "description" TEXT,
    "queue_id" UUID NOT NULL,
    "trunk_id" UUID NOT NULL,
    "caller_id_name" TEXT,
    "caller_id_number" TEXT,
    "timezone" TEXT NOT NULL DEFAULT 'America/Sao_Paulo',
    "start_date" DATE,
    "end_date" DATE,
    "days_of_week" INTEGER[],
    "start_time" TEXT,
    "end_time" TEXT,
    "max_cps" INTEGER,
    "max_concurrent_calls" INTEGER,
    "pacing_initial" DOUBLE PRECISION NOT NULL DEFAULT 1.0,
    "pacing_min" DOUBLE PRECISION NOT NULL DEFAULT 1.0,
    "pacing_max" DOUBLE PRECISION NOT NULL DEFAULT 3.0,
    "target_abandon_rate" DOUBLE PRECISION NOT NULL DEFAULT 0.03,
    "ring_timeout" INTEGER NOT NULL DEFAULT 30,
    "max_attempts" INTEGER NOT NULL DEFAULT 3,
    "recording_enabled" BOOLEAN NOT NULL DEFAULT false,
    "avmd_enabled" BOOLEAN NOT NULL DEFAULT false,
    "ai_transcription_enabled" BOOLEAN NOT NULL DEFAULT false,
    "ai_analysis_enabled" BOOLEAN NOT NULL DEFAULT false,
    "status" "campaign_status" NOT NULL DEFAULT 'DRAFT',
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,
    "deleted_at" TIMESTAMP(3),

    CONSTRAINT "campaigns_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "leads" (
    "id" UUID NOT NULL,
    "tenant_id" UUID NOT NULL,
    "campaign_id" UUID NOT NULL,
    "name" TEXT,
    "phone_original" TEXT NOT NULL,
    "phone_normalized" TEXT NOT NULL,
    "status" "lead_status" NOT NULL DEFAULT 'NEW',
    "attempt_count" INTEGER NOT NULL DEFAULT 0,
    "last_attempt_at" TIMESTAMP(3),
    "next_attempt_at" TIMESTAMP(3),
    "last_result" TEXT,
    "custom_fields" JSONB NOT NULL DEFAULT '{}',
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "leads_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "suppression_entries" (
    "id" UUID NOT NULL,
    "tenant_id" UUID NOT NULL,
    "phone_normalized" TEXT NOT NULL,
    "reason" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "suppression_entries_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE INDEX "campaigns_tenant_id_idx" ON "campaigns"("tenant_id");

-- CreateIndex
CREATE UNIQUE INDEX "campaigns_tenant_id_name_key" ON "campaigns"("tenant_id", "name");

-- CreateIndex
CREATE INDEX "leads_tenant_id_idx" ON "leads"("tenant_id");

-- CreateIndex
CREATE INDEX "leads_campaign_id_status_next_attempt_at_idx" ON "leads"("campaign_id", "status", "next_attempt_at");

-- CreateIndex
CREATE UNIQUE INDEX "leads_campaign_id_phone_normalized_key" ON "leads"("campaign_id", "phone_normalized");

-- CreateIndex
CREATE INDEX "suppression_entries_tenant_id_idx" ON "suppression_entries"("tenant_id");

-- CreateIndex
CREATE UNIQUE INDEX "suppression_entries_tenant_id_phone_normalized_key" ON "suppression_entries"("tenant_id", "phone_normalized");

-- AddForeignKey
ALTER TABLE "campaigns" ADD CONSTRAINT "campaigns_tenant_id_fkey" FOREIGN KEY ("tenant_id") REFERENCES "tenants"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "campaigns" ADD CONSTRAINT "campaigns_queue_id_fkey" FOREIGN KEY ("queue_id") REFERENCES "queues"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "campaigns" ADD CONSTRAINT "campaigns_trunk_id_fkey" FOREIGN KEY ("trunk_id") REFERENCES "trunks"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "leads" ADD CONSTRAINT "leads_tenant_id_fkey" FOREIGN KEY ("tenant_id") REFERENCES "tenants"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "leads" ADD CONSTRAINT "leads_campaign_id_fkey" FOREIGN KEY ("campaign_id") REFERENCES "campaigns"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "suppression_entries" ADD CONSTRAINT "suppression_entries_tenant_id_fkey" FOREIGN KEY ("tenant_id") REFERENCES "tenants"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- Tabelas de negocio tenant-scoped: RLS obrigatorio em todas (ver docs/TENANT_ISOLATION.md).
-- "plans" e' um catalogo compartilhado entre tenants (nao tem tenant_id), sem RLS.
ALTER TABLE "campaigns" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "campaigns" FORCE ROW LEVEL SECURITY;
CREATE POLICY "tenant_isolation" ON "campaigns"
  USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);

ALTER TABLE "leads" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "leads" FORCE ROW LEVEL SECURITY;
CREATE POLICY "tenant_isolation" ON "leads"
  USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);

ALTER TABLE "suppression_entries" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "suppression_entries" FORCE ROW LEVEL SECURITY;
CREATE POLICY "tenant_isolation" ON "suppression_entries"
  USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);