-- Apple经销商ERP系统 - 业务表索引创建脚本
-- 按照数据库设计文档创建索引

-- =============================================
-- 订单主表索引
-- =============================================

-- 业务唯一索引
CREATE UNIQUE INDEX uk_order_no ON t_order_main(order_no);

-- 查询优化索引
CREATE INDEX idx_order_dealer_code ON t_order_main(dealer_code);
CREATE INDEX idx_order_date ON t_order_main(order_date);
CREATE INDEX idx_order_status ON t_order_main(delivery_status, invoice_status);
CREATE INDEX idx_order_verify ON t_order_main(verify_status);
CREATE INDEX idx_order_upload_time ON t_order_main(upload_time);

-- =============================================
-- 订单商品明细表索引
-- =============================================

-- 外键索引
CREATE INDEX idx_item_order_id ON t_order_item(order_id);
CREATE INDEX idx_item_order_no ON t_order_item(order_no);
CREATE INDEX idx_item_product_code ON t_order_item(product_code);

-- =============================================
-- 出库主表索引
-- =============================================

-- 业务唯一索引
CREATE UNIQUE INDEX uk_delivery_no ON t_delivery_main(delivery_no);

-- 查询优化索引
CREATE INDEX idx_delivery_dealer_code ON t_delivery_main(dealer_code);
CREATE INDEX idx_delivery_order_no ON t_delivery_main(order_no);
CREATE INDEX idx_delivery_date ON t_delivery_main(delivery_date);
CREATE INDEX idx_delivery_status ON t_delivery_main(delivery_status);

-- =============================================
-- 出库商品明细表索引
-- =============================================

-- 外键索引
CREATE INDEX idx_delivery_item_delivery_id ON t_delivery_item(delivery_id);
CREATE INDEX idx_delivery_item_delivery_no ON t_delivery_item(delivery_no);
CREATE INDEX idx_delivery_item_order_no ON t_delivery_item(order_no);
CREATE INDEX idx_delivery_item_product_code ON t_delivery_item(product_code);

-- =============================================
-- 发票主表索引
-- =============================================

-- 业务唯一索引
CREATE UNIQUE INDEX uk_invoice_no ON t_invoice_main(invoice_no);

-- 查询优化索引
CREATE INDEX idx_invoice_dealer_code ON t_invoice_main(dealer_code);
CREATE INDEX idx_invoice_order_no ON t_invoice_main(order_no);
CREATE INDEX idx_invoice_delivery_no ON t_invoice_main(delivery_no);
CREATE INDEX idx_invoice_date ON t_invoice_main(invoice_date);
CREATE INDEX idx_invoice_status ON t_invoice_main(invoice_status);

-- =============================================
-- 发票商品明细表索引
-- =============================================

-- 外键索引
CREATE INDEX idx_invoice_item_invoice_id ON t_invoice_item(invoice_id);
CREATE INDEX idx_invoice_item_invoice_no ON t_invoice_item(invoice_no);
CREATE INDEX idx_invoice_item_order_no ON t_invoice_item(order_no);
CREATE INDEX idx_invoice_item_product_code ON t_invoice_item(product_code);

-- =============================================
-- 返利台账明细表索引
-- =============================================

-- 业务唯一索引
CREATE UNIQUE INDEX uk_rebate_no ON t_rebate_detail(rebate_no);

-- 查询优化索引
CREATE INDEX idx_rebate_dealer_code ON t_rebate_detail(dealer_code);
CREATE INDEX idx_rebate_order_no ON t_rebate_detail(order_no);
CREATE INDEX idx_rebate_product_code ON t_rebate_detail(product_code);
CREATE INDEX idx_rebate_date ON t_rebate_detail(rebate_date);
CREATE INDEX idx_rebate_operate_type ON t_rebate_detail(operate_type);
CREATE INDEX idx_rebate_calc_flag ON t_rebate_detail(calc_flag);

-- =============================================
-- 异常工单表索引
-- =============================================

