-- 第三方平台接入框架

CREATE TABLE IF NOT EXISTS `iot_integration_platform` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `platform_code` varchar(64) NOT NULL COMMENT '平台编码',
  `platform_name` varchar(128) NOT NULL COMMENT '平台名称',
  `base_url` varchar(512) NOT NULL COMMENT '基础地址',
  `auth_type` varchar(64) DEFAULT NULL COMMENT '认证类型',
  `credential_json` json DEFAULT NULL COMMENT '凭证配置',
  `status` tinyint DEFAULT 1 COMMENT '状态：0=停用 1=启用',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志',
  `create_by` varchar(64) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_integration_platform_code` (`platform_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='第三方平台接入配置';

CREATE TABLE IF NOT EXISTS `iot_integration_api` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `platform_id` bigint NOT NULL COMMENT '平台ID',
  `api_code` varchar(64) NOT NULL COMMENT '接口编码',
  `api_name` varchar(128) NOT NULL COMMENT '接口名称',
  `method` varchar(16) NOT NULL COMMENT '请求方法',
  `uri` varchar(512) NOT NULL COMMENT '接口URI',
  `content_type` varchar(128) DEFAULT 'application/json' COMMENT 'Content-Type',
  `headers_json` json DEFAULT NULL COMMENT '请求头模板',
  `query_json` json DEFAULT NULL COMMENT 'Query模板',
  `body_json` json DEFAULT NULL COMMENT 'Body模板',
  `response_path` varchar(256) DEFAULT NULL COMMENT '返回路径',
  `depends_on` varchar(64) DEFAULT NULL COMMENT '依赖接口编码',
  `sort_order` int DEFAULT 0 COMMENT '排序',
  `status` tinyint DEFAULT 1 COMMENT '状态：0=停用 1=启用',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志',
  `create_by` varchar(64) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_integration_api_code` (`platform_id`, `api_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='第三方平台接口定义';

CREATE TABLE IF NOT EXISTS `iot_integration_device_mapping` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `platform_id` bigint NOT NULL COMMENT '平台ID',
  `external_device_id` varchar(128) NOT NULL COMMENT '第三方设备ID',
  `external_device_name` varchar(128) DEFAULT NULL COMMENT '第三方设备名称',
  `product_id` bigint NOT NULL COMMENT '本系统产品ID',
  `serial_number` varchar(64) NOT NULL COMMENT '本系统设备编号',
  `identifier_mapping_json` json DEFAULT NULL COMMENT '指标映射 sourceField -> identifier',
  `status` tinyint DEFAULT 1 COMMENT '状态：0=停用 1=启用',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志',
  `create_by` varchar(64) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_integration_device_mapping` (`platform_id`, `external_device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='第三方设备映射';

CREATE TABLE IF NOT EXISTS `iot_integration_log` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `platform_id` bigint DEFAULT NULL COMMENT '平台ID',
  `platform_code` varchar(64) NOT NULL COMMENT '平台编码',
  `flow_code` varchar(64) NOT NULL COMMENT '流程编码',
  `request_id` varchar(64) NOT NULL COMMENT '请求ID',
  `start_time` datetime DEFAULT NULL COMMENT '开始时间',
  `end_time` datetime DEFAULT NULL COMMENT '结束时间',
  `status` tinyint DEFAULT 2 COMMENT '状态：0=失败 1=成功 2=执行中',
  `success_count` int DEFAULT 0 COMMENT '成功数量',
  `fail_count` int DEFAULT 0 COMMENT '失败数量',
  `error_msg` varchar(1000) DEFAULT NULL COMMENT '错误信息',
  `detail_json` json DEFAULT NULL COMMENT '详情',
  `create_by` varchar(64) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_integration_log_request` (`request_id`),
  KEY `idx_integration_log_platform` (`platform_code`, `start_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='第三方平台接入执行日志';

INSERT INTO `iot_integration_platform`
(`platform_code`, `platform_name`, `base_url`, `auth_type`, `credential_json`, `status`, `del_flag`, `create_by`, `create_time`)
VALUES
('DCP', 'DCP轴力计平台', 'http://47.108.182.132:8200', 'PASSWORD_TOKEN',
 JSON_OBJECT('grant_type','password','scope','DACP','client_id','DACP_App','userName','ceshi','password','12345678',
             'openClientId','DCP_Open_App','openUserName','open_user','openPassword','change-me'),
 0, '0', 'admin', NOW())
ON DUPLICATE KEY UPDATE `platform_name` = VALUES(`platform_name`);

-- 启用前请按现场账号修改 credential_json 中的 DCP 上游账号和 openClientId/openUserName/openPassword，并将 status 改为 1。
-- 若需要第三方应用匿名触发同步，请在 credential_json 中增加 openApiKey：
-- UPDATE iot_integration_platform SET credential_json = JSON_SET(credential_json, '$.openApiKey', 'replace-with-random-secret') WHERE platform_code = 'DCP';
--
-- 设备映射示例，需替换 product_id、serial_number、external_device_id：
-- INSERT INTO iot_integration_device_mapping
-- (platform_id, external_device_id, external_device_name, product_id, serial_number, identifier_mapping_json, status, del_flag, create_by, create_time)
-- SELECT id, 'DCP_DEVICE_ID', 'DCP测点名称', 1, 'FASTBEE_SERIAL_NUMBER',
--        JSON_OBJECT('e','axialForce','temperature','temperature','ct','ct','pt','pt'),
--        1, '0', 'admin', NOW()
-- FROM iot_integration_platform WHERE platform_code = 'DCP';

