-- CreateEnum
CREATE TYPE "BlockDataSource" AS ENUM ('DRILL_HOLE', 'SURVEY', 'INTERPOLATION');

-- CreateTable
CREATE TABLE "BlockModel" (
    "id" TEXT NOT NULL,
    "license_id" TEXT NOT NULL,
    "pit_id" TEXT,
    "grid_i" INTEGER NOT NULL,
    "grid_j" INTEGER NOT NULL,
    "grid_k" INTEGER NOT NULL,
    "block_x" DOUBLE PRECISION NOT NULL,
    "block_y" DOUBLE PRECISION NOT NULL,
    "block_z" DOUBLE PRECISION NOT NULL,
    "block_size_m" DOUBLE PRECISION NOT NULL DEFAULT 5,
    "tons_estimate" DOUBLE PRECISION NOT NULL,
    "ppm_estimate" DOUBLE PRECISION NOT NULL,
    "ppm_variance" DOUBLE PRECISION,
    "confidence" DOUBLE PRECISION NOT NULL,
    "data_source" "BlockDataSource" NOT NULL DEFAULT 'INTERPOLATION',
    "last_updated" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "Excavator" (
    "id" TEXT NOT NULL,
    "excavator_name" TEXT NOT NULL,
    "excavator_id" TEXT NOT NULL,
    "license_id" TEXT NOT NULL,
    "current_pit_id" TEXT,
    "gps_device_id" TEXT NOT NULL,
    "rtk_accuracy_cm" DOUBLE PRECISION NOT NULL DEFAULT 2,
    "payload_sensor_id" TEXT,
    "status" TEXT NOT NULL DEFAULT 'IDLE',
    "engine_hours" DOUBLE PRECISION NOT NULL DEFAULT 0,
    "fuel_level_percent" DOUBLE PRECISION NOT NULL DEFAULT 100,
    "last_maintenance" TIMESTAMP(3),
    "next_maintenance" TIMESTAMP(3),
    "immobilized" BOOLEAN NOT NULL DEFAULT false,
    "last_lat" DOUBLE PRECISION,
    "last_lng" DOUBLE PRECISION,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "ExcavatorTelemetry" (
    "id" TEXT NOT NULL,
    "excavator_id" TEXT NOT NULL,
    "recorded_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lat" DOUBLE PRECISION NOT NULL,
    "lng" DOUBLE PRECISION NOT NULL,
    "altitude_m" DOUBLE PRECISION,
    "payload_tons" DOUBLE PRECISION,
    "ppm_reading" DOUBLE PRECISION,
    "fuel_percent" DOUBLE PRECISION,
    "engine_hours_snapshot" DOUBLE PRECISION,

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

-- CreateTable
CREATE TABLE "ExcavatorIncident" (
    "id" TEXT NOT NULL,
    "excavator_id" TEXT NOT NULL,
    "severity" TEXT NOT NULL,
    "message" TEXT NOT NULL,
    "outside_fence" BOOLEAN NOT NULL DEFAULT false,
    "low_ppm" BOOLEAN NOT NULL DEFAULT false,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "BlendingInstruction" (
    "id" TEXT NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "target_plant" TEXT NOT NULL,
    "target_ppm" DOUBLE PRECISION NOT NULL,
    "target_tons" DOUBLE PRECISION NOT NULL,
    "source_1_pit_id" TEXT NOT NULL,
    "source_1_tons" DOUBLE PRECISION NOT NULL,
    "source_1_ppm" DOUBLE PRECISION NOT NULL,
    "source_2_pit_id" TEXT,
    "source_2_tons" DOUBLE PRECISION,
    "source_2_ppm" DOUBLE PRECISION,
    "source_3_pit_id" TEXT,
    "source_3_tons" DOUBLE PRECISION,
    "source_3_ppm" DOUBLE PRECISION,
    "result_ppm" DOUBLE PRECISION NOT NULL,
    "issued_by" TEXT NOT NULL,
    "status" TEXT NOT NULL DEFAULT 'PENDING',
    "manifest_pdf_url" TEXT,

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

-- CreateTable
CREATE TABLE "JVAutoSwitchLog" (
    "id" TEXT NOT NULL,
    "pit_id" TEXT NOT NULL,
    "license_id" TEXT NOT NULL,
    "original_ppm" DOUBLE PRECISION NOT NULL,
    "original_owner" TEXT NOT NULL,
    "new_owner" TEXT NOT NULL,
    "switch_reason" TEXT NOT NULL,
    "tons_affected" DOUBLE PRECISION NOT NULL,
    "gold_value_affected" DOUBLE PRECISION NOT NULL,
    "triggered_by" TEXT NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "GradeLoadLog" (
    "id" TEXT NOT NULL,
    "pit_id" TEXT NOT NULL,
    "license_id" TEXT NOT NULL,
    "ppm" DOUBLE PRECISION NOT NULL,
    "tons" DOUBLE PRECISION NOT NULL,
    "notes" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "DilutionAlert" (
    "id" TEXT NOT NULL,
    "pit_id" TEXT NOT NULL,
    "license_id" TEXT NOT NULL,
    "message" TEXT NOT NULL,
    "active" BOOLEAN NOT NULL DEFAULT true,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateIndex
CREATE INDEX "BlockModel_license_id_idx" ON "BlockModel"("license_id");

-- CreateIndex
CREATE INDEX "BlockModel_ppm_estimate_idx" ON "BlockModel"("ppm_estimate");

-- CreateIndex
CREATE UNIQUE INDEX "BlockModel_license_id_grid_i_grid_j_grid_k_key" ON "BlockModel"("license_id", "grid_i", "grid_j", "grid_k");

-- CreateIndex
CREATE UNIQUE INDEX "Excavator_excavator_id_key" ON "Excavator"("excavator_id");

-- CreateIndex
CREATE INDEX "Excavator_license_id_idx" ON "Excavator"("license_id");

-- CreateIndex
CREATE INDEX "ExcavatorTelemetry_excavator_id_recorded_at_idx" ON "ExcavatorTelemetry"("excavator_id", "recorded_at");

-- CreateIndex
CREATE INDEX "ExcavatorIncident_excavator_id_idx" ON "ExcavatorIncident"("excavator_id");

-- CreateIndex
CREATE INDEX "BlendingInstruction_target_plant_idx" ON "BlendingInstruction"("target_plant");

-- CreateIndex
CREATE INDEX "BlendingInstruction_status_idx" ON "BlendingInstruction"("status");

-- CreateIndex
CREATE INDEX "JVAutoSwitchLog_license_id_idx" ON "JVAutoSwitchLog"("license_id");

-- CreateIndex
CREATE INDEX "JVAutoSwitchLog_pit_id_idx" ON "JVAutoSwitchLog"("pit_id");

-- CreateIndex
CREATE INDEX "GradeLoadLog_pit_id_created_at_idx" ON "GradeLoadLog"("pit_id", "created_at");

-- CreateIndex
CREATE INDEX "DilutionAlert_pit_id_idx" ON "DilutionAlert"("pit_id");

-- CreateIndex
CREATE INDEX "DilutionAlert_active_idx" ON "DilutionAlert"("active");

-- AddForeignKey
ALTER TABLE "BlockModel" ADD CONSTRAINT "BlockModel_license_id_fkey" FOREIGN KEY ("license_id") REFERENCES "License"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "BlockModel" ADD CONSTRAINT "BlockModel_pit_id_fkey" FOREIGN KEY ("pit_id") REFERENCES "Pit"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "Excavator" ADD CONSTRAINT "Excavator_license_id_fkey" FOREIGN KEY ("license_id") REFERENCES "License"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "Excavator" ADD CONSTRAINT "Excavator_current_pit_id_fkey" FOREIGN KEY ("current_pit_id") REFERENCES "Pit"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "ExcavatorTelemetry" ADD CONSTRAINT "ExcavatorTelemetry_excavator_id_fkey" FOREIGN KEY ("excavator_id") REFERENCES "Excavator"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "ExcavatorIncident" ADD CONSTRAINT "ExcavatorIncident_excavator_id_fkey" FOREIGN KEY ("excavator_id") REFERENCES "Excavator"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "BlendingInstruction" ADD CONSTRAINT "BlendingInstruction_source_1_pit_id_fkey" FOREIGN KEY ("source_1_pit_id") REFERENCES "Pit"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "BlendingInstruction" ADD CONSTRAINT "BlendingInstruction_source_2_pit_id_fkey" FOREIGN KEY ("source_2_pit_id") REFERENCES "Pit"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "BlendingInstruction" ADD CONSTRAINT "BlendingInstruction_source_3_pit_id_fkey" FOREIGN KEY ("source_3_pit_id") REFERENCES "Pit"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "JVAutoSwitchLog" ADD CONSTRAINT "JVAutoSwitchLog_pit_id_fkey" FOREIGN KEY ("pit_id") REFERENCES "Pit"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "JVAutoSwitchLog" ADD CONSTRAINT "JVAutoSwitchLog_license_id_fkey" FOREIGN KEY ("license_id") REFERENCES "License"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "GradeLoadLog" ADD CONSTRAINT "GradeLoadLog_pit_id_fkey" FOREIGN KEY ("pit_id") REFERENCES "Pit"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "GradeLoadLog" ADD CONSTRAINT "GradeLoadLog_license_id_fkey" FOREIGN KEY ("license_id") REFERENCES "License"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "DilutionAlert" ADD CONSTRAINT "DilutionAlert_pit_id_fkey" FOREIGN KEY ("pit_id") REFERENCES "Pit"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "DilutionAlert" ADD CONSTRAINT "DilutionAlert_license_id_fkey" FOREIGN KEY ("license_id") REFERENCES "License"("id") ON DELETE RESTRICT ON UPDATE CASCADE;