-- CreateTable
CREATE TABLE "system_settings" (
    "key" VARCHAR(100) NOT NULL,
    "value" TEXT NOT NULL,
    "type" VARCHAR(20) NOT NULL DEFAULT 'string',
    "category" VARCHAR(50),
    "description" VARCHAR(255),
    "is_secret" BOOLEAN NOT NULL DEFAULT false,
    "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_by" INTEGER,

    CONSTRAINT "system_settings_pkey" PRIMARY KEY ("key")
);

-- CreateTable
CREATE TABLE "plans" (
    "id" SERIAL NOT NULL,
    "name" VARCHAR(100) NOT NULL,
    "slug" VARCHAR(50) NOT NULL,
    "description" TEXT,
    "price" DECIMAL(10,2) NOT NULL,
    "billing_cycle" VARCHAR(20) NOT NULL DEFAULT 'monthly',
    "period_days" INTEGER NOT NULL DEFAULT 30,
    "max_instances" INTEGER NOT NULL DEFAULT 1,
    "max_messages_month" INTEGER NOT NULL DEFAULT 1000,
    "max_messages_day" INTEGER NOT NULL DEFAULT 100,
    "max_contacts" INTEGER,
    "rate_limit_per_minute" INTEGER NOT NULL DEFAULT 30,
    "features" JSONB NOT NULL DEFAULT '{}',
    "is_active" BOOLEAN NOT NULL DEFAULT true,
    "is_featured" BOOLEAN NOT NULL DEFAULT false,
    "sort_order" INTEGER NOT NULL DEFAULT 0,
    "trial_days" INTEGER NOT NULL DEFAULT 0,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "clients" (
    "id" SERIAL NOT NULL,
    "name" VARCHAR(200) NOT NULL,
    "email" VARCHAR(200) NOT NULL,
    "password_hash" VARCHAR(255) NOT NULL,
    "phone" VARCHAR(20),
    "document" VARCHAR(20),
    "document_type" VARCHAR(10),
    "company_name" VARCHAR(200),
    "plan_id" INTEGER,
    "status" VARCHAR(20) NOT NULL DEFAULT 'pending',
    "pagarme_customer_id" VARCHAR(50),
    "pagarme_subscription_id" VARCHAR(50),
    "subscription_status" VARCHAR(20),
    "api_key" VARCHAR(64) NOT NULL,
    "api_key_test" VARCHAR(64),
    "webhook_url" VARCHAR(500),
    "webhook_secret" VARCHAR(64),
    "webhook_events" JSONB NOT NULL DEFAULT '["message.received", "message.status"]',
    "allowed_ips" JSONB NOT NULL DEFAULT '[]',
    "email_verified_at" TIMESTAMP(3),
    "trial_ends_at" TIMESTAMP(3),
    "subscription_ends_at" TIMESTAMP(3),
    "last_login_at" TIMESTAMP(3),
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "instances" (
    "id" SERIAL NOT NULL,
    "client_id" INTEGER NOT NULL,
    "name" VARCHAR(100) NOT NULL,
    "evolution_instance_name" VARCHAR(100) NOT NULL,
    "evolution_instance_id" VARCHAR(100),
    "phone_number" VARCHAR(20),
    "status" VARCHAR(20) NOT NULL DEFAULT 'pending',
    "messages_sent_today" INTEGER NOT NULL DEFAULT 0,
    "messages_sent_month" INTEGER NOT NULL DEFAULT 0,
    "last_message_at" TIMESTAMP(3),
    "connected_at" TIMESTAMP(3),
    "disconnected_at" TIMESTAMP(3),
    "disconnect_reason" VARCHAR(100),
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "messages" (
    "id" BIGSERIAL NOT NULL,
    "client_id" INTEGER NOT NULL,
    "instance_id" INTEGER NOT NULL,
    "to_number" VARCHAR(20) NOT NULL,
    "type" VARCHAR(20) NOT NULL,
    "content" TEXT,
    "file_name" VARCHAR(255),
    "caption" TEXT,
    "metadata" JSONB,
    "status" VARCHAR(20) NOT NULL DEFAULT 'queued',
    "evolution_message_id" VARCHAR(100),
    "error_message" TEXT,
    "source" VARCHAR(50) NOT NULL DEFAULT 'api',
    "source_id" VARCHAR(100),
    "source_ip" VARCHAR(45),
    "queued_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "sent_at" TIMESTAMP(3),
    "delivered_at" TIMESTAMP(3),
    "read_at" TIMESTAMP(3),
    "failed_at" TIMESTAMP(3),

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

-- CreateTable
CREATE TABLE "usage_daily" (
    "id" SERIAL NOT NULL,
    "client_id" INTEGER NOT NULL,
    "date" DATE NOT NULL,
    "messages_sent" INTEGER NOT NULL DEFAULT 0,
    "messages_failed" INTEGER NOT NULL DEFAULT 0,
    "api_calls" INTEGER NOT NULL DEFAULT 0,

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

-- CreateTable
CREATE TABLE "usage_monthly" (
    "id" SERIAL NOT NULL,
    "client_id" INTEGER NOT NULL,
    "year_month" CHAR(7) NOT NULL,
    "messages_sent" INTEGER NOT NULL DEFAULT 0,
    "messages_failed" INTEGER NOT NULL DEFAULT 0,
    "api_calls" INTEGER NOT NULL DEFAULT 0,

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

-- CreateTable
CREATE TABLE "contracts" (
    "id" SERIAL NOT NULL,
    "type" VARCHAR(20) NOT NULL,
    "version" VARCHAR(20) NOT NULL,
    "title" VARCHAR(200) NOT NULL,
    "content" TEXT NOT NULL,
    "is_active" BOOLEAN NOT NULL DEFAULT false,
    "requires_acceptance" BOOLEAN NOT NULL DEFAULT true,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "published_at" TIMESTAMP(3),

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

-- CreateTable
CREATE TABLE "client_contract_acceptances" (
    "id" SERIAL NOT NULL,
    "client_id" INTEGER NOT NULL,
    "contract_id" INTEGER NOT NULL,
    "accepted_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "ip_address" VARCHAR(45),
    "user_agent" TEXT,

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

-- CreateTable
CREATE TABLE "email_templates" (
    "id" SERIAL NOT NULL,
    "slug" VARCHAR(50) NOT NULL,
    "name" VARCHAR(100) NOT NULL,
    "subject" VARCHAR(200) NOT NULL,
    "body_html" TEXT NOT NULL,
    "body_text" TEXT,
    "variables" JSONB NOT NULL DEFAULT '[]',
    "is_active" BOOLEAN NOT NULL DEFAULT true,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "api_logs" (
    "id" BIGSERIAL NOT NULL,
    "client_id" INTEGER,
    "endpoint" VARCHAR(200) NOT NULL,
    "method" VARCHAR(10) NOT NULL,
    "request_body" JSONB,
    "response_code" INTEGER NOT NULL,
    "response_time_ms" INTEGER,
    "ip_address" VARCHAR(45),
    "user_agent" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "blacklist" (
    "id" SERIAL NOT NULL,
    "phone_number" VARCHAR(20) NOT NULL,
    "reason" VARCHAR(200),
    "source" VARCHAR(20) NOT NULL DEFAULT 'manual',
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "created_by" INTEGER,

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

-- CreateTable
CREATE TABLE "admins" (
    "id" SERIAL NOT NULL,
    "name" VARCHAR(200) NOT NULL,
    "email" VARCHAR(200) NOT NULL,
    "password_hash" VARCHAR(255) NOT NULL,
    "role" VARCHAR(20) NOT NULL DEFAULT 'admin',
    "permissions" JSONB NOT NULL DEFAULT '[]',
    "is_active" BOOLEAN NOT NULL DEFAULT true,
    "last_login_at" TIMESTAMP(3),
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "payments" (
    "id" SERIAL NOT NULL,
    "client_id" INTEGER NOT NULL,
    "pagarme_invoice_id" VARCHAR(50),
    "pagarme_charge_id" VARCHAR(50),
    "pagarme_order_id" VARCHAR(50),
    "amount" INTEGER NOT NULL,
    "amount_paid" INTEGER,
    "amount_refunded" INTEGER,
    "status" VARCHAR(20) NOT NULL DEFAULT 'pending',
    "payment_method" VARCHAR(20),
    "card_brand" VARCHAR(20),
    "card_last_digits" VARCHAR(4),
    "boleto_url" VARCHAR(500),
    "boleto_barcode" VARCHAR(100),
    "boleto_due_date" TIMESTAMP(3),
    "pix_qr_code" TEXT,
    "pix_code" TEXT,
    "pix_expires_at" TIMESTAMP(3),
    "paid_at" TIMESTAMP(3),
    "failed_at" TIMESTAMP(3),
    "refunded_at" TIMESTAMP(3),
    "fail_reason" VARCHAR(500),
    "plan_id" INTEGER,
    "plan_name" VARCHAR(100),
    "plan_price" INTEGER,
    "period_start" TIMESTAMP(3),
    "period_end" TIMESTAMP(3),
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateIndex
CREATE INDEX "system_settings_category_idx" ON "system_settings"("category");

-- CreateIndex
CREATE UNIQUE INDEX "plans_slug_key" ON "plans"("slug");

-- CreateIndex
CREATE UNIQUE INDEX "clients_email_key" ON "clients"("email");

-- CreateIndex
CREATE UNIQUE INDEX "clients_pagarme_customer_id_key" ON "clients"("pagarme_customer_id");

-- CreateIndex
CREATE UNIQUE INDEX "clients_pagarme_subscription_id_key" ON "clients"("pagarme_subscription_id");

-- CreateIndex
CREATE UNIQUE INDEX "clients_api_key_key" ON "clients"("api_key");

-- CreateIndex
CREATE UNIQUE INDEX "clients_api_key_test_key" ON "clients"("api_key_test");

-- CreateIndex
CREATE INDEX "clients_email_idx" ON "clients"("email");

-- CreateIndex
CREATE INDEX "clients_api_key_idx" ON "clients"("api_key");

-- CreateIndex
CREATE INDEX "clients_status_idx" ON "clients"("status");

-- CreateIndex
CREATE INDEX "clients_pagarme_customer_id_idx" ON "clients"("pagarme_customer_id");

-- CreateIndex
CREATE INDEX "clients_pagarme_subscription_id_idx" ON "clients"("pagarme_subscription_id");

-- CreateIndex
CREATE INDEX "instances_client_id_idx" ON "instances"("client_id");

-- CreateIndex
CREATE INDEX "instances_evolution_instance_name_idx" ON "instances"("evolution_instance_name");

-- CreateIndex
CREATE INDEX "instances_status_idx" ON "instances"("status");

-- CreateIndex
CREATE UNIQUE INDEX "instances_client_id_name_key" ON "instances"("client_id", "name");

-- CreateIndex
CREATE INDEX "messages_client_id_queued_at_idx" ON "messages"("client_id", "queued_at" DESC);

-- CreateIndex
CREATE INDEX "messages_instance_id_idx" ON "messages"("instance_id");

-- CreateIndex
CREATE INDEX "messages_status_idx" ON "messages"("status");

-- CreateIndex
CREATE INDEX "messages_evolution_message_id_idx" ON "messages"("evolution_message_id");

-- CreateIndex
CREATE INDEX "usage_daily_client_id_date_idx" ON "usage_daily"("client_id", "date" DESC);

-- CreateIndex
CREATE UNIQUE INDEX "usage_daily_client_id_date_key" ON "usage_daily"("client_id", "date");

-- CreateIndex
CREATE INDEX "usage_monthly_client_id_year_month_idx" ON "usage_monthly"("client_id", "year_month" DESC);

-- CreateIndex
CREATE UNIQUE INDEX "usage_monthly_client_id_year_month_key" ON "usage_monthly"("client_id", "year_month");

-- CreateIndex
CREATE UNIQUE INDEX "contracts_type_version_key" ON "contracts"("type", "version");

-- CreateIndex
CREATE UNIQUE INDEX "client_contract_acceptances_client_id_contract_id_key" ON "client_contract_acceptances"("client_id", "contract_id");

-- CreateIndex
CREATE UNIQUE INDEX "email_templates_slug_key" ON "email_templates"("slug");

-- CreateIndex
CREATE INDEX "api_logs_client_id_created_at_idx" ON "api_logs"("client_id", "created_at" DESC);

-- CreateIndex
CREATE UNIQUE INDEX "blacklist_phone_number_key" ON "blacklist"("phone_number");

-- CreateIndex
CREATE INDEX "blacklist_phone_number_idx" ON "blacklist"("phone_number");

-- CreateIndex
CREATE UNIQUE INDEX "admins_email_key" ON "admins"("email");

-- CreateIndex
CREATE UNIQUE INDEX "payments_pagarme_invoice_id_key" ON "payments"("pagarme_invoice_id");

-- CreateIndex
CREATE INDEX "payments_client_id_idx" ON "payments"("client_id");

-- CreateIndex
CREATE INDEX "payments_status_idx" ON "payments"("status");

-- CreateIndex
CREATE INDEX "payments_pagarme_invoice_id_idx" ON "payments"("pagarme_invoice_id");

-- CreateIndex
CREATE INDEX "payments_created_at_idx" ON "payments"("created_at");

-- AddForeignKey
ALTER TABLE "clients" ADD CONSTRAINT "clients_plan_id_fkey" FOREIGN KEY ("plan_id") REFERENCES "plans"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "instances" ADD CONSTRAINT "instances_client_id_fkey" FOREIGN KEY ("client_id") REFERENCES "clients"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "messages" ADD CONSTRAINT "messages_client_id_fkey" FOREIGN KEY ("client_id") REFERENCES "clients"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "messages" ADD CONSTRAINT "messages_instance_id_fkey" FOREIGN KEY ("instance_id") REFERENCES "instances"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "usage_daily" ADD CONSTRAINT "usage_daily_client_id_fkey" FOREIGN KEY ("client_id") REFERENCES "clients"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "usage_monthly" ADD CONSTRAINT "usage_monthly_client_id_fkey" FOREIGN KEY ("client_id") REFERENCES "clients"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "client_contract_acceptances" ADD CONSTRAINT "client_contract_acceptances_client_id_fkey" FOREIGN KEY ("client_id") REFERENCES "clients"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "client_contract_acceptances" ADD CONSTRAINT "client_contract_acceptances_contract_id_fkey" FOREIGN KEY ("contract_id") REFERENCES "contracts"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "api_logs" ADD CONSTRAINT "api_logs_client_id_fkey" FOREIGN KEY ("client_id") REFERENCES "clients"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "payments" ADD CONSTRAINT "payments_client_id_fkey" FOREIGN KEY ("client_id") REFERENCES "clients"("id") ON DELETE CASCADE ON UPDATE CASCADE;
