-- robot_manager v3 review fixes — 一体化 migration
-- 落地依据：docs/standards/20-database-review-checklist.md
--
-- 涵盖 finding：
--   F1  标准字段 5 项 (RobotModel / RobotSku 加 organizationId/createdById/deletedAt)
--   F2  actorId → createdById (ServiceTicketActivity)
--   F3  unique → partial unique WHERE deleted_at IS NULL (12 张表)
--   F5  RobotUnit.placeholderSnOrig 索引（扫码激活 SN 热路径）
--   F6  RobotUnit.skuId / purchaseOrderLineId FK 索引补全
--   F7  PaymentRecord / DeliveryFulfillment 加 version 乐观锁
--   F13 Attachment.ownerType 加 VARCHAR(32) 限长
--
-- 注：本 migration 不包含 platform_ai_usage 的 schema drift 修复（别人留的债，单独 PR 处理）。

-- ============================================================
-- F13: Attachment.ownerType String → VARCHAR(32) 限长
-- ============================================================
ALTER TABLE "platform_master"."attachments" ALTER COLUMN "owner_type" SET DATA TYPE VARCHAR(32);

-- ============================================================
-- F7: 资金敏感表加 version 乐观锁（原则 18）
-- ============================================================
ALTER TABLE "robot_manager"."delivery_fulfillments" ADD COLUMN "version" INTEGER NOT NULL DEFAULT 0;
ALTER TABLE "robot_manager"."payment_records" ADD COLUMN "version" INTEGER NOT NULL DEFAULT 0;

-- ============================================================
-- F1: RobotModel / RobotSku 加标准字段 5 项（原则 3）
--   - organization_id NOT NULL —— 既有行回填 NIL_ORG_ID 占位（单组织阶段）
--   - created_by_id NOT NULL —— 既有行回填 NIL_UUID 占位（已知缺审计源）
--   - deleted_at NULL —— 软删除标记，默认 NULL
-- 用 DEFAULT 子句保证生产部署时既有数据可顺利加列；之后业务代码强制填 actual user。
-- ============================================================
ALTER TABLE "robot_manager"."robot_models"
  ADD COLUMN "organization_id" UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
  ADD COLUMN "created_by_id"   UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
  ADD COLUMN "deleted_at"      TIMESTAMPTZ(3);

ALTER TABLE "robot_manager"."robot_skus"
  ADD COLUMN "organization_id" UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
  ADD COLUMN "created_by_id"   UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
  ADD COLUMN "deleted_at"      TIMESTAMPTZ(3);

-- ============================================================
-- F2: ServiceTicketActivity.actor_id → created_by_id (rename, 标准命名)
-- 例外说明：违反 standard 11（不单次迁移删列），但 service_ticket_activities 是 v3 新建表，
-- 至本 migration 落地前**未在任何 prod/UAT 环境部署**（reference 模块发布前），无生产数据。
-- 因此 single-step rename 安全；正常生产路径仍需走"加新列 → 回填 → 删旧列"两阶段。
-- ============================================================
ALTER TABLE "robot_manager"."service_ticket_activities" DROP COLUMN "actor_id";
ALTER TABLE "robot_manager"."service_ticket_activities" ADD COLUMN "created_by_id" UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000';

-- ============================================================
-- 删 robot_models 旧 enabled 单列索引（被复合 (organization_id, enabled) 取代）
-- ============================================================
DROP INDEX IF EXISTS "robot_manager"."robot_models_enabled_idx";

-- ============================================================
-- F1 配套：新标准字段索引（DataScope 友好 + 软删除路径）
-- ============================================================
CREATE INDEX "robot_models_organization_id_deleted_at_idx" ON "robot_manager"."robot_models"("organization_id", "deleted_at");
CREATE INDEX "robot_models_organization_id_enabled_idx"    ON "robot_manager"."robot_models"("organization_id", "enabled");
CREATE INDEX "robot_skus_organization_id_deleted_at_idx"   ON "robot_manager"."robot_skus"("organization_id", "deleted_at");

-- ============================================================
-- F5: 扫码激活 SN 热路径索引（按 placeholder_sn_orig 反查 robot）
-- F6: FK 索引补全（原则 16，所有 FK 列必加索引）
-- ============================================================
CREATE INDEX "robot_units_sku_id_idx"                              ON "robot_manager"."robot_units"("sku_id");
CREATE INDEX "robot_units_purchase_order_line_id_idx"              ON "robot_manager"."robot_units"("purchase_order_line_id");
CREATE INDEX "robot_units_organization_id_placeholder_sn_orig_idx" ON "robot_manager"."robot_units"("organization_id", "placeholder_sn_orig");

