-- Add robot-manager module (v6)
-- 本 migration 合并了 v2→v6 的开发期迭代，最终落地为：
--   1. 10 状态机（含 SOLD / REPAIRED）
--   2. RobotFieldDef 动态字段（scope=unit/service_record/location，options 内嵌 JSONB）
--   3. 完整业务实体：Model / SKU / Supplier / Customer / Location / Unit / ServiceRecord / StatusChangeLog / SystemConfig / Attachment
--   4. 合并前的中间产物（RobotOption 表 + RobotOptionCategory 枚举 + dict_category 列）未包含

-- CreateSchema
CREATE SCHEMA IF NOT EXISTS "robot_manager";

-- CreateEnum
CREATE TYPE "robot_manager"."RobotStatus" AS ENUM ('ORDERED', 'IN_TRANSIT', 'BONDED', 'IN_STOCK', 'RESERVED', 'SOLD', 'DELIVERED', 'REPAIR', 'REPAIRED', 'CANCELLED');

-- CreateTable
CREATE TABLE "robot_manager"."robot_field_defs" (
    "id" UUID NOT NULL,
    "scope" TEXT NOT NULL DEFAULT 'unit',
    "key" TEXT NOT NULL,
    "label_en" TEXT NOT NULL,
    "label" TEXT,
    "description" TEXT,
    "type" TEXT NOT NULL,
    "group" TEXT,
    "sort_order" INTEGER NOT NULL DEFAULT 0,
    "required" BOOLEAN NOT NULL DEFAULT false,
    "default_value" TEXT,
    "options" JSONB NOT NULL DEFAULT '[]',
    "validation" JSONB NOT NULL DEFAULT '{}',
    "show_in_list" BOOLEAN NOT NULL DEFAULT false,
    "indexed" BOOLEAN NOT NULL DEFAULT false,
    "enabled" BOOLEAN NOT NULL DEFAULT true,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "robot_manager"."robot_system_config" (
    "id" UUID NOT NULL,
    "key" TEXT NOT NULL,
    "value" JSONB NOT NULL,
    "description" TEXT,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "robot_manager"."robot_models" (
    "id" UUID NOT NULL,
    "code" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "brand" TEXT,
    "description" TEXT,
    "image_url" TEXT,
    "enabled" BOOLEAN NOT NULL DEFAULT true,
    "metadata" JSONB NOT NULL DEFAULT '{}',
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "robot_manager"."robot_skus" (
    "id" UUID NOT NULL,
    "model_id" UUID NOT NULL,
    "code" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "variant" TEXT,
    "default_price" DECIMAL(12,2),
    "default_cost" DECIMAL(12,2),
    "description" TEXT,
    "enabled" BOOLEAN NOT NULL DEFAULT true,
    "metadata" JSONB NOT NULL DEFAULT '{}',
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "robot_manager"."robot_suppliers" (
    "id" UUID NOT NULL,
    "code" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "contact_name" TEXT,
    "contact_phone" TEXT,
    "contact_email" TEXT,
    "address" TEXT,
    "payment_terms" TEXT,
    "lead_time_days" INTEGER,
    "enabled" BOOLEAN NOT NULL DEFAULT true,
    "metadata" JSONB NOT NULL DEFAULT '{}',
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "robot_manager"."robot_customers" (
    "id" UUID NOT NULL,
    "code" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "industry" TEXT,
    "contact_name" TEXT,
    "contact_phone" TEXT,
    "contact_email" TEXT,
    "address" TEXT,
    "credit_limit" DECIMAL(14,2),
    "enabled" BOOLEAN NOT NULL DEFAULT true,
    "metadata" JSONB NOT NULL DEFAULT '{}',
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "robot_manager"."robot_locations" (
    "id" UUID NOT NULL,
    "code" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "type_code" TEXT NOT NULL,
    "address" TEXT,
    "contact" TEXT,
    "customer_id" UUID,
    "enabled" BOOLEAN NOT NULL DEFAULT true,
    "metadata" JSONB NOT NULL DEFAULT '{}',
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "robot_manager"."robot_units" (
    "id" UUID NOT NULL,
    "organization_id" UUID NOT NULL,
    "version" INTEGER NOT NULL DEFAULT 0,
    "metadata" JSONB NOT NULL DEFAULT '{}',
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,
    "created_by" UUID NOT NULL,
    "updated_by" UUID NOT NULL,
    "deleted_at" TIMESTAMPTZ(3),
    "ffsn" TEXT NOT NULL,
    "model_id" UUID NOT NULL,
    "sku_id" UUID NOT NULL,
    "current_status" "robot_manager"."RobotStatus" NOT NULL DEFAULT 'ORDERED',
    "supplier_id" UUID,
    "customer_id" UUID,
    "location_id" UUID,

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

-- CreateTable
CREATE TABLE "robot_manager"."robot_service_records" (
    "id" UUID NOT NULL,
    "robot_unit_id" UUID NOT NULL,
    "service_type_code" TEXT NOT NULL,
    "description" TEXT NOT NULL,
    "resolution" TEXT,
    "service_date" DATE NOT NULL,
    "completed_date" DATE,
    "customer_feedback" TEXT,
    "created_by" UUID NOT NULL,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "robot_manager"."robot_status_change_logs" (
    "id" UUID NOT NULL,
    "robot_unit_id" UUID NOT NULL,
    "from_status" "robot_manager"."RobotStatus",
    "to_status" "robot_manager"."RobotStatus" NOT NULL,
    "changed_by" UUID NOT NULL,
    "changed_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "remark" TEXT,

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

-- CreateTable
CREATE TABLE "robot_manager"."robot_attachments" (
    "id" UUID NOT NULL,
    "robot_unit_id" UUID NOT NULL,
    "filename" TEXT NOT NULL,
    "mime_type" TEXT NOT NULL,
    "size" INTEGER NOT NULL,
    "storage_path" TEXT NOT NULL,
    "uploaded_by" UUID NOT NULL,
    "uploaded_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateIndex
CREATE INDEX "robot_field_defs_scope_group_sort_order_idx" ON "robot_manager"."robot_field_defs"("scope", "group", "sort_order");

-- CreateIndex
CREATE INDEX "robot_field_defs_enabled_idx" ON "robot_manager"."robot_field_defs"("enabled");

-- CreateIndex
CREATE UNIQUE INDEX "robot_field_defs_scope_key_key" ON "robot_manager"."robot_field_defs"("scope", "key");

-- CreateIndex
CREATE UNIQUE INDEX "robot_system_config_key_key" ON "robot_manager"."robot_system_config"("key");

-- CreateIndex
CREATE UNIQUE INDEX "robot_models_code_key" ON "robot_manager"."robot_models"("code");

-- CreateIndex
CREATE INDEX "robot_models_enabled_idx" ON "robot_manager"."robot_models"("enabled");

-- CreateIndex
CREATE UNIQUE INDEX "robot_skus_code_key" ON "robot_manager"."robot_skus"("code");

-- CreateIndex
CREATE INDEX "robot_skus_model_id_enabled_idx" ON "robot_manager"."robot_skus"("model_id", "enabled");

-- CreateIndex
CREATE UNIQUE INDEX "robot_suppliers_code_key" ON "robot_manager"."robot_suppliers"("code");

-- CreateIndex
CREATE INDEX "robot_suppliers_enabled_idx" ON "robot_manager"."robot_suppliers"("enabled");

-- CreateIndex
CREATE UNIQUE INDEX "robot_customers_code_key" ON "robot_manager"."robot_customers"("code");

-- CreateIndex
CREATE INDEX "robot_customers_enabled_idx" ON "robot_manager"."robot_customers"("enabled");

-- CreateIndex
CREATE UNIQUE INDEX "robot_locations_code_key" ON "robot_manager"."robot_locations"("code");

-- CreateIndex
CREATE INDEX "robot_locations_enabled_idx" ON "robot_manager"."robot_locations"("enabled");

-- CreateIndex
CREATE INDEX "robot_locations_customer_id_idx" ON "robot_manager"."robot_locations"("customer_id");

-- CreateIndex
CREATE INDEX "robot_locations_type_code_idx" ON "robot_manager"."robot_locations"("type_code");

-- CreateIndex
CREATE INDEX "robot_units_organization_id_current_status_idx" ON "robot_manager"."robot_units"("organization_id", "current_status");

-- CreateIndex
CREATE INDEX "robot_units_organization_id_model_id_idx" ON "robot_manager"."robot_units"("organization_id", "model_id");

-- CreateIndex
CREATE INDEX "robot_units_organization_id_customer_id_idx" ON "robot_manager"."robot_units"("organization_id", "customer_id");

-- CreateIndex
CREATE INDEX "robot_units_organization_id_supplier_id_idx" ON "robot_manager"."robot_units"("organization_id", "supplier_id");

-- CreateIndex
CREATE INDEX "robot_units_organization_id_location_id_idx" ON "robot_manager"."robot_units"("organization_id", "location_id");

-- CreateIndex
CREATE INDEX "robot_units_organization_id_deleted_at_idx" ON "robot_manager"."robot_units"("organization_id", "deleted_at");

-- CreateIndex
CREATE INDEX "robot_service_records_robot_unit_id_idx" ON "robot_manager"."robot_service_records"("robot_unit_id");

-- CreateIndex
CREATE INDEX "robot_status_change_logs_robot_unit_id_idx" ON "robot_manager"."robot_status_change_logs"("robot_unit_id");

-- CreateIndex
CREATE INDEX "robot_status_change_logs_robot_unit_id_changed_at_idx" ON "robot_manager"."robot_status_change_logs"("robot_unit_id", "changed_at");

-- CreateIndex
CREATE INDEX "robot_attachments_robot_unit_id_idx" ON "robot_manager"."robot_attachments"("robot_unit_id");

-- AddForeignKey
ALTER TABLE "robot_manager"."robot_skus" ADD CONSTRAINT "robot_skus_model_id_fkey" FOREIGN KEY ("model_id") REFERENCES "robot_manager"."robot_models"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "robot_manager"."robot_locations" ADD CONSTRAINT "robot_locations_customer_id_fkey" FOREIGN KEY ("customer_id") REFERENCES "robot_manager"."robot_customers"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "robot_manager"."robot_units" ADD CONSTRAINT "robot_units_model_id_fkey" FOREIGN KEY ("model_id") REFERENCES "robot_manager"."robot_models"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "robot_manager"."robot_units" ADD CONSTRAINT "robot_units_sku_id_fkey" FOREIGN KEY ("sku_id") REFERENCES "robot_manager"."robot_skus"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "robot_manager"."robot_units" ADD CONSTRAINT "robot_units_supplier_id_fkey" FOREIGN KEY ("supplier_id") REFERENCES "robot_manager"."robot_suppliers"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "robot_manager"."robot_units" ADD CONSTRAINT "robot_units_customer_id_fkey" FOREIGN KEY ("customer_id") REFERENCES "robot_manager"."robot_customers"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "robot_manager"."robot_units" ADD CONSTRAINT "robot_units_location_id_fkey" FOREIGN KEY ("location_id") REFERENCES "robot_manager"."robot_locations"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "robot_manager"."robot_service_records" ADD CONSTRAINT "robot_service_records_robot_unit_id_fkey" FOREIGN KEY ("robot_unit_id") REFERENCES "robot_manager"."robot_units"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "robot_manager"."robot_status_change_logs" ADD CONSTRAINT "robot_status_change_logs_robot_unit_id_fkey" FOREIGN KEY ("robot_unit_id") REFERENCES "robot_manager"."robot_units"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "robot_manager"."robot_attachments" ADD CONSTRAINT "robot_attachments_robot_unit_id_fkey" FOREIGN KEY ("robot_unit_id") REFERENCES "robot_manager"."robot_units"("id") ON DELETE CASCADE ON UPDATE CASCADE;

