CREATE DATABASE IF NOT EXISTS recycle DEFAULT CHARSET utf8mb4;
USE recycle;

/* ================= 分类 ================= */
CREATE TABLE t_chx_category (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  category_id BIGINT NOT NULL,
  name VARCHAR(60) NOT NULL,
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

/* ================= 机型 ================= */
CREATE TABLE t_machine (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(32),
  type_id BIGINT,
  type_name VARCHAR(100),
  brand_id BIGINT,
  brand_name VARCHAR(100),
  machine_id BIGINT,
  name VARCHAR(100),
  shrink_name VARCHAR(100),
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

/* ================= 机型模板 ================= */
CREATE TABLE machine_temp (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  machine_id BIGINT,
  temp_type VARCHAR(32) DEFAULT '00',
  estimate_packet TEXT,
  base_template_id BIGINT NULL,
  is_custom TINYINT DEFAULT 0,
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
  update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

/* ================= 设备情况项 ================= */
CREATE TABLE release_option (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  step_id BIGINT COMMENT '1物品信息 2成色情况 3功能情况',
  step_name VARCHAR(50),
  option_key_id BIGINT,
  option_key_name VARCHAR(60),
  option_id BIGINT,
  option_name VARCHAR(60),
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP
  update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


/* ================= step1设备选项 ================= */

CREATE TABLE step1_attr (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,

  base_template_id BIGINT NOT NULL COMMENT '基础模板ID：99181 / 99198 / 99197',
  step TINYINT DEFAULT 1 COMMENT '固定为 step=1',

  attr_key VARCHAR(64) NOT NULL COMMENT 'capacity / color / channel / warranty',
  attr_name VARCHAR(64) NOT NULL COMMENT '容量 / 颜色 / 购买渠道 / 保修',

  value_id BIGINT NOT NULL COMMENT '选项值ID（用于JSON）',
  value_name VARCHAR(64) NOT NULL COMMENT '选项值名称',

  is_default TINYINT DEFAULT 0 COMMENT '是否默认',
  is_normal TINYINT DEFAULT 1 COMMENT '是否正常值',

  required TINYINT DEFAULT 1 COMMENT '是否必填',
  is_multi TINYINT DEFAULT 0 COMMENT '是否多选',

  sort_order INT DEFAULT 0,
  is_base_price_attr TINYINT DEFAULT 0 COMMENT '是否用于选择基准价',
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
  update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  KEY idx_tpl (base_template_id),
  KEY idx_attr (attr_key)
) COMMENT='step1 模板属性表';


/* ================= 基础价格 ================= */
CREATE TABLE machine_base_price (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  machine_id BIGINT,
  capacity VARCHAR(32),
  base_price DECIMAL(10,2),
  UNIQUE KEY uk_machine_capacity (machine_id, capacity)
);

/* ================= 扣减系数 ================= */
CREATE TABLE price_option_factor (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  option_id BIGINT,
  factor DECIMAL(5,4) DEFAULT 1.0000,
  absolute_deduct DECIMAL(10,2) DEFAULT 0
);

/* ================= 调节系数 ================= */
CREATE TABLE price_adjust_factor (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  level VARCHAR(32),
  ref_id BIGINT,
  factor DECIMAL(5,4) DEFAULT 1.0000
);

/* ================  基础模板 ================= */
CREATE TABLE base_estimate_template (
  id BIGINT PRIMARY KEY COMMENT '99181 / 99198 / 99197',
  name VARCHAR(64) NOT NULL,
  template_json JSON NOT NULL,
  template_version INT DEFAULT 1,
  remark VARCHAR(255),
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
  update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);



/* ================= 估价版本 ================= */
CREATE TABLE estimate_version (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  machine_id BIGINT,
  version_no VARCHAR(32),
  content TEXT,
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

/* ================= 估价记录 ================= */
CREATE TABLE estimate_record (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  machine_id BIGINT,
  capacity VARCHAR(32),
  option_ids TEXT,
  final_price DECIMAL(10,2),
  version_no VARCHAR(32),
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);