-- CreateExtension
CREATE EXTENSION IF NOT EXISTS postgis;

-- CreateEnum
CREATE TYPE "Role" AS ENUM ('MD', 'LEGAL_OFFICER', 'ACCOUNTANT', 'OPERATOR', 'VIEWER');

-- CreateEnum
CREATE TYPE "LicenseType" AS ENUM ('PML', 'ML', 'SML');

-- CreateEnum
CREATE TYPE "LicenseStatus" AS ENUM ('ACTIVE', 'SUSPENDED', 'EXPIRED', 'PENDING_RENEWAL');

-- CreateEnum
CREATE TYPE "PitStatus" AS ENUM ('ACTIVE', 'DISABLED', 'SUSPENDED');

-- CreateEnum
CREATE TYPE "DocumentType" AS ENUM ('PML_CERTIFICATE', 'JV_CONTRACT', 'ENVIRONMENTAL_PERMIT', 'MINING_PLAN', 'COMMUNITY_AGREEMENT', 'OTHER');

-- CreateTable
CREATE TABLE "User" (
    "id" TEXT NOT NULL,
    "email" TEXT NOT NULL,
    "password_hash" TEXT NOT NULL,
    "full_name" TEXT NOT NULL,
    "role" "Role" NOT NULL DEFAULT 'OPERATOR',
    "is_suspended" BOOLEAN NOT NULL DEFAULT false,
    "suspension_reason" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "License" (
    "id" TEXT NOT NULL,
    "license_type" "LicenseType" NOT NULL,
    "license_number" TEXT NOT NULL,
    "license_name" TEXT NOT NULL,
    "region" TEXT NOT NULL,
    "district" TEXT NOT NULL,
    "ward" TEXT NOT NULL,
    "village" TEXT NOT NULL,
    "gps_center_lat" DOUBLE PRECISION,
    "gps_center_lng" DOUBLE PRECISION,
    "gps_boundary_url" TEXT,
    "area_hectares" DOUBLE PRECISION NOT NULL,
    "issue_date" TIMESTAMP(3) NOT NULL,
    "expiry_date" TIMESTAMP(3) NOT NULL,
    "issuing_authority" TEXT NOT NULL,
    "wmcl_ownership_percent" INTEGER NOT NULL DEFAULT 100,
    "status" "LicenseStatus" NOT NULL DEFAULT 'ACTIVE',
    "days_remaining" INTEGER,
    "deleted_at" TIMESTAMP(3),
    "deleted_by" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "Pit" (
    "id" TEXT NOT NULL,
    "pit_guid" TEXT NOT NULL,
    "pit_number" INTEGER NOT NULL,
    "duara_number" INTEGER NOT NULL,
    "pit_name" TEXT,
    "license_id" TEXT NOT NULL,
    "gps_latitude" DOUBLE PRECISION NOT NULL,
    "gps_longitude" DOUBLE PRECISION NOT NULL,
    "qr_code_url" TEXT NOT NULL,
    "barcode_image_url" TEXT NOT NULL,
    "status" "PitStatus" NOT NULL DEFAULT 'ACTIVE',
    "operational_access" BOOLEAN NOT NULL DEFAULT true,
    "deleted_at" TIMESTAMP(3),
    "deleted_by" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "JVPartner" (
    "id" TEXT NOT NULL,
    "license_id" TEXT NOT NULL,
    "full_name" TEXT NOT NULL,
    "nida_number" TEXT NOT NULL,
    "date_of_birth" TIMESTAMP(3) NOT NULL,
    "nationality" TEXT NOT NULL,
    "gender" TEXT NOT NULL,
    "phone" TEXT NOT NULL,
    "email" TEXT,
    "equity_percentage" INTEGER NOT NULL,
    "role_in_jv" TEXT NOT NULL,
    "contract_start_date" TIMESTAMP(3) NOT NULL,
    "contract_end_date" TIMESTAMP(3) NOT NULL,
    "contract_pdf_url" TEXT NOT NULL,
    "nida_verified" BOOLEAN NOT NULL DEFAULT false,
    "nida_verification_date" TIMESTAMP(3),
    "nida_face_match_percent" DOUBLE PRECISION,
    "equity_locked" BOOLEAN NOT NULL DEFAULT false,
    "equity_unlock_request_reason" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "Document" (
    "id" TEXT NOT NULL,
    "license_id" TEXT NOT NULL,
    "document_type" "DocumentType" NOT NULL,
    "file_name" TEXT NOT NULL,
    "file_url" TEXT NOT NULL,
    "file_size" INTEGER NOT NULL,
    "mime_type" TEXT NOT NULL,
    "description" TEXT,
    "expiry_date" TIMESTAMP(3),
    "issued_by" TEXT,
    "reference_number" TEXT,
    "verified" BOOLEAN NOT NULL DEFAULT false,
    "verified_by" TEXT,
    "verified_at" TIMESTAMP(3),
    "uploaded_by" TEXT NOT NULL,
    "uploaded_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "is_required" BOOLEAN NOT NULL DEFAULT false,
    "deleted_at" TIMESTAMP(3),
    "archived_key" TEXT,

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

-- CreateTable
CREATE TABLE "AuditLog" (
    "id" TEXT NOT NULL,
    "user_id" TEXT NOT NULL,
    "user_name" TEXT NOT NULL,
    "user_role" TEXT NOT NULL,
    "action" TEXT NOT NULL,
    "table_name" TEXT NOT NULL,
    "record_id" TEXT NOT NULL,
    "old_value" JSONB,
    "new_value" JSONB,
    "backdate_otp_used" TEXT,
    "ip_address" TEXT NOT NULL,
    "user_agent" TEXT NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "KillSwitchLog" (
    "id" TEXT NOT NULL,
    "license_id" TEXT NOT NULL,
    "license_number" TEXT NOT NULL,
    "trigger_date" TIMESTAMP(3) NOT NULL,
    "days_remaining" INTEGER NOT NULL DEFAULT 0,
    "action_taken" TEXT NOT NULL,
    "notified_md" BOOLEAN NOT NULL,
    "notified_legal" BOOLEAN NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateIndex
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

-- CreateIndex
CREATE UNIQUE INDEX "License_license_number_key" ON "License"("license_number");

-- CreateIndex
CREATE INDEX "License_license_type_idx" ON "License"("license_type");

-- CreateIndex
CREATE INDEX "License_status_idx" ON "License"("status");

-- CreateIndex
CREATE INDEX "License_expiry_date_idx" ON "License"("expiry_date");

-- CreateIndex
CREATE INDEX "License_deleted_at_idx" ON "License"("deleted_at");

-- CreateIndex
CREATE UNIQUE INDEX "Pit_pit_guid_key" ON "Pit"("pit_guid");

-- CreateIndex
CREATE INDEX "Pit_pit_guid_idx" ON "Pit"("pit_guid");

-- CreateIndex
CREATE INDEX "Pit_status_idx" ON "Pit"("status");

-- CreateIndex
CREATE INDEX "Pit_deleted_at_idx" ON "Pit"("deleted_at");

-- CreateIndex
CREATE UNIQUE INDEX "Pit_license_id_pit_number_key" ON "Pit"("license_id", "pit_number");

-- CreateIndex
CREATE UNIQUE INDEX "JVPartner_nida_number_key" ON "JVPartner"("nida_number");

-- CreateIndex
CREATE UNIQUE INDEX "JVPartner_license_id_nida_number_key" ON "JVPartner"("license_id", "nida_number");

-- CreateIndex
CREATE INDEX "Document_license_id_idx" ON "Document"("license_id");

-- CreateIndex
CREATE INDEX "Document_document_type_idx" ON "Document"("document_type");

-- CreateIndex
CREATE INDEX "Document_deleted_at_idx" ON "Document"("deleted_at");

-- CreateIndex
CREATE INDEX "AuditLog_table_name_idx" ON "AuditLog"("table_name");

-- CreateIndex
CREATE INDEX "AuditLog_record_id_idx" ON "AuditLog"("record_id");

-- CreateIndex
CREATE INDEX "AuditLog_created_at_idx" ON "AuditLog"("created_at");

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

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

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