-- CreateEnum
CREATE TYPE "Roles" AS ENUM ('ADMINISTRATOR', 'USER');

-- CreateEnum
CREATE TYPE "UserType" AS ENUM ('LEGAL', 'PHYSICAL');

-- CreateEnum
CREATE TYPE "FavoriteOrder" AS ENUM ('FIRST', 'SECOND', 'THIRD');

-- CreateEnum
CREATE TYPE "TransactionStatus" AS ENUM ('PENDING', 'CANCELED', 'REJECTED', 'COMPLETED', 'REVERSED');

-- CreateEnum
CREATE TYPE "TransactionType" AS ENUM ('WITHDRAWAL', 'DEPOSIT', 'TRANSFER', 'REVERSAL', 'PAYMENT');

-- CreateEnum
CREATE TYPE "DonationStatus" AS ENUM ('AWAITING_UTILIZATION', 'PARTIALLY_USED', 'FULLY_USED');

-- CreateEnum
CREATE TYPE "TreeSize" AS ENUM ('SMALL', 'MEDIUM', 'LARGE');

-- CreateEnum
CREATE TYPE "RequestStatus" AS ENUM ('PENDING', 'CANCELED', 'PROCESSED', 'IN_PROGRESS', 'COMPLETED', 'FAILED');

-- CreateTable
CREATE TABLE "user" (
    "id" SERIAL NOT NULL,
    "email" TEXT NOT NULL,
    "password" TEXT NOT NULL,
    "role" "Roles" NOT NULL DEFAULT 'USER',
    "user_type" "UserType" NOT NULL,
    "registration_date" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "person" (
    "id" INTEGER NOT NULL,
    "name" TEXT NOT NULL,
    "cpf" TEXT NOT NULL,
    "rg" TEXT NOT NULL,
    "phone" TEXT NOT NULL,
    "birth_date" TIMESTAMP(3) NOT NULL,
    "creation_date" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "person_address" (
    "id" SERIAL NOT NULL,
    "person_id" INTEGER NOT NULL,
    "address_id" INTEGER NOT NULL,
    "favorite" "FavoriteOrder" NOT NULL DEFAULT 'FIRST',

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

-- CreateTable
CREATE TABLE "company" (
    "id" INTEGER NOT NULL,
    "cnpj" TEXT NOT NULL,
    "business_name" TEXT NOT NULL,
    "trade_name" TEXT NOT NULL,
    "phone" TEXT NOT NULL,
    "email" TEXT NOT NULL,
    "founding_date" TIMESTAMP(3) NOT NULL,
    "address_id" INTEGER NOT NULL,

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

-- CreateTable
CREATE TABLE "nursery" (
    "id" INTEGER NOT NULL,
    "creation_date" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "tree_nursery" (
    "nursery_stock_id" SERIAL NOT NULL,
    "nursery_id" INTEGER NOT NULL,
    "species_id" INTEGER NOT NULL,
    "quantity" INTEGER NOT NULL,

    CONSTRAINT "tree_nursery_pkey" PRIMARY KEY ("nursery_stock_id")
);

-- CreateTable
CREATE TABLE "transaction" (
    "id" SERIAL NOT NULL,
    "source_account_id" INTEGER NOT NULL,
    "destination_account_id" INTEGER NOT NULL,
    "amount" DOUBLE PRECISION NOT NULL,
    "type" "TransactionType" NOT NULL,
    "notes" TEXT,
    "status" "TransactionStatus" NOT NULL DEFAULT 'PENDING',
    "transaction_date" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "donation" (
    "id" SERIAL NOT NULL,
    "amount" DOUBLE PRECISION NOT NULL,
    "donation_date" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "utilization_date" TIMESTAMP(3),
    "utilized_amount" DOUBLE PRECISION,
    "status" "DonationStatus" NOT NULL,
    "donor_id" INTEGER NOT NULL,

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

-- CreateTable
CREATE TABLE "balance" (
    "id" INTEGER NOT NULL,
    "balance" DOUBLE PRECISION NOT NULL,
    "user_id" INTEGER NOT NULL,

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

-- CreateTable
CREATE TABLE "city" (
    "id" SERIAL NOT NULL,
    "name" VARCHAR(60) NOT NULL,
    "uf" VARCHAR(2) NOT NULL,
    "ibge" INTEGER NOT NULL,
    "lat" DOUBLE PRECISION NOT NULL,
    "long" DOUBLE PRECISION NOT NULL,
    "cod_tom" INTEGER NOT NULL,

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

-- CreateTable
CREATE TABLE "state" (
    "id" SERIAL NOT NULL,
    "name" VARCHAR(60) NOT NULL,
    "uf" VARCHAR(2) NOT NULL,
    "ibge" INTEGER NOT NULL,
    "country" INTEGER NOT NULL,
    "ddd" JSONB NOT NULL,

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

-- CreateTable
CREATE TABLE "country" (
    "id" SERIAL NOT NULL,
    "name" VARCHAR(60) NOT NULL,
    "name_pt" VARCHAR(60),
    "acronym" VARCHAR(2),
    "bacen" INTEGER NOT NULL,

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

-- CreateTable
CREATE TABLE "address" (
    "id" SERIAL NOT NULL,
    "city_id" INTEGER NOT NULL,
    "street" TEXT NOT NULL,
    "number" INTEGER NOT NULL,
    "neighborhood" TEXT NOT NULL,
    "postal_code" TEXT NOT NULL,

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

-- CreateTable
CREATE TABLE "species" (
    "id" SERIAL NOT NULL,
    "common_name" TEXT NOT NULL,
    "scientific_name" TEXT NOT NULL,
    "family" TEXT NOT NULL,
    "origin" TEXT NOT NULL,
    "foliage" TEXT NOT NULL,
    "foliage_color" TEXT NOT NULL,
    "canopy_size" TEXT NOT NULL,
    "growth_rate" TEXT NOT NULL,
    "deciduousness" TEXT NOT NULL,

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

-- CreateTable
CREATE TABLE "planting_rule" (
    "id" SERIAL NOT NULL,
    "manual_id" INTEGER NOT NULL,
    "tree_size" "TreeSize" NOT NULL,
    "post_distance" DOUBLE PRECISION NOT NULL,
    "inspection_box_distance" DOUBLE PRECISION NOT NULL,
    "building_distance" DOUBLE PRECISION NOT NULL,
    "transformer_distance" DOUBLE PRECISION NOT NULL,

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

-- CreateTable
CREATE TABLE "manual" (
    "id" SERIAL NOT NULL,
    "city_id" INTEGER NOT NULL,
    "status" BOOLEAN NOT NULL,

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

-- CreateTable
CREATE TABLE "request" (
    "id" SERIAL NOT NULL,
    "species_id" INTEGER NOT NULL,
    "requester_id" INTEGER NOT NULL,
    "message" VARCHAR(255),
    "request_date" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "completion_date" TIMESTAMP(3),
    "address_street" TEXT NOT NULL,
    "address_number" TEXT NOT NULL,
    "address_neighborhood" TEXT NOT NULL,
    "address_city" TEXT NOT NULL,
    "address_state" TEXT NOT NULL,
    "address_postal_code" TEXT NOT NULL,
    "address_complement" TEXT,
    "post_distance" DOUBLE PRECISION,
    "inspection_box_distance" DOUBLE PRECISION,
    "building_distance" DOUBLE PRECISION,
    "transformer_distance" DOUBLE PRECISION,
    "status" "RequestStatus" NOT NULL DEFAULT 'PENDING',
    "nurseryId" INTEGER,

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

-- CreateTable
CREATE TABLE "service" (
    "request_id" INTEGER NOT NULL,
    "planter_id" INTEGER NOT NULL,
    "nursery_id" INTEGER NOT NULL,
    "requester_observation" VARCHAR(255),
    "planter_observation" VARCHAR(255),
    "nursery_observation" VARCHAR(255),

    CONSTRAINT "service_pkey" PRIMARY KEY ("request_id")
);

-- CreateTable
CREATE TABLE "request_cost" (
    "service_id" INTEGER NOT NULL,
    "planter" DOUBLE PRECISION NOT NULL,
    "nursery" DOUBLE PRECISION NOT NULL,
    "plantae" DOUBLE PRECISION NOT NULL,
    "total_cost" DOUBLE PRECISION NOT NULL,
    "transaction_nursery_id" INTEGER NOT NULL,
    "transaction_planter_id" INTEGER NOT NULL,

    CONSTRAINT "request_cost_pkey" PRIMARY KEY ("service_id")
);

-- CreateTable
CREATE TABLE "planting_media" (
    "id" SERIAL NOT NULL,
    "planting_id" INTEGER NOT NULL,
    "image_url" TEXT,
    "qr_code_url" TEXT,
    "date" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateIndex
CREATE UNIQUE INDEX "user_email_key" ON "user"("email");

-- CreateIndex
CREATE UNIQUE INDEX "balance_user_id_key" ON "balance"("user_id");

-- CreateIndex
CREATE UNIQUE INDEX "planting_rule_tree_size_key" ON "planting_rule"("tree_size");

-- AddForeignKey
ALTER TABLE "person" ADD CONSTRAINT "person_id_fkey" FOREIGN KEY ("id") REFERENCES "user"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "person_address" ADD CONSTRAINT "person_address_address_id_fkey" FOREIGN KEY ("address_id") REFERENCES "address"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "person_address" ADD CONSTRAINT "person_address_person_id_fkey" FOREIGN KEY ("person_id") REFERENCES "person"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "company" ADD CONSTRAINT "company_id_fkey" FOREIGN KEY ("id") REFERENCES "user"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "company" ADD CONSTRAINT "company_address_id_fkey" FOREIGN KEY ("address_id") REFERENCES "address"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "nursery" ADD CONSTRAINT "nursery_id_fkey" FOREIGN KEY ("id") REFERENCES "company"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "tree_nursery" ADD CONSTRAINT "tree_nursery_nursery_id_fkey" FOREIGN KEY ("nursery_id") REFERENCES "nursery"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "tree_nursery" ADD CONSTRAINT "tree_nursery_species_id_fkey" FOREIGN KEY ("species_id") REFERENCES "species"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "transaction" ADD CONSTRAINT "transaction_source_account_id_fkey" FOREIGN KEY ("source_account_id") REFERENCES "balance"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "transaction" ADD CONSTRAINT "transaction_destination_account_id_fkey" FOREIGN KEY ("destination_account_id") REFERENCES "balance"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "donation" ADD CONSTRAINT "donation_donor_id_fkey" FOREIGN KEY ("donor_id") REFERENCES "user"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "balance" ADD CONSTRAINT "balance_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "user"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "address" ADD CONSTRAINT "address_city_id_fkey" FOREIGN KEY ("city_id") REFERENCES "city"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "planting_rule" ADD CONSTRAINT "planting_rule_manual_id_fkey" FOREIGN KEY ("manual_id") REFERENCES "manual"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "manual" ADD CONSTRAINT "manual_city_id_fkey" FOREIGN KEY ("city_id") REFERENCES "city"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "request" ADD CONSTRAINT "request_requester_id_fkey" FOREIGN KEY ("requester_id") REFERENCES "user"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "request" ADD CONSTRAINT "request_species_id_fkey" FOREIGN KEY ("species_id") REFERENCES "species"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "request" ADD CONSTRAINT "request_nurseryId_fkey" FOREIGN KEY ("nurseryId") REFERENCES "nursery"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "service" ADD CONSTRAINT "service_planter_id_fkey" FOREIGN KEY ("planter_id") REFERENCES "user"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "service" ADD CONSTRAINT "service_nursery_id_fkey" FOREIGN KEY ("nursery_id") REFERENCES "nursery"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "request_cost" ADD CONSTRAINT "request_cost_transaction_nursery_id_fkey" FOREIGN KEY ("transaction_nursery_id") REFERENCES "transaction"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "request_cost" ADD CONSTRAINT "request_cost_transaction_planter_id_fkey" FOREIGN KEY ("transaction_planter_id") REFERENCES "transaction"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "request_cost" ADD CONSTRAINT "request_cost_service_id_fkey" FOREIGN KEY ("service_id") REFERENCES "service"("request_id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "planting_media" ADD CONSTRAINT "planting_media_planting_id_fkey" FOREIGN KEY ("planting_id") REFERENCES "service"("request_id") ON DELETE RESTRICT ON UPDATE CASCADE;