INSERT INTO `iot_integration_api`
(`platform_id`, `api_code`, `api_name`, `method`, `uri`, `content_type`, `depends_on`, `sort_order`, `status`, `del_flag`, `create_by`, `create_time`)
SELECT p.id, 'dcp_token', '获取访问凭证', 'POST', '/connect/token', 'application/x-www-form-urlencoded', NULL, 1, 1, '0', 'admin', NOW()
FROM `iot_integration_platform` p WHERE p.platform_code = 'DCP'
ON DUPLICATE KEY UPDATE `uri` = VALUES(`uri`);

INSERT INTO `iot_integration_api`
(`platform_id`, `api_code`, `api_name`, `method`, `uri`, `content_type`, `depends_on`, `sort_order`, `status`, `del_flag`, `create_by`, `create_time`)
SELECT p.id, 'dcp_user_id', '获取用户信息', 'GET', '/api/app/user/user-id', 'application/json', 'dcp_token', 2, 1, '0', 'admin', NOW()
FROM `iot_integration_platform` p WHERE p.platform_code = 'DCP'
ON DUPLICATE KEY UPDATE `uri` = VALUES(`uri`);

INSERT INTO `iot_integration_api`
(`platform_id`, `api_code`, `api_name`, `method`, `uri`, `content_type`, `depends_on`, `sort_order`, `status`, `del_flag`, `create_by`, `create_time`)
SELECT p.id, 'dcp_project_list', '获取项目信息', 'GET', '/api/app/project/by-user-id/{userId}', 'application/json', 'dcp_user_id', 3, 1, '0', 'admin', NOW()
FROM `iot_integration_platform` p WHERE p.platform_code = 'DCP'
ON DUPLICATE KEY UPDATE `uri` = VALUES(`uri`);

INSERT INTO `iot_integration_api`
(`platform_id`, `api_code`, `api_name`, `method`, `uri`, `content_type`, `depends_on`, `sort_order`, `status`, `del_flag`, `create_by`, `create_time`)
SELECT p.id, 'dcp_stress_sensor_list', '获取项目轴力计测点', 'POST', '/api/app/stress-sensor/by-project-id/{projectId}', 'application/json', 'dcp_project_list', 4, 1, '0', 'admin', NOW()
FROM `iot_integration_platform` p WHERE p.platform_code = 'DCP'
ON DUPLICATE KEY UPDATE `uri` = VALUES(`uri`);

INSERT INTO `iot_integration_api`
(`platform_id`, `api_code`, `api_name`, `method`, `uri`, `content_type`, `depends_on`, `sort_order`, `status`, `del_flag`, `create_by`, `create_time`)
SELECT p.id, 'dcp_stress_sensor_data', '批量获取轴力计数据', 'POST', '/api/app/inclino-data/load-dataset-by-device-ids', 'application/json', 'dcp_stress_sensor_list', 5, 1, '0', 'admin', NOW()
FROM `iot_integration_platform` p WHERE p.platform_code = 'DCP'
ON DUPLICATE KEY UPDATE `uri` = VALUES(`uri`);

-- 菜单权限。若目标库已有相同 menu_id，请按现场菜单ID调整后执行。
INSERT INTO `sys_menu`
(`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES
(3440, '平台接入', 3051, 9, 'integration', 'iot/integration/index', NULL, 1, 0, 'C', '0', '0', 'iot:integration:list', 'api', 'admin', NOW(), '', NULL, '第三方平台接入菜单')
ON DUPLICATE KEY UPDATE `menu_name` = VALUES(`menu_name`), `component` = VALUES(`component`), `visible` = VALUES(`visible`), `perms` = VALUES(`perms`);

INSERT INTO `sys_menu`
(`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES
(3441, '平台接入查询', 3440, 1, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:integration:list', '#', 'admin', NOW(), '', NULL, '')
ON DUPLICATE KEY UPDATE `perms` = VALUES(`perms`);

INSERT INTO `sys_menu`
(`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES
(3442, '平台数据同步', 3440, 2, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:integration:sync', '#', 'admin', NOW(), '', NULL, '')
ON DUPLICATE KEY UPDATE `perms` = VALUES(`perms`);

INSERT INTO `sys_menu`
(`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES
(3443, '平台接入新增', 3440, 3, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:integration:add', '#', 'admin', NOW(), '', NULL, '')
ON DUPLICATE KEY UPDATE `perms` = VALUES(`perms`);

INSERT INTO `sys_menu`
(`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES
(3444, '平台接入修改', 3440, 4, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:integration:edit', '#', 'admin', NOW(), '', NULL, '')
ON DUPLICATE KEY UPDATE `perms` = VALUES(`perms`);

INSERT INTO `sys_menu`
(`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES
(3445, '平台接入删除', 3440, 5, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:integration:remove', '#', 'admin', NOW(), '', NULL, '')
ON DUPLICATE KEY UPDATE `perms` = VALUES(`perms`);