sqxp/docs/sql/005_inquiry_quotation_select_supplier_price_source.sql

-- ----------------------------
-- Migration: inquiry/quotation select supplier -> price list source fields
-- Date: 2026-04-03
-- Notes:
-- - Add source linkage fields on biz_price_list (+ his table)
-- - Add helpful indexes for query patterns
-- ----------------------------

-- 1) biz_price_list: add source fields
ALTER TABLE `biz_price_list`
  ADD COLUMN `inquiry_id` varchar(32) DEFAULT NULL COMMENT '来源询价单ID' AFTER `supplier_id`,
  ADD COLUMN `quotation_id` varchar(32) DEFAULT NULL COMMENT '来源报价单ID' AFTER `inquiry_id`,
  ADD COLUMN `source_type` varchar(20) DEFAULT NULL COMMENT '来源类型: quotation_select/manual' AFTER `quotation_id`;

-- 2) biz_price_list_his: keep same fields for traceability
ALTER TABLE `biz_price_list_his`
  ADD COLUMN `inquiry_id` varchar(32) DEFAULT NULL COMMENT '来源询价单ID' AFTER `supplier_id`,
  ADD COLUMN `quotation_id` varchar(32) DEFAULT NULL COMMENT '来源报价单ID' AFTER `inquiry_id`,
  ADD COLUMN `source_type` varchar(20) DEFAULT NULL COMMENT '来源类型' AFTER `quotation_id`;

-- 3) Indexes
-- price list: active list per region + recent
CREATE INDEX `idx_price_list_region_status` ON `biz_price_list` (`region_id`, `status`, `is_del`);
CREATE INDEX `idx_price_list_inquiry` ON `biz_price_list` (`inquiry_id`, `is_del`);
CREATE INDEX `idx_price_list_quotation` ON `biz_price_list` (`quotation_id`, `is_del`);

-- inquiry: list per region with published first + recent
CREATE INDEX `idx_inquiry_region_status_create_at` ON `biz_inquiry` (`region_id`, `status`, `create_at`);

-- quotation: latest quotation per supplier within inquiry
CREATE INDEX `idx_quotation_inquiry_supplier_create_at` ON `biz_quotation` (`inquiry_id`, `supplier_id`, `create_at`);