-- ============================================================
-- 产品管理模块（Product & TSL & OTA）
-- 新增产品、物模型（属性/事件/服务）、固件、OTA 任务；
-- device 表新增 product_id（保留原 model/manufacturer 作为兼容快照，后续再废弃）。
-- ============================================================

CREATE TABLE IF NOT EXISTS `product` (
  `id`                   BIGINT       NOT NULL COMMENT '主键',
  `product_key`          VARCHAR(64)  NOT NULL COMMENT '产品业务编码',
  `name`                 VARCHAR(128) NOT NULL COMMENT '产品名称',
  `category`             VARCHAR(32)  DEFAULT '' COMMENT '品类(light/switch/ac/security...)',
  `manufacturer`         VARCHAR(64)  DEFAULT '' COMMENT '厂商',
  `model`                VARCHAR(64)  DEFAULT '' COMMENT '型号',
  `platform`             VARCHAR(32)  DEFAULT 'tuya' COMMENT '接入平台(tuya/local/matter)',
  `platform_product_id`  VARCHAR(64)  DEFAULT '' COMMENT '平台产品ID(如涂鸦productId)',
  `connect_type`         VARCHAR(32)  DEFAULT '' COMMENT '联网方式(wifi/ble/zigbee/matter)',
  `net_config_type`      VARCHAR(32)  DEFAULT '' COMMENT '配网方式',
  `icon`                 VARCHAR(255) DEFAULT '' COMMENT '图标',
  `description`          VARCHAR(512) DEFAULT '' COMMENT '说明',
  `status`               VARCHAR(16)  DEFAULT 'draft' COMMENT 'draft/published',
  `spec_version`         INT          DEFAULT 1 COMMENT '物模型版本',
  `tenant_id`            BIGINT       DEFAULT NULL,
  `create_time`          DATETIME     DEFAULT NULL,
  `update_time`          DATETIME     DEFAULT NULL,
  `deleted`              INT          DEFAULT 0,
  `create_by`            BIGINT       DEFAULT NULL,
  `update_by`            BIGINT       DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_product_key` (`product_key`),
  KEY `idx_platform_product` (`platform`, `platform_product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='产品表';

CREATE TABLE IF NOT EXISTS `product_property` (
  `id`            BIGINT       NOT NULL COMMENT '主键',
  `product_id`    BIGINT       NOT NULL COMMENT '产品ID',
  `code`          VARCHAR(64)  NOT NULL COMMENT '功能点编码(如 switch_1)',
  `name`          VARCHAR(128) DEFAULT '' COMMENT '名称',
  `access`        VARCHAR(16)  DEFAULT 'read' COMMENT 'read/write/notify',
  `type`          VARCHAR(16)  DEFAULT 'bool' COMMENT 'bool/int/enum/float/string/json',
  `min`           VARCHAR(32)  DEFAULT '' COMMENT '最小值',
  `max`           VARCHAR(32)  DEFAULT '' COMMENT '最大值',
  `step`          VARCHAR(32)  DEFAULT '' COMMENT '步长',
  `unit`          VARCHAR(32)  DEFAULT '' COMMENT '单位',
  `enum_list`     TEXT         COMMENT '枚举值(JSON数组)',
  `dp_id`         VARCHAR(32)  DEFAULT '' COMMENT '平台DP点映射(如涂鸦DP)',
  `default_value` VARCHAR(128) DEFAULT '' COMMENT '默认值',
  `sort`          INT          DEFAULT 0 COMMENT '排序',
  `tenant_id`     BIGINT       DEFAULT NULL,
  `create_time`   DATETIME     DEFAULT NULL,
  `update_time`   DATETIME     DEFAULT NULL,
  `deleted`       INT          DEFAULT 0,
  `create_by`     BIGINT       DEFAULT NULL,
  `update_by`     BIGINT       DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_product_property` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='产品属性(物模型)';

CREATE TABLE IF NOT EXISTS `product_event` (
  `id`          BIGINT       NOT NULL COMMENT '主键',
  `product_id`  BIGINT       NOT NULL COMMENT '产品ID',
  `code`        VARCHAR(64)  NOT NULL COMMENT '事件编码',
  `name`        VARCHAR(128) DEFAULT '' COMMENT '名称',
  `type`        VARCHAR(16)  DEFAULT 'info' COMMENT 'info/alert/fault',
  `params`      TEXT         COMMENT '事件参数(JSON)',
  `tenant_id`   BIGINT       DEFAULT NULL,
  `create_time` DATETIME     DEFAULT NULL,
  `update_time` DATETIME     DEFAULT NULL,
  `deleted`     INT          DEFAULT 0,
  `create_by`   BIGINT       DEFAULT NULL,
  `update_by`   BIGINT       DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_product_event` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='产品事件(物模型)';

CREATE TABLE IF NOT EXISTS `product_action` (
  `id`          BIGINT       NOT NULL COMMENT '主键',
  `product_id`  BIGINT       NOT NULL COMMENT '产品ID',
  `code`        VARCHAR(64)  NOT NULL COMMENT '服务编码',
  `name`        VARCHAR(128) DEFAULT '' COMMENT '名称',
  `input`       TEXT         COMMENT '输入参数(JSON)',
  `output`      TEXT         COMMENT '输出参数(JSON)',
  `tenant_id`   BIGINT       DEFAULT NULL,
  `create_time` DATETIME     DEFAULT NULL,
  `update_time` DATETIME     DEFAULT NULL,
  `deleted`     INT          DEFAULT 0,
  `create_by`   BIGINT       DEFAULT NULL,
  `update_by`   BIGINT       DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_product_action` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='产品服务(物模型)';

CREATE TABLE IF NOT EXISTS `firmware` (
  `id`           BIGINT       NOT NULL COMMENT '主键',
  `product_id`   BIGINT       NOT NULL COMMENT '产品ID',
  `version`      VARCHAR(64)  NOT NULL COMMENT '固件版本',
  `file_url`     VARCHAR(512) DEFAULT '' COMMENT '升级包地址',
  `file_md5`     VARCHAR(64)  DEFAULT '' COMMENT '校验MD5',
  `size`         BIGINT       DEFAULT 0 COMMENT '大小(字节)',
  `release_note` VARCHAR(512) DEFAULT '' COMMENT '更新说明',
  `is_latest`    INT          DEFAULT 0 COMMENT '是否最新(0/1)',
  `status`       VARCHAR(16)  DEFAULT 'draft' COMMENT 'draft/published',
  `tenant_id`    BIGINT       DEFAULT NULL,
  `create_time`  DATETIME     DEFAULT NULL,
  `update_time`  DATETIME     DEFAULT NULL,
  `deleted`      INT          DEFAULT 0,
  `create_by`    BIGINT       DEFAULT NULL,
  `update_by`    BIGINT       DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_firmware_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='固件表';

CREATE TABLE IF NOT EXISTS `ota_task` (
  `id`              BIGINT       NOT NULL COMMENT '主键',
  `product_id`      BIGINT       NOT NULL COMMENT '产品ID',
  `firmware_id`     BIGINT       DEFAULT NULL COMMENT '目标固件ID',
  `target_version`  VARCHAR(64)  DEFAULT '' COMMENT '目标版本',
  `scope`           VARCHAR(16)  DEFAULT 'all' COMMENT 'all/designated/gray',
  `device_ids`      TEXT         COMMENT '指定设备ID(JSON数组)',
  `status`          VARCHAR(16)  DEFAULT 'running' COMMENT 'running/done/paused/failed',
  `success_count`   INT          DEFAULT 0 COMMENT '成功数',
  `fail_count`      INT          DEFAULT 0 COMMENT '失败数',
  `tenant_id`       BIGINT       DEFAULT NULL,
  `create_time`     DATETIME     DEFAULT NULL,
  `update_time`     DATETIME     DEFAULT NULL,
  `deleted`         INT          DEFAULT 0,
  `create_by`       BIGINT       DEFAULT NULL,
  `update_by`       BIGINT       DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_ota_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='OTA升级任务';

ALTER TABLE `device` ADD COLUMN `product_id` BIGINT DEFAULT NULL COMMENT '所属产品ID' AFTER `home_id`;