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),
  series_id BIGINT NULL COMMENT '系列ID（Mate / P / nova 等）',
  series_name VARCHAR(100) NULL COMMENT '系列名称',
  shrink_name VARCHAR(100),
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
  update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE 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成色情况 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设备选项 ================= 
 drop base_template_id BIGINT NOT NULL COMMENT '基础模板ID：99181 / 99198 / 99197',

*/

CREATE TABLE step1_attr (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  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_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,
  base_template_id BIGINT NOT NULL COMMENT '所属基础模板',
  option_id BIGINT,
  factor DECIMAL(5,4) DEFAULT 1.0000,
  absolute_deduct DECIMAL(10,2) DEFAULT 0,
  group_code VARCHAR(32) NULL COMMENT '损伤分组，如 screen_display / touch / repair / camera',
  severity_level TINYINT DEFAULT 1 COMMENT '严重等级：1轻 2中 3重（用于排序）',
  sub_weight DECIMAL(5,2) DEFAULT 0.30 COMMENT '作为次瑕疵时的折扣系数',
  is_special TINYINT DEFAULT 0 COMMENT '是否特殊选项（用于特殊规则组）',
  repair_level TINYINT DEFAULT 0 COMMENT '维修分级：0非维修 1次要 2重要 3核心',
  priority INT DEFAULT 0 COMMENT '覆盖与排序优先级',
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
  update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_tpl_option (base_template_id, option_id)
);

-- ALTER TABLE price_option_factor
-- ADD COLUMN group_code VARCHAR(32) NULL COMMENT '损伤分组，如 screen_display / touch / repair / camera',
-- ADD COLUMN severity_level TINYINT DEFAULT 1 COMMENT '严重等级：1轻 2中 3重（用于排序）',
-- ADD COLUMN sub_weight DECIMAL(5,2) DEFAULT 0.30 COMMENT '作为次瑕疵时的折扣系数',
-- ADD COLUMN is_special TINYINT DEFAULT 0 COMMENT '是否特殊选项（用于特殊规则组）',
-- ADD COLUMN repair_level TINYINT DEFAULT 0 COMMENT '维修分级：0非维修 1次要 2重要 3核心',
-- ADD COLUMN priority INT DEFAULT 0 COMMENT '覆盖与排序优先级';
-- ALTER TABLE price_option_factor
-- ADD COLUMN base_template_id BIGINT NOT NULL COMMENT '所属基础模板';

CREATE TABLE price_damage_group (
  group_code VARCHAR(32) PRIMARY KEY,
  group_name VARCHAR(64),
  cap_ratio DECIMAL(5,2) DEFAULT 1.15 COMMENT '组封顶系数（主瑕疵×ratio）',
  group_weight DECIMAL(5,2) DEFAULT 1.00 COMMENT '整组权重'
);


CREATE TABLE price_group_override (
  trigger_group_code VARCHAR(32),
  target_group_code VARCHAR(32),
  override_type VARCHAR(16) COMMENT 'skip / weight',
  override_value DECIMAL(5,2) NULL,
  PRIMARY KEY (trigger_group_code, target_group_code)
);


/* ================= 调节系数 ================= */
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_time DATETIME DEFAULT CURRENT_TIMESTAMP,
  update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

/* ================  基础模板 ================= */
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,
  update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE 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,
  update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);