-- =============================================
-- 物流轨迹服务数据库脚本
-- 适用于 MySQL 8.0+
-- =============================================

-- 创建数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS erp_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE erp_db;

-- =============================================
-- 运单状态表
-- =============================================
CREATE TABLE IF NOT EXISTS logistics_waybill_status (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    waybill_no VARCHAR(50) NOT NULL COMMENT '运单号',
    carrier VARCHAR(20) NOT NULL COMMENT '物流商编码：SF-顺丰, YTO-圆通, ZTO-中通, YUNDA-韵达',
    status VARCHAR(30) DEFAULT 'PENDING' COMMENT '运单状态：PENDING-待发货, IN_TRANSIT-运输中, DELIVERING-派送中, SIGNED-已签收, RETURNING-退回中, RETURNED-已退回, EXCEPTION-异常',
    status_label VARCHAR(50) DEFAULT '待发货' COMMENT '状态标签（中文描述）',
    location VARCHAR(200) DEFAULT '' COMMENT '当前位置',
    description VARCHAR(500) DEFAULT '' COMMENT '状态描述',
    signed_time DATETIME DEFAULT NULL COMMENT '签收时间',
    last_trace_time DATETIME DEFAULT NULL COMMENT '最后轨迹时间',
    need_sync TINYINT(1) DEFAULT 1 COMMENT '是否需要同步：0-否, 1-是',
    sync_status TINYINT(1) DEFAULT 0 COMMENT '同步状态：0-未同步, 1-同步中, 2-已同步, 3-同步失败',
    sync_fail_reason VARCHAR(500) DEFAULT NULL COMMENT '同步失败原因',
    sync_retry_count INT DEFAULT 0 COMMENT '同步重试次数',
    order_id BIGINT DEFAULT NULL COMMENT '订单ID',
    order_no VARCHAR(50) DEFAULT NULL COMMENT '订单编号',
    receiver_name VARCHAR(100) DEFAULT '' COMMENT '收件人姓名',
    receiver_phone VARCHAR(20) DEFAULT '' COMMENT '收件人电话',
    receiver_address VARCHAR(500) DEFAULT '' COMMENT '收件人地址',
    deleted TINYINT(1) DEFAULT 0 COMMENT '逻辑删除：0-未删除, 1-已删除',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

    INDEX idx_waybill_no (waybill_no),
    INDEX idx_carrier (carrier),
    INDEX idx_status (status),
    INDEX idx_order_id (order_id),
    INDEX idx_sync_status (sync_status),
    INDEX idx_need_sync (need_sync),
    INDEX idx_last_trace_time (last_trace_time),
    INDEX idx_updated_at (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='运单状态表';

-- =============================================
-- 物流轨迹记录表
-- =============================================
CREATE TABLE IF NOT EXISTS logistics_trace (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    waybill_no VARCHAR(50) NOT NULL COMMENT '运单号',
    carrier VARCHAR(20) NOT NULL COMMENT '物流商编码：SF-顺丰, YTO-圆通, ZTO-中通, YUNDA-韵达',
    status VARCHAR(30) DEFAULT '' COMMENT '轨迹节点状态',
    status_label VARCHAR(50) DEFAULT '' COMMENT '状态标签',
    location VARCHAR(200) DEFAULT '' COMMENT '轨迹发生地点',
    description VARCHAR(500) DEFAULT '' COMMENT '轨迹描述',
    trace_time DATETIME NOT NULL COMMENT '轨迹发生时间',
    raw_status_code VARCHAR(50) DEFAULT '' COMMENT '物流商返回的原始状态码',
    raw_data JSON DEFAULT NULL COMMENT '原始轨迹数据（JSON格式）',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    deleted TINYINT(1) DEFAULT 0 COMMENT '逻辑删除：0-未删除, 1-已删除',

    INDEX idx_waybill_no (waybill_no),
    INDEX idx_carrier (carrier),
    INDEX idx_trace_time (trace_time),
    INDEX idx_waybill_trace (waybill_no, trace_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='物流轨迹记录表';

-- =============================================
-- 初始化数据
-- =============================================

-- 插入示例运单数据
INSERT INTO logistics_waybill_status (waybill_no, carrier, status, status_label, location, description, need_sync, sync_status, order_id, order_no, receiver_name, receiver_phone, receiver_address)
VALUES
    ('SF1234567890', 'SF', 'IN_TRANSIT', '运输中', '深圳市', '快件已从深圳发出，正在运输途中', 1, 0, 10001, 'ORD20240404001', '张三', '13800138000', '广东省广州市天河区'),
    ('YT9876543210', 'YTO', 'DELIVERING', '派送中', '广州市', '快件正在派送中，请保持电话畅通', 1, 0, 10002, 'ORD20240404002', '李四', '13900139000', '广东省深圳市南山区'),
    ('ZTO1112223334', 'ZTO', 'SIGNED', '已签收', '上海市', '已签收，签收人：本人', 0, 2, 10003, 'ORD20240404003', '王五', '13700137000', '上海市浦东新区');

-- =============================================
-- 视图定义（方便查询）
-- =============================================

-- 运单状态视图
CREATE OR REPLACE VIEW v_waybill_status AS
SELECT
    w.id,
    w.waybill_no,
    w.carrier,
    CASE w.carrier
        WHEN 'SF' THEN '顺丰速运'
        WHEN 'YTO' THEN '圆通速递'
        WHEN 'ZTO' THEN '中通快递'
        WHEN 'YUNDA' THEN '韵达快递'
        ELSE w.carrier
    END AS carrier_name,
    w.status,
    w.status_label,
    w.location,
    w.description,
    w.last_trace_time,
    w.signed_time,
    w.need_sync,
    CASE w.sync_status
        WHEN 0 THEN '未同步'
        WHEN 1 THEN '同步中'
        WHEN 2 THEN '已同步'
        WHEN 3 THEN '同步失败'
        ELSE '未知'
    END AS sync_status_label,
    w.sync_fail_reason,
    w.order_id,
    w.order_no,
    w.receiver_name,
    w.updated_at
FROM logistics_waybill_status w
WHERE w.deleted = 0;