-- 业务唯一索引
CREATE UNIQUE INDEX uk_workorder_no ON t_exception_workorder(workorder_no);

-- 查询优化索引
CREATE INDEX idx_workorder_dealer_code ON t_exception_workorder(dealer_code);
CREATE INDEX idx_workorder_order_no ON t_exception_workorder(order_no);
CREATE INDEX idx_workorder_exception_type ON t_exception_workorder(exception_type);
CREATE INDEX idx_workorder_severity_level ON t_exception_workorder(severity_level);
CREATE INDEX idx_workorder_status ON t_exception_workorder(workorder_status);
CREATE INDEX idx_workorder_create_time ON t_exception_workorder(create_time);
CREATE INDEX idx_workorder_handler_user ON t_exception_workorder(handler_user);

-- =============================================
-- 异常工单处理日志表索引
-- =============================================

-- 外键索引
CREATE INDEX idx_workorder_log_workorder_id ON t_exception_workorder_log(workorder_id);
CREATE INDEX idx_workorder_log_workorder_no ON t_exception_workorder_log(workorder_no);
CREATE INDEX idx_workorder_log_handle_user ON t_exception_workorder_log(handle_user);
CREATE INDEX idx_workorder_log_handle_time ON t_exception_workorder_log(handle_time);

-- =============================================
-- 经销商信息表索引
-- =============================================

-- 业务唯一索引（已在表定义中创建）
-- UNIQUE INDEX uk_dealer_code ON t_dealer_info(dealer_code);
-- UNIQUE INDEX uk_credit_code ON t_dealer_info(credit_code);

-- 查询优化索引
CREATE INDEX idx_dealer_name ON t_dealer_info(dealer_name);
CREATE INDEX idx_dealer_level ON t_dealer_info(dealer_level);
CREATE INDEX idx_dealer_region ON t_dealer_info(region);
CREATE INDEX idx_dealer_cooperate_status ON t_dealer_info(cooperate_status);
CREATE INDEX idx_dealer_qualification_audit_status ON t_dealer_info(qualification_audit_status);
CREATE INDEX idx_dealer_cooperate_start_date ON t_dealer_info(cooperate_start_date);

-- =============================================
-- 产品信息表索引
-- =============================================

-- 业务唯一索引（已在表定义中创建）
-- UNIQUE INDEX uk_product_code ON t_product_info(product_code);

-- 查询优化索引
CREATE INDEX idx_product_name ON t_product_info(product_name);
CREATE INDEX idx_product_model ON t_product_info(product_model);
CREATE INDEX idx_product_type ON t_product_info(product_type);
CREATE INDEX idx_product_sale_status ON t_product_info(sale_status);
CREATE INDEX idx_product_rebate_flag ON t_product_info(rebate_flag);
CREATE INDEX idx_product_sale_start_date ON t_product_info(sale_start_date);
CREATE INDEX idx_product_sale_end_date ON t_product_info(sale_end_date);

-- =============================================
-- 复合索引优化查询性能
-- =============================================

-- 订单查询优化
CREATE INDEX idx_order_complex_query ON t_order_main(dealer_code, order_date, delivery_status, invoice_status);

-- 出库查询优化
CREATE INDEX idx_delivery_complex_query ON t_delivery_main(dealer_code, delivery_date, delivery_status);

-- 发票查询优化
CREATE INDEX idx_invoice_complex_query ON t_invoice_main(dealer_code, invoice_date, invoice_status);

-- 返利查询优化
CREATE INDEX idx_rebate_complex_query ON t_rebate_detail(dealer_code, rebate_date, operate_type);

-- 异常工单查询优化
CREATE INDEX idx_workorder_complex_query ON t_exception_workorder(workorder_status, severity_level, create_time);

-- 经销商查询优化
CREATE INDEX idx_dealer_complex_query ON t_dealer_info(dealer_level, cooperate_status, region);

-- 产品查询优化
CREATE INDEX idx_product_complex_query ON t_product_info(product_type, sale_status, rebate_flag);