-- ============================================================
-- 收尾：DEFAULT 子句仅为生产部署加列兼容，业务代码强制填 actual 值，
-- 因此本 migration 末尾移除这些 DEFAULT（避免新 INSERT 命中 NIL_UUID 占位）
-- ============================================================
ALTER TABLE "robot_manager"."robot_models"            ALTER COLUMN "organization_id" DROP DEFAULT;
ALTER TABLE "robot_manager"."robot_models"            ALTER COLUMN "created_by_id"   DROP DEFAULT;
ALTER TABLE "robot_manager"."robot_skus"              ALTER COLUMN "organization_id" DROP DEFAULT;
ALTER TABLE "robot_manager"."robot_skus"              ALTER COLUMN "created_by_id"   DROP DEFAULT;
ALTER TABLE "robot_manager"."service_ticket_activities" ALTER COLUMN "created_by_id" DROP DEFAULT;

-- ============================================================
-- F3: partial unique 软删除一致（12 张表）
--
-- 问题：所有"自己生成的业务 code/no" 字段以前用普通 @unique constraint。
--      软删一行后无法再创建同 code 的新行（unique constraint 仍卡住 soft-deleted 行）。
--      违反 standard 17「软删除一致」。
-- 修复：DROP 原 unique，CREATE UNIQUE INDEX ... WHERE deleted_at IS NULL（仅对活跃行生效）。
--
-- ⚠️ 保留 Prisma schema 里的 @unique 装饰器（让 client 类型层 findUnique/upsert 继续工作）。
-- DB 层实际是 partial unique（功能等价：活跃范围内 unique），Prisma 不感知 partial 条件。
-- 副作用：未来 prisma migrate dev 检测到 drift 时会提议重建 unique。
--        团队约定 → 用 migrate diff 看 plan，手动编辑 migration 文件移除"重建 unique"那段。
--
-- 跳过范围（不做 partial）：
-- - 字典 / 配置（Currency / Country / GeoRegion / UnitOfMeasure / Dictionary / RobotSystemConfig）— 无 deletedAt
-- - external system anchor（PurchaseOrder.sap_po_no / SalesOrder.d365_so_id）— 外部系统侧严格 unique
-- - 1:1 关联子表（compliance_checks / robot_import_audits / robot_package_readiness）— 跟父表生命周期
-- - append-only 子表（inspection_records / logistics_legs / quality_label_records / *_lines）— 无独立 deletedAt
-- ============================================================


-- ============================================================
-- platform_master.customers.code
-- ============================================================
ALTER TABLE "platform_master"."customers" DROP CONSTRAINT IF EXISTS "customers_code_key";
DROP INDEX IF EXISTS "platform_master"."customers_code_key";
CREATE UNIQUE INDEX "customers_code_active_key"
  ON "platform_master"."customers" ("code")
  WHERE "deleted_at" IS NULL;

-- ============================================================
-- platform_master.suppliers.code
-- ============================================================
ALTER TABLE "platform_master"."suppliers" DROP CONSTRAINT IF EXISTS "suppliers_code_key";
DROP INDEX IF EXISTS "platform_master"."suppliers_code_key";
CREATE UNIQUE INDEX "suppliers_code_active_key"
  ON "platform_master"."suppliers" ("code")
  WHERE "deleted_at" IS NULL;

-- ============================================================
-- platform_master.partners.code
-- ============================================================
ALTER TABLE "platform_master"."partners" DROP CONSTRAINT IF EXISTS "partners_code_key";
DROP INDEX IF EXISTS "platform_master"."partners_code_key";
CREATE UNIQUE INDEX "partners_code_active_key"
  ON "platform_master"."partners" ("code")
  WHERE "deleted_at" IS NULL;

-- ============================================================
-- platform_master.locations.code
-- ============================================================
ALTER TABLE "platform_master"."locations" DROP CONSTRAINT IF EXISTS "locations_code_key";
DROP INDEX IF EXISTS "platform_master"."locations_code_key";
CREATE UNIQUE INDEX "locations_code_active_key"
  ON "platform_master"."locations" ("code")
  WHERE "deleted_at" IS NULL;

-- ============================================================
-- robot_manager.robot_models.code（phase 1 刚加 deleted_at）
-- ============================================================
ALTER TABLE "robot_manager"."robot_models" DROP CONSTRAINT IF EXISTS "robot_models_code_key";
DROP INDEX IF EXISTS "robot_manager"."robot_models_code_key";
CREATE UNIQUE INDEX "robot_models_code_active_key"
  ON "robot_manager"."robot_models" ("code")
  WHERE "deleted_at" IS NULL;

-- ============================================================
-- robot_manager.robot_skus.code（phase 1 刚加 deleted_at）
-- ============================================================
ALTER TABLE "robot_manager"."robot_skus" DROP CONSTRAINT IF EXISTS "robot_skus_code_key";
DROP INDEX IF EXISTS "robot_manager"."robot_skus_code_key";
CREATE UNIQUE INDEX "robot_skus_code_active_key"
  ON "robot_manager"."robot_skus" ("code")
  WHERE "deleted_at" IS NULL;

-- ============================================================
-- robot_manager.robot_units (organization_id, ffsn) — 复合 partial unique
-- ============================================================
ALTER TABLE "robot_manager"."robot_units" DROP CONSTRAINT IF EXISTS "robot_units_organization_id_ffsn_key";
DROP INDEX IF EXISTS "robot_manager"."robot_units_organization_id_ffsn_key";
CREATE UNIQUE INDEX "robot_units_organization_id_ffsn_active_key"
  ON "robot_manager"."robot_units" ("organization_id", "ffsn")
  WHERE "deleted_at" IS NULL;

-- ============================================================
-- robot_manager.purchase_orders.po_no（业务自定义 PO 号）
-- 注：sap_po_no 保留普通 unique（external anchor）
-- ============================================================
ALTER TABLE "robot_manager"."purchase_orders" DROP CONSTRAINT IF EXISTS "purchase_orders_po_no_key";
DROP INDEX IF EXISTS "robot_manager"."purchase_orders_po_no_key";
CREATE UNIQUE INDEX "purchase_orders_po_no_active_key"
  ON "robot_manager"."purchase_orders" ("po_no")
  WHERE "deleted_at" IS NULL;

-- ============================================================
-- robot_manager.sales_orders.so_no（业务自定义 SO 号）
-- 注：d365_so_id 保留普通 unique（external anchor）
-- ============================================================
ALTER TABLE "robot_manager"."sales_orders" DROP CONSTRAINT IF EXISTS "sales_orders_so_no_key";
DROP INDEX IF EXISTS "robot_manager"."sales_orders_so_no_key";
CREATE UNIQUE INDEX "sales_orders_so_no_active_key"
  ON "robot_manager"."sales_orders" ("so_no")
  WHERE "deleted_at" IS NULL;

-- ============================================================
-- robot_manager.delivery_requests.delivery_no
-- ============================================================
ALTER TABLE "robot_manager"."delivery_requests" DROP CONSTRAINT IF EXISTS "delivery_requests_delivery_no_key";
DROP INDEX IF EXISTS "robot_manager"."delivery_requests_delivery_no_key";
CREATE UNIQUE INDEX "delivery_requests_delivery_no_active_key"
  ON "robot_manager"."delivery_requests" ("delivery_no")
  WHERE "deleted_at" IS NULL;

-- ============================================================
-- robot_manager.service_tickets.ticket_no
-- ============================================================
ALTER TABLE "robot_manager"."service_tickets" DROP CONSTRAINT IF EXISTS "service_tickets_ticket_no_key";
DROP INDEX IF EXISTS "robot_manager"."service_tickets_ticket_no_key";
CREATE UNIQUE INDEX "service_tickets_ticket_no_active_key"
  ON "robot_manager"."service_tickets" ("ticket_no")
  WHERE "deleted_at" IS NULL;

-- ============================================================
-- robot_manager.payment_records.payment_no
-- ============================================================
ALTER TABLE "robot_manager"."payment_records" DROP CONSTRAINT IF EXISTS "payment_records_payment_no_key";
DROP INDEX IF EXISTS "robot_manager"."payment_records_payment_no_key";
CREATE UNIQUE INDEX "payment_records_payment_no_active_key"
  ON "robot_manager"."payment_records" ("payment_no")
  WHERE "deleted_at" IS NULL;
