-- 空调操作记录表
create table ods_bus_feedback
(
    id             STRING comment '主键',
    project_id     STRING  comment '项目ID',
    object_id      STRING comment '空间ID',
    source_type    STRING comment '反馈来源分类',
    user_id        STRING,
    user_phone     STRING,
    user_name      STRING,
    `value_type`   STRING comment '调节方式:1按调节项2按精确值',
    item_id        STRING comment '吐槽内容ID',
    value          DECIMAL(15, 5),
    create_time    STRING,
    next_open_time STRING comment '下次开启时间',
    model          STRING comment '反馈模式 1.一般模式 2.专业模式',
    duration_type  STRING comment '时效， 1.未来两个小时 2.未来24小时（不包含非定制区间）3.每天此刻都保持该温度',
    custom_plan    STRING comment '温度计划',
    curr_temp      decimal(4, 2)  comment '反馈实际温度',
    nick_name      STRING comment '反馈人微信昵称',
    result         STRING comment '控制模块获取结果是否成功',
    exe_result     STRING comment '控制模块返回结果',
    fb_temp        decimal(4, 2) comment '反馈后目标温度',
    remark         STRING comment '备注'
) COMMENT '空调操作记录表'
    PARTITIONED BY (`dt` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
    STORED AS TEXTFILE
    LOCATION
        'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/tmp/ods_bus_feedback'
    TBLPROPERTIES (
        'orc.compress'='lzo');

-- 照明操作记录
create table saga_dw.ods_bus_light_switch
(
    id STRING comment '主键',
    project_id  STRING  comment '项目ID',
    object_id   STRING  comment '空间ID',
    source_type STRING  comment '反馈来源分类',
    user_id     STRING ,
    user_phone  STRING ,
    user_name   STRING ,
    on_off      STRING comment '1开 0关',
    result      STRING comment '1成功 0失败',
    create_time STRING,
    exe_result  integer comment '执行结果'
) COMMENT '照明操作记录'
    PARTITIONED BY (`dt` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
    STORED AS TEXTFILE
    LOCATION
        'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_bus_light_switch'
    TBLPROPERTIES (
        'orc.compress'='lzo');

-- 项目信息
create table saga_dw.ods_object_data_project
(
    id  STRING comment '项目id',
    secret  STRING comment '',
    `name`  STRING comment '',
    local_id   STRING comment '',
    local_name STRING comment '',
    longitude  STRING comment '经度',
    latitude   STRING comment '纬度',
    distance   STRING comment '',
    city_id    STRING comment '',
    is_duoduo  STRING comment '',
    `status` STRING comment '0:无效，1:有效'
) COMMENT '项目信息'
    PARTITIONED BY (`dt` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
    STORED AS TEXTFILE
    LOCATION
        'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_project'
    TBLPROPERTIES (
        'orc.compress'='lzo');

-- 建筑信息
create table saga_dw.ods_object_data_building
(
    id STRING,
    name       STRING,
    local_id   STRING,
    local_name STRING,
    project_id STRING,
    `status`   STRING
) COMMENT '建筑信息'
    PARTITIONED BY (`dt` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
    STORED AS TEXTFILE
    LOCATION
        'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_building'
    TBLPROPERTIES (
        'orc.compress'='lzo');


-- 空间
create table saga_dw.ods_object_data_space
(
    id  STRING,
    name                     STRING comment '',
    local_id                 STRING comment '',
    local_name               STRING comment '',
    room_func_type           STRING comment '',
    group_id                 STRING comment '',
    bim_location             STRING comment '',
    room_q_r_code            STRING comment '',
    business_type            STRING comment '业务区域编码',
    function_code            STRING comment '',
    out_line                 STRING comment '',
    pic                      STRING comment '',
    position_img             STRING comment '',
    position_img_upload_type STRING comment '',
    position_img_create_time STRING comment '',
    project_id               STRING comment '',
    building_id              STRING comment '',
    floor_id                 STRING comment '',
    `status`                 STRING comment '',
    is_controlled            STRING comment '是否为服务定制受控对象',
    is_chiller_monitor       STRING comment '是否冷站智控监测 1:是 0：不是',
    has_window               STRING comment '是否有窗户',
    has_defect               STRING comment '是否有工程缺陷',
    region                   STRING comment '内外区',
    zone_orientation         STRING comment '朝向，东西南北',
    remark                   STRING comment '备注',
    tenant_id                STRING comment '租户id',
    room_serial              STRING comment '空间序号',
    monitor_remark           STRING comment '日常数据监控的空间备注',
    light_group_id           STRING comment '照明分组ID'
) COMMENT ''
    PARTITIONED BY (`dt` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
    STORED AS TEXTFILE
    LOCATION
        'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_space'
    TBLPROPERTIES (
        'orc.compress'='lzo');

-- 空间组
create table saga_dw.ods_object_data_space_group
(
    id  STRING,
    project_id  STRING comment '',
    name        STRING comment '',
    `status`      STRING comment '',
    `readonly`    STRING comment '',
    update_time STRING comment '',
    type        STRING comment '组类型，默认0 空调；1照明；其它...',
    update_user STRING comment '',
    remark      STRING comment ''
) COMMENT ''
    PARTITIONED BY (`dt` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
    STORED AS TEXTFILE
    LOCATION
        'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_space_group'
    TBLPROPERTIES (
        'orc.compress'='lzo');

-- 楼层
create table saga_dw.ods_object_data_floor
(
    id STRING comment '',
    name              STRING comment '',
    local_id          STRING comment '',
    local_name        STRING comment '',
    floor_map         STRING comment '',
    project_id        STRING comment '',
    building_id       STRING comment '',
    floor_sequence_id integer comment '',
    `status`          STRING comment ''
) COMMENT ''
    PARTITIONED BY (`dt` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
    STORED AS TEXTFILE
    LOCATION
        'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_floor'
    TBLPROPERTIES (
        'orc.compress'='lzo');

-- 设备
create table saga_dw.ods_object_data_equipment
(
    id  STRING comment '',
    name               STRING comment '',
    local_id           STRING comment '',
    local_name         STRING comment '',
    major_category     STRING comment '',
    system_category    STRING comment '',
    equipment_category STRING comment '',
    bim_location       STRING comment '',
    run_status         STRING comment '',
    equip_q_r_code     STRING comment '',
    install_location   STRING comment '',
    position_img       STRING comment '',
    position_img_upload_type STRING comment '',
    position_img_create_time STRING comment '',
    project_id               STRING comment '',
    building_id              STRING comment '',
    `status`                   STRING comment ''
) COMMENT ''
    PARTITIONED BY (`dt` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
    STORED AS TEXTFILE
    LOCATION
        'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_equipment'
    TBLPROPERTIES (
        'orc.compress'='lzo');

-- 设备信息
create table saga_dw.ods_object_data_equipment_infos
(
    id STRING comment '',
    project_id STRING comment '',
    category   STRING comment '',
    infos  STRING comment '',
    body   STRING comment ''
) COMMENT ''
    PARTITIONED BY (`dt` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
    STORED AS TEXTFILE
    LOCATION
        'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_equipment_infos'
    TBLPROPERTIES (
        'orc.compress'='lzo');

-- 关系表
create table saga_dw.ods_object_data_relation
(
    id STRING comment '',
    project_id STRING comment '',
    graph_id   STRING comment '',
    from_id    STRING comment '',
    rel_type   STRING comment '',
    to_id      STRING comment '',
    infos      STRING comment ''
) COMMENT ''
    PARTITIONED BY (`dt` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
    STORED AS TEXTFILE
    LOCATION
        'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_relation'
    TBLPROPERTIES (
        'orc.compress'='lzo');

-- 租户
create table saga_dw.ods_object_data_tenant
(
    id  STRING comment '',
    name       STRING comment '',
    local_id   STRING comment '',
    local_name STRING comment '',
    project_id STRING comment '',
    `status`   STRING comment ''
) COMMENT ''
    PARTITIONED BY (`dt` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
    STORED AS TEXTFILE
    LOCATION
        'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_object_data_tenant'
    TBLPROPERTIES (
        'orc.compress'='lzo');


create table saga_dw.ods_custom_content_real
(
    id             STRING comment '',
    project_id     STRING comment '',
    space_id       STRING comment '',
    group_id       STRING comment '',
    `date`         STRING comment '',
    `time`         STRING comment '',
    item           STRING comment '',
    time_point     STRING comment '',
    fixed_value    double comment '',
    max_value      double comment '',
    min_value      double comment '',
    create_user_id STRING comment '',
    create_time    STRING comment '',
    update_user_id STRING comment '',
    update_time    STRING comment '',
    update_source  STRING comment '更新来源  0 初始化配置  1、反馈算法调整季节  2、反馈算法调整日期 3、临时服务定制 4、提前开机  5、小程序捏曲线',
    is_beforehand  STRING comment '提前开机1 不提前开机0',
    custom_type    STRING comment '0 初始化 1 算法季节 2算法日期 3 app 4 临时 5灯加班    关机情况有初始服务定制默认0'
) COMMENT ''
    PARTITIONED BY (`dt` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
    STORED AS TEXTFILE
    LOCATION
        'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_custom_content_real'
    TBLPROPERTIES (
        'orc.compress'='lzo');


create table saga_dw.ods_custom_persist_target
(
    id              STRING comment '',
    project_id      STRING comment '',
    space_id        STRING comment '',
    `date`          STRING comment '',
    `time`          STRING comment '',
    temperature_max double comment '',
    temperature_min double comment '',
    humidity_max    double comment '',
    humidity_min    double comment '',
    co2_max         double comment '',
    co2_min         double comment '',
    hcho_max        double comment '',
    hcho_min        double comment '',
    pm25_max        double comment '',
    pm25_min        double comment '',
    sun_fixed       double comment '',
    create_time     STRING comment '',
    is_beforehand   STRING comment ''
) COMMENT ''
    PARTITIONED BY (`dt` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
    STORED AS TEXTFILE
    LOCATION
        'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_custom_persist_target'
    TBLPROPERTIES (
        'orc.compress'='lzo');

-- 用户信息
create table saga_dw.ods_user
(
    id STRING comment '',
    name             STRING comment '用户名   游客用户没有名字',
    company_id       STRING comment '所属企业ID',
    type             STRING comment '类型：1，普通租户；2，租户行政；3，运营方；4，访客',
    `status`         STRING comment '用户状态【0:未入驻/1:审核中/2:已入驻激活/3:已入驻待激活/4:已入驻冻结/5:已入驻作废】',
    create_date      STRING comment '创建时间',
    update_date      STRING comment '更新时间',
    dep_name         STRING comment '',
    job              STRING comment '职务',
    phone            STRING comment '',
    manage_user_type STRING comment '成员类型：1-正式成员, 2-临时成员',
    first_use_time   STRING comment '首次使用时间（入住、激活时的时间）'
) COMMENT ''
    PARTITIONED BY (`dt` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
    STORED AS TEXTFILE
    LOCATION
        'hdfs://sagaCluster:8020/warehouse/saga_dw/ods/ods_user'
    TBLPROPERTIES (
        'orc.compress'='lzo');