package com.iot.scheduler.repository;

import com.iot.scheduler.entity.DeviceState;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * 设备状态数据访问层
 */
@Repository
public class DeviceStateRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private final RowMapper<DeviceState> rowMapper = new RowMapper<>() {
        @Override
        public DeviceState mapRow(ResultSet rs, int rowNum) throws SQLException {
            DeviceState device = new DeviceState();
            device.setProductType(rs.getString("所属产品"));
            device.setEquipmentNo(rs.getString("设备名称"));
            Object stateObj = rs.getObject("状态");
            if (stateObj != null) {
                device.setState(((Number) stateObj).intValue());
            }
            return device;
        }
    };

    /**
     * 查询所有设备状态
     */
    public List<DeviceState> findAllDeviceStates() {
        String sql = """
            select
              de.type as 所属产品,
              de.name as 设备名称,
              CASE
                WHEN attrA0.bool_v != true THEN 0
                WHEN tk.long_v = 0 THEN 2
                WHEN tk.long_v = 1 THEN 3
              END as 状态
            from
              device de
              left join ts_kv_latest tk on de.id = tk.entity_id
              and tk.key = 103
              LEFT JOIN attribute_kv attrA0 on de.id = attrA0.entity_id
              AND attrA0.entity_type = 'DEVICE'
              AND attrA0.attribute_key = 'active'
            where
              de.tenant_id = '606bd430-1b6d-11f0-916e-45391b6ab681'
              and (
                de.device_profile_id = '3bedb640-1f29-11f0-95bb-f76fd8ecf988'
                or de.device_profile_id = '47f59320-1f26-11f0-95bb-f76fd8ecf988'
              )

            UNION ALL

            select
              de.type as 所属产品,
              de.name as 设备名称,
              CASE
                WHEN attrA0.bool_v != true THEN 0
                WHEN tk.long_v = 0 THEN 2
                WHEN tk.long_v = 1 THEN 3
              END as 状态
            from
              device de
              left join ts_kv_latest tk on de.id = tk.entity_id
              and tk.key = 103
              LEFT JOIN attribute_kv attrA0 on de.id = attrA0.entity_id
              AND attrA0.entity_type = 'DEVICE'
              AND attrA0.attribute_key = 'active'
            where
              de.tenant_id = '606bd430-1b6d-11f0-916e-45391b6ab681'
              and de.device_profile_id = 'ee71f340-1f2d-11f0-95bb-f76fd8ecf988'

            UNION ALL

            select
              de.type as 所属产品,
              de.name as 设备名称,
              CASE
                WHEN attrA0.bool_v != true THEN 0
                WHEN MAX(tk.long_v) = 0 THEN 2
                WHEN MAX(tk.long_v) = 1 THEN 3
              END as 状态
            from
              device de
              left join ts_kv_latest tk on de.id = tk.entity_id
              and tk.key IN (103, 102, 101, 100)
              LEFT JOIN attribute_kv attrA0 on de.id = attrA0.entity_id
              AND attrA0.entity_type = 'DEVICE'
              AND attrA0.attribute_key = 'active'
            where
              de.tenant_id = '606bd430-1b6d-11f0-916e-45391b6ab681'
              and de.device_profile_id = 'a5fbfe30-1f2d-11f0-95bb-f76fd8ecf988'
            GROUP BY
              de.name,
              de.type,
              attrA0.bool_v

            UNION ALL

            select
              de.type as 所属产品,
              de.name as 设备名称,
              CASE
                WHEN attrA0.bool_v != true THEN 0
                WHEN tk1.str_v != '***(Others)' THEN 1
                WHEN tk.str_v = 'STaRT' THEN 3
                ELSE 2
              END as 状态
            from
              device de
              left join ts_kv_latest tk on de.id = tk.entity_id
              and tk.key = 231
              left join ts_kv_latest tk1 on de.id = tk1.entity_id
              and tk1.key = 221
              LEFT JOIN attribute_kv attrA0 on de.id = attrA0.entity_id
              AND attrA0.entity_type = 'DEVICE'
              AND attrA0.attribute_key = 'active'
            where
              de.tenant_id = '606bd430-1b6d-11f0-916e-45391b6ab681'
              and de.device_profile_id = 'a3a188e0-1f2c-11f0-95bb-f76fd8ecf988'

            UNION ALL

            select
              de.type as 所属产品,
              de.name as 设备名称,
              CASE
                WHEN attrA0.bool_v != true THEN 0
                WHEN tk1.long_v = 1 THEN 1
                WHEN tk.long_v = 1 THEN 3
                ELSE 2
              END as 状态
            from
              device de
              left join ts_kv_latest tk on de.id = tk.entity_id
              and tk.key = 81
              left join ts_kv_latest tk1 on de.id = tk1.entity_id
              and tk1.key = 119
              LEFT JOIN attribute_kv attrA0 on de.id = attrA0.entity_id
              AND attrA0.entity_type = 'DEVICE'
              AND attrA0.attribute_key = 'active'
            where
              de.tenant_id = '606bd430-1b6d-11f0-916e-45391b6ab681'
              and de.device_profile_id = '8c247660-1f2b-11f0-95bb-f76fd8ecf988'

            UNION ALL

            select
              de.type as 所属产品,
              de.name as 设备名称,
              CASE
                WHEN attrA0.bool_v != true THEN 0
                WHEN tk1.long_v = 1 THEN 1
                WHEN tk.long_v = 1 THEN 3
                ELSE 2
              END as 状态
            from
              device de
              left join ts_kv_latest tk on de.id = tk.entity_id
              and tk.key = 86
              left join ts_kv_latest tk1 on de.id = tk1.entity_id
              and tk1.key = 147
              LEFT JOIN attribute_kv attrA0 on de.id = attrA0.entity_id
              AND attrA0.entity_type = 'DEVICE'
              AND attrA0.attribute_key = 'active'
            where
              de.tenant_id = '606bd430-1b6d-11f0-916e-45391b6ab681'
              and de.device_profile_id = '312d1be0-1f2b-11f0-95bb-f76fd8ecf988'

            UNION ALL

            select
              de.type as 所属产品,
              de.name as 设备名称,
              CASE
                WHEN attrA0.bool_v != true THEN 0
                WHEN tk1.long_v = 1 THEN 1
                WHEN tk.long_v = 1 THEN 3
                ELSE 2
              END as 状态
            from
              device de
              left join ts_kv_latest tk on de.id = tk.entity_id
              and tk.key = 97
              left join ts_kv_latest tk1 on de.id = tk1.entity_id
              and tk1.key = 111
              LEFT JOIN attribute_kv attrA0 on de.id = attrA0.entity_id
              AND attrA0.entity_type = 'DEVICE'
              AND attrA0.attribute_key = 'active'
            where
              de.tenant_id = '606bd430-1b6d-11f0-916e-45391b6ab681'
              and de.device_profile_id = 'aaee40f0-1f24-11f0-95bb-f76fd8ecf988'

            UNION ALL

            -- 子设备固安力冲压机
            select
              de.type as 所属产品,
              de.name as 设备名称,
              CASE
                WHEN attrA0.bool_v != true THEN 0
                WHEN tk1.long_v != 1 and tk.long_v = 1 THEN 3
                WHEN tk1.long_v != 1 and tk.long_v = 0 THEN 2
                WHEN tk1.long_v = 1 THEN 1
              END as 状态
            from
              device de
              left join ts_kv_latest tk on de.id = tk.entity_id
              and tk.key = 98
              left join ts_kv_latest tk1 on de.id = tk1.entity_id
              and tk1.key = 103
              LEFT JOIN attribute_kv attrA0 on de.id = attrA0.entity_id
              AND attrA0.entity_type = 'DEVICE'
              AND attrA0.attribute_key = 'active'
            where
              de.tenant_id = '606bd430-1b6d-11f0-916e-45391b6ab681'
              and de.device_profile_id = '01e3db90-1f20-11f0-95bb-f76fd8ecf988'

            UNION ALL

            -- 子设备海天注塑机
            select
              de.type as 所属产品,
              de.name as 设备名称,
              CASE
                WHEN attrA0.bool_v != true THEN 0
                WHEN tk.long_v = 1 THEN 2
                WHEN tk.long_v = 2 THEN 3
              END as 状态
            from
              device de
              left join ts_kv_latest tk on de.id = tk.entity_id
              and tk.key = 65
              LEFT JOIN attribute_kv attrA0 on de.id = attrA0.entity_id
              AND attrA0.entity_type = 'DEVICE'
              AND attrA0.attribute_key = 'active'
            where
              de.tenant_id = '606bd430-1b6d-11f0-916e-45391b6ab681'
              and de.device_profile_id = 'ffe73360-1e7e-11f0-95bb-f76fd8ecf988'
            """;

        return jdbcTemplate.query(sql, rowMapper);
    }
}