doris-sync/sql/patient_exam/01_create_ods_tables.sql

-- ============================================================
-- ODS 层（原始数据层）建表语句
-- 说明：MySQL 原始表的镜像，结构完全一致，加 ods_ 前缀
-- ============================================================

-- 1. 患者表
CREATE TABLE IF NOT EXISTS ods_patient_custome (
    id BIGINT COMMENT '患者ID（主键）',
    patient_id VARCHAR(50) COMMENT '病历号',
    name VARCHAR(100) COMMENT '姓名',
    sex VARCHAR(10) COMMENT '性别',
    birthday DATE COMMENT '生日',
    identity VARCHAR(50) COMMENT '身份证号',
    mobile VARCHAR(20) COMMENT '手机号',
    source VARCHAR(200) COMMENT '来源',
    inviter VARCHAR(100) COMMENT '邀请人',
    inviter_phone VARCHAR(20) COMMENT '邀请人电话',
    customer_optometrist_staff_id BIGINT COMMENT '专属视光师ID',
    create_time DATETIME COMMENT '创建时间',
    update_time DATETIME COMMENT '更新时间',
    source_create_time DATETIME COMMENT '源表创建时间',
    source_update_time DATETIME COMMENT '源表更新时间'
)
DUPLICATE KEY(id)
PROPERTIES (
    "replication_num" = "1",
    "in_memory" = "false",
    "storage_format" = "V2"
);

-- 2. 就诊表
CREATE TABLE IF NOT EXISTS ods_clinic_clinic (
    id BIGINT COMMENT '就诊ID（主键）',
    patient_id BIGINT COMMENT '患者ID',
    org_id INT COMMENT '机构ID',
    type VARCHAR(20) COMMENT '就诊类型',
    channel VARCHAR(50) COMMENT '渠道',
    add_user VARCHAR(100) COMMENT '添加人',
    appoint_date DATE COMMENT '预约日期',
    appointment_time_start DATETIME COMMENT '预约开始时间',
    matter TEXT COMMENT '事项',
    accept_doctor_id BIGINT COMMENT '接诊医生ID',
    accept_optometrist_id BIGINT COMMENT '接诊视光师ID',
    accept_assistant_id BIGINT COMMENT '接诊助理ID',
    come_time DATETIME COMMENT '就诊时间',
    come_date DATE COMMENT '就诊日期',
    reg_status INT COMMENT '挂号状态',
    pno VARCHAR(50) COMMENT '病历号',
    create_time DATETIME COMMENT '创建时间',
    update_time DATETIME COMMENT '更新时间',
    source_create_time DATETIME COMMENT '源表创建时间',
    source_update_time DATETIME COMMENT '源表更新时间'
)
DUPLICATE KEY(id)
PROPERTIES (
    "replication_num" = "1",
    "in_memory" = "false",
    "storage_format" = "V2"
);

-- 3. 检查结果表
CREATE TABLE IF NOT EXISTS ods_exam_examresult (
    id BIGINT COMMENT '检查结果ID（主键）',
    clinic_id BIGINT COMMENT '就诊ID',
    org_id INT COMMENT '机构ID',
    result STRING COMMENT '检查结果JSON',
    tab VARCHAR(50) COMMENT '检查类型：yj_dnyg/tj_gxsw',
    visual TINYINT COMMENT '是否视力检查',
    exam_id INT COMMENT '检查项目ID',
    created DATETIME COMMENT '创建时间',
    create_time DATETIME COMMENT '创建时间',
    update_time DATETIME COMMENT '更新时间',
    source_create_time DATETIME COMMENT '源表创建时间',
    source_update_time DATETIME COMMENT '源表更新时间'
)
DUPLICATE KEY(id)
PROPERTIES (
    "replication_num" = "1",
    "in_memory" = "false",
    "storage_format" = "V2"
);

-- 4. 取镜记录表
CREATE TABLE IF NOT EXISTS ods_treat_sellgoods (
    id BIGINT COMMENT '取镜记录ID（主键）',
    sell_goods_list_id BIGINT COMMENT '销售清单ID',
    goods_id BIGINT COMMENT '商品ID',
    out_time DATETIME COMMENT '取镜时间',
    out_date DATE COMMENT '取镜日期',
    remark VARCHAR(500) COMMENT '备注',
    create_time DATETIME COMMENT '创建时间',
    update_time DATETIME COMMENT '更新时间',
    source_create_time DATETIME COMMENT '源表创建时间',
    source_update_time DATETIME COMMENT '源表更新时间'
)
DUPLICATE KEY(id)
PROPERTIES (
    "replication_num" = "1",
    "in_memory" = "false",
    "storage_format" = "V2"
);

-- 5. 商品表
CREATE TABLE IF NOT EXISTS ods_material_goods (
    id BIGINT COMMENT '商品ID（主键）',
    name VARCHAR(200) COMMENT '商品名称',
    uuid_no VARCHAR(100) COMMENT '商品UUID',
    fee_class VARCHAR(50) COMMENT '收费分类',
    group_id INT COMMENT '地区ID',
    create_time DATETIME COMMENT '创建时间',
    update_time DATETIME COMMENT '更新时间',
    source_create_time DATETIME COMMENT '源表创建时间',
    source_update_time DATETIME COMMENT '源表更新时间'
)
DUPLICATE KEY(id)
PROPERTIES (
    "replication_num" = "1",
    "in_memory" = "false",
    "storage_format" = "V2"
);

-- 6. 订单表
CREATE TABLE IF NOT EXISTS ods_fee_order (
    id BIGINT COMMENT '订单ID（主键）',
    patientId VARCHAR(50) COMMENT '病历号',
    org_id INT COMMENT '机构ID',
    pay_status INT COMMENT '支付状态',
    pay_time DATETIME COMMENT '支付时间',
    pay_date DATE COMMENT '支付日期',
    total_amt DECIMAL(18,2) COMMENT '订单金额',
    create_time DATETIME COMMENT '创建时间',
    update_time DATETIME COMMENT '更新时间',
    source_create_time DATETIME COMMENT '源表创建时间',
    source_update_time DATETIME COMMENT '源表更新时间'
)
DUPLICATE KEY(id)
PROPERTIES (
    "replication_num" = "1",
    "in_memory" = "false",
    "storage_format" = "V2"
);

-- 7. 订单明细表
CREATE TABLE IF NOT EXISTS ods_fee_orderdetail (
    id BIGINT COMMENT '订单明细ID（主键）',
    order_id BIGINT COMMENT '订单ID',
    goods_id BIGINT COMMENT '商品ID',
    patient_id BIGINT COMMENT '患者ID',
    name VARCHAR(200) COMMENT '商品名称',
    pay_time DATETIME COMMENT '支付时间',
    pay_date DATE COMMENT '支付日期',
    price DECIMAL(18,2) COMMENT '单价',
    quantity INT COMMENT '数量',
    total_amt DECIMAL(18,2) COMMENT '总金额',
    create_time DATETIME COMMENT '创建时间',
    update_time DATETIME COMMENT '更新时间',
    source_create_time DATETIME COMMENT '源表创建时间',
    source_update_time DATETIME COMMENT '源表更新时间'
)
DUPLICATE KEY(id)
PROPERTIES (
    "replication_num" = "1",
    "in_memory" = "false",
    "storage_format" = "V2"
);

-- 8. 员工表
CREATE TABLE IF NOT EXISTS ods_staff_staff (
    id BIGINT COMMENT '员工ID（主键）',
    user_id BIGINT COMMENT '用户ID',
    main_org_id INT COMMENT '主机构ID',
    name VARCHAR(100) COMMENT '姓名',
    mobile VARCHAR(20) COMMENT '手机号',
    qywx_userid VARCHAR(100) COMMENT '企业微信用户ID',
    status INT COMMENT '状态',
    create_time DATETIME COMMENT '创建时间',
    update_time DATETIME COMMENT '更新时间',
    source_create_time DATETIME COMMENT '源表创建时间',
    source_update_time DATETIME COMMENT '源表更新时间'
)
DUPLICATE KEY(id)
PROPERTIES (
    "replication_num" = "1",
    "in_memory" = "false",
    "storage_format" = "V2"
);

-- 9. 机构表
CREATE TABLE IF NOT EXISTS ods_org_org (
    id INT COMMENT '机构ID（主键）',
    name VARCHAR(200) COMMENT '机构名称',
    group_id INT COMMENT '地区ID',
    is_effective TINYINT COMMENT '是否有效',
    create_time DATETIME COMMENT '创建时间',
    update_time DATETIME COMMENT '更新时间',
    source_create_time DATETIME COMMENT '源表创建时间',
    source_update_time DATETIME COMMENT '源表更新时间'
)
DUPLICATE KEY(id)
PROPERTIES (
    "replication_num" = "1",
    "in_memory" = "false",
    "storage_format" = "V2"
);