/**
    更新的SQL
*/

/**
  日期：2024-03-25
 */
alter table personal_calculate add COLUMN sell_money decimal(16,2) DEFAULT NULL comment '出让金';
alter table personal_calculate add COLUMN sell_money_base decimal(16,2) DEFAULT NULL comment '出让金扣除的基准价格';
alter table personal_calculate add COLUMN sell_money_rate decimal(16,2) DEFAULT NULL comment '出让金扣除比例';

/**
  日期：2024-03-27
  新增五张资产业务相关表
 */
# 资产业务订单表
DROP TABLE IF EXISTS `assets`;
CREATE TABLE `assets`
(
    `id`                                bigint(20)                                              NOT NULL COMMENT '主键',
    `name`                              varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '项目名称',
    `order_id`                          varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci  NOT NULL COMMENT '项目编号',
    `financial`                         bit(1)                                                  NOT NULL COMMENT '是否金融',
    `allot_type`                        varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci  NOT NULL COMMENT '分单类型',
    `client_manager_id`                 bigint(20)                                              NOT NULL COMMENT '客户经理id(user_id)',
    `principal_id`                      bigint(20)                                              NULL     DEFAULT NULL COMMENT '项目负责人id(user_id)',
    `department_id`                     bigint(20)                                              NOT NULL COMMENT '接单部门id',
    `assets_business_gener`             varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL     DEFAULT NULL COMMENT '资产业务类型',
    `entrust_again`                     bit(1)                                                  NOT NULL DEFAULT b'0' COMMENT '客户再次委托',
    `clientele_type`                    varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci  NOT NULL COMMENT '客户类型（企业，个人）',
    `clientele_id`                      bigint(20)                                              NULL     DEFAULT NULL COMMENT '客户id',
    `clientele_sub_id`                  bigint(20)                                              NULL     DEFAULT NULL COMMENT '业务来源（下级客户id）',
    `clientele_contact_id`              bigint(20)                                              NOT NULL COMMENT '客户联系人id',
    `security`                          bit(1)                                                  NOT NULL COMMENT '是否证券项目(0:Y 1:N)',
    `significant_assets_reorganization` bit(1)                                                  NOT NULL COMMENT '是否资产重组项目(0:Y 1:N)',
    `state_assets`                      bit(1)                                                  NOT NULL COMMENT '是否国资项目(0:Y 1:N)',
    `foreign_assets_involved`           bit(1)                                                  NOT NULL COMMENT '是否涉及境外资产(0:Y 1:N)',
    `dispense_benefit`                  bit(1)                                                  NOT NULL DEFAULT b'0' COMMENT '是否分配产值',
    `bailor`                            varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci  NOT NULL COMMENT '委托人',
    `bailor_address`                    varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL     DEFAULT NULL COMMENT '委托人地址',
    `bailor_contact_name`               varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci  NOT NULL COMMENT '委托联系人姓名',
    `bailor_contact_tel`                varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci  NOT NULL COMMENT '委托人联系人电话',
    `terminal_clientele_id`             bigint(20)                                              NULL     DEFAULT NULL COMMENT '终端客户id',
    `terminal_clientele_type`           varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci  NOT NULL COMMENT '终端客户类型（企业，个人）',
    `terminal_clientele_contact_id`     bigint(20)                                              NULL     DEFAULT NULL COMMENT '终端联系人',
    `members`                           json                                                    NULL COMMENT '项目参与人(json格式,存用户表id)',
    `remark`                            text CHARACTER SET utf8 COLLATE utf8_unicode_ci         NULL COMMENT '备注',
    `deleted`                           bit(1)                                                  NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
    `created`                           datetime(0)                                             NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
    `modified`                          timestamp(6)                                            NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  CHARACTER SET = latin1
  COLLATE = latin1_swedish_ci COMMENT = '资产业务订单表'
  ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

# 资产评估方法表
DROP TABLE IF EXISTS `assets_evaluation_method`;
CREATE TABLE `assets_evaluation_method`
(
    `id`          bigint(20)                                                  NOT NULL COMMENT '资产评估方法id',
    `method_name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL COMMENT '资产评估方法名',
    `deleted`     bit(1)                                                      NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
    `created`     datetime(0)                                                 NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
    `modified`    timestamp(6)                                                NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  CHARACTER SET = latin1
  COLLATE = latin1_swedish_ci COMMENT = '资产评估方法表'
  ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

# 资产评估对象表
DROP TABLE IF EXISTS `assets_evaluation_target`;
CREATE TABLE `assets_evaluation_target`
(
    `id`                                bigint(20)                                                  NOT NULL COMMENT '资产评估对象id',
    `evaluation_purpose_id`             bigint(20)                                                  NOT NULL COMMENT '资产评估目的id',
    `evaluation_type_id`                bigint(20)                                                  NOT NULL COMMENT '资产评估对象类型一级id',
    `evaluation_type_sec_id`            bigint(20)                                                  NOT NULL COMMENT '资产评估对象类型二级id',
    `the_holder`                        varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL COMMENT '产权持有人',
    `assets_value_id`                   bigint(20)                                                  NOT NULL COMMENT '资产价值类型id',
    `principal_id`                      bigint(20)                                                  NOT NULL COMMENT '项目负责人id (user_id)',
    `site_ids`                          json                                                        NOT NULL COMMENT '实勘人id (json 可多个)',
    `valuation_basis_date`              date                                                        NOT NULL COMMENT '估价基准日',
    `evaluation_method_id`              bigint(20)                                                  NOT NULL COMMENT '评估方法id',
    `choice_evaluation_method_id`       bigint(20)                                                  NOT NULL COMMENT '最终选择的评估方法id',
    `total_asset_carrying_amount`       decimal(16, 2)                                              NULL     DEFAULT NULL COMMENT '总资产账面值(万)',
    `total_liabilities_carrying_amount` decimal(16, 2)                                              NULL     DEFAULT NULL COMMENT '总负债账面值(万)',
    `carrying_amount_of_net_assets`     decimal(16, 2)                                              NULL     DEFAULT NULL COMMENT '净资产账面值(万)',
    `estimated_value`                   decimal(16, 2)                                              NULL     DEFAULT NULL COMMENT '评估结论对应的评估值(万)',
    `remark`                            text CHARACTER SET utf8 COLLATE utf8_unicode_ci             NULL COMMENT '备注',
    `file_path`                         varchar(500) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL COMMENT '资料路由信息',
    `deleted`                           bit(1)                                                      NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
    `created`                           datetime(0)                                                 NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
    `modified`                          timestamp(6)                                                NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  CHARACTER SET = latin1
  COLLATE = latin1_swedish_ci COMMENT = '资产评估对象表'
  ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

# 资产评估对象类型表
DROP TABLE IF EXISTS `assets_evaluation_target_type`;
CREATE TABLE `assets_evaluation_target_type`
(
    `id`        bigint(20)                                                  NOT NULL COMMENT '资产评估对象类型id',
    `parent_id` bigint(20)                                                  NOT NULL COMMENT '父级id(0代表没有父级)',
    `type_name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL COMMENT '类型名',
    `deleted`   bit(1)                                                      NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
    `created`   datetime(0)                                                 NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
    `modified`  timestamp(6)                                                NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  CHARACTER SET = latin1
  COLLATE = latin1_swedish_ci COMMENT = '资产评估对象类型表'
  ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

# 价值类型表
DROP TABLE IF EXISTS `assets_value_type`;
CREATE TABLE `assets_value_type`
(
    `id`        bigint(20)                                                  NOT NULL COMMENT '价值类型id',
    `type_name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL COMMENT '价值类型名',
    `deleted`   bit(1)                                                      NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
    `created`   datetime(0)                                                 NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
    `modified`  timestamp(6)                                                NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  CHARACTER SET = latin1
  COLLATE = latin1_swedish_ci COMMENT = '价值类型表'
  ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;