-- robot_manager v3 review fixes — phase 3
-- 落地依据：docs/standards/20-database-review-checklist.md
--
-- 涵盖 finding：
--   N8 ServiceTicket (organizationId, customerId) 复合索引 ("客户售后历史"查询路径)
--   N9 业务字段 VarChar(N) 限长 (原则 15)
--
-- N9 范围（"机器人相关"）：
--   - robot_manager 模块自身字段（code / no / anchor / typeCode）
--   - platform_master 主数据字段（v3 重构时迁过去）+ Attachment 字段
--   - 不动：字典表 (Currency / Country / GeoRegion / UnitOfMeasure / Dictionary)
--   - 不动：name / description / notes / brand / industry / taxId 等业务长文本（TEXT 保灵活）
--
-- 注：本 migration 不包含 prisma migrate diff 自动生成的下列内容（drift 不属本主题）：
--   - 重建 partial unique (commit 2 已用 raw SQL 做成 partial)
--   - platform_ai_usage 的 RenameIndex (别人留的债)
--
-- ALTER COLUMN ... SET DATA TYPE VARCHAR(N) 在 PostgreSQL 上：
--   - VARCHAR(N) >= 现有最长值时 = 快速元数据变更 (无表锁、无数据重写)
--   - VARCHAR(N) <  现有最长值时 = 报错 ERROR: value too long for type
--   生产部署前必须先 SELECT MAX(LENGTH(col)) 确认安全。本 PR 选定的 N 值都比业务规则上限宽松数倍。

-- ============================================================
-- N8: ServiceTicket (organizationId, customerId) 复合索引
-- ============================================================
CREATE INDEX "service_tickets_organization_id_customer_id_idx"
  ON "robot_manager"."service_tickets"("organization_id", "customer_id");

-- ============================================================
-- N9: robot_manager 字段限长
-- ============================================================

-- L2 主数据
ALTER TABLE "robot_manager"."robot_models"        ALTER COLUMN "code"               SET DATA TYPE VARCHAR(32);
ALTER TABLE "robot_manager"."robot_skus"          ALTER COLUMN "code"               SET DATA TYPE VARCHAR(32);
ALTER TABLE "robot_manager"."robot_system_config" ALTER COLUMN "key"                SET DATA TYPE VARCHAR(64);

-- L2 核心 RobotUnit
ALTER TABLE "robot_manager"."robot_units"
  ALTER COLUMN "ffsn"                SET DATA TYPE VARCHAR(64),
  ALTER COLUMN "ffsn_display"        SET DATA TYPE VARCHAR(64),
  ALTER COLUMN "placeholder_sn_orig" SET DATA TYPE VARCHAR(64),
  ALTER COLUMN "supplier_sn"         SET DATA TYPE VARCHAR(64),
  ALTER COLUMN "sap_material_no"     SET DATA TYPE VARCHAR(32);

-- L3 单据号 / 外部系统 anchor
ALTER TABLE "robot_manager"."purchase_orders"
  ALTER COLUMN "po_no"     SET DATA TYPE VARCHAR(32),
  ALTER COLUMN "sap_po_no" SET DATA TYPE VARCHAR(32);

ALTER TABLE "robot_manager"."purchase_order_lines" ALTER COLUMN "placeholder_pattern" SET DATA TYPE VARCHAR(64);

ALTER TABLE "robot_manager"."sales_orders"
  ALTER COLUMN "so_no"      SET DATA TYPE VARCHAR(32),
  ALTER COLUMN "d365_so_id" SET DATA TYPE VARCHAR(32);

ALTER TABLE "robot_manager"."delivery_requests"     ALTER COLUMN "delivery_no"      SET DATA TYPE VARCHAR(32);
ALTER TABLE "robot_manager"."delivery_fulfillments" ALTER COLUMN "sap_pgi_doc_no"   SET DATA TYPE VARCHAR(32);

ALTER TABLE "robot_manager"."payment_records"
  ALTER COLUMN "payment_no"             SET DATA TYPE VARCHAR(32),
  ALTER COLUMN "sap_clearing_doc_no"    SET DATA TYPE VARCHAR(32);

ALTER TABLE "robot_manager"."service_tickets"
  ALTER COLUMN "ticket_no"       SET DATA TYPE VARCHAR(32),
  ALTER COLUMN "issue_type_code" SET DATA TYPE VARCHAR(64);

ALTER TABLE "robot_manager"."quality_label_records" ALTER COLUMN "label_type_code" SET DATA TYPE VARCHAR(64);
ALTER TABLE "robot_manager"."inspection_records"    ALTER COLUMN "issue_tag_code"  SET DATA TYPE VARCHAR(64);

ALTER TABLE "robot_manager"."logistics_legs"
  ALTER COLUMN "import_declaration_type_code" SET DATA TYPE VARCHAR(64),
  ALTER COLUMN "tariff_type_code"             SET DATA TYPE VARCHAR(64);

ALTER TABLE "robot_manager"."robot_import_audits" ALTER COLUMN "import_batch" SET DATA TYPE VARCHAR(64);

-- ============================================================
-- N9: platform_master 主数据字段限长（v3 重构迁过去的）
-- ============================================================

ALTER TABLE "platform_master"."customers" ALTER COLUMN "code" SET DATA TYPE VARCHAR(32);
ALTER TABLE "platform_master"."suppliers" ALTER COLUMN "code" SET DATA TYPE VARCHAR(32);
ALTER TABLE "platform_master"."partners"  ALTER COLUMN "code" SET DATA TYPE VARCHAR(32);
ALTER TABLE "platform_master"."locations" ALTER COLUMN "code" SET DATA TYPE VARCHAR(32);

ALTER TABLE "platform_master"."customer_contacts"
  ALTER COLUMN "phone" SET DATA TYPE VARCHAR(32),
  ALTER COLUMN "email" SET DATA TYPE VARCHAR(255);

ALTER TABLE "platform_master"."supplier_contacts"
  ALTER COLUMN "phone" SET DATA TYPE VARCHAR(32),
  ALTER COLUMN "email" SET DATA TYPE VARCHAR(255);

ALTER TABLE "platform_master"."partner_contacts"
  ALTER COLUMN "phone" SET DATA TYPE VARCHAR(32),
  ALTER COLUMN "email" SET DATA TYPE VARCHAR(255);

ALTER TABLE "platform_master"."attachments"
  ALTER COLUMN "category"     SET DATA TYPE VARCHAR(64),
  ALTER COLUMN "filename"     SET DATA TYPE VARCHAR(255),
  ALTER COLUMN "mime_type"    SET DATA TYPE VARCHAR(128),
  ALTER COLUMN "storage_path" SET DATA TYPE VARCHAR(512);
