tyc-server/deploy/sql/product.sql

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;

CREATE TABLE `product` (
    `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `delete_time` datetime DEFAULT NULL COMMENT '删除时间',
    `del_state` tinyint NOT NULL DEFAULT '0' COMMENT '删除状态',
    `version` bigint NOT NULL DEFAULT '0' COMMENT '版本号',
    `product_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '服务名',
    `product_en` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '英文名',
    `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '描述',
    `notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '备注',
    `cost_price` DECIMAL(10, 2) NOT NULL DEFAULT '1.00' COMMENT '成本',
    `sell_price` DECIMAL(10, 2) NOT NULL DEFAULT '1.00' COMMENT '售价',
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_product_en` (`product_en`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '产品表';

-- ----------------------------
-- Records for product
-- ----------------------------
INSERT INTO
    `product` (
        `product_name`,
        `product_en`,
        `description`,
        `notes`,
        `cost_price`,
        `sell_price`
    )
VALUES (
        '背景调查',
        'backgroundchecklogic',
        '',
        '',
        1,
        1
    ),
    (
        '企业报告',
        'companyinfologic',
        '',
        '',
        1,
        1
    ),
    (
        '家政服务',
        'homeservicelogic',
        '',
        '',
        1,
        1
    ),
    (
        '婚姻状态',
        'marriagelogic',
        '',
        '',
        1,
        1
    ),
    (
        '贷前背调',
        'preloanbackgroundchecklogic',
        '',
        '',
        1,
        1
    ),
    (
        '租赁服务',
        'rentalinfologic',
        '',
        '',
        1,
        1
    ),
    (
        '个人风险评估',
        'riskassessmentlogic',
        '',
        '',
        1,
        1
    ),
    (
        '手机三要素',
        'toc_PhoneThreeElements',
        '',
        '',
        1,
        1
    ),
    (
        '银行卡黑名单',
        'toc_BankCardBlacklist',
        '',
        '',
        1,
        1
    ),
    (
        '身份证二要素',
        'toc_IDCardTwoElements',
        '',
        '',
        1,
        1
    ),
    (
        '手机二要素',
        'toc_PhoneTwoElements',
        '',
        '',
        1,
        1
    ),
    (
        '在网时长',
        'toc_NetworkDuration',
        '',
        '',
        1,
        1
    ),
    (
        '手机二次卡',
        'toc_PhoneSecondaryCard',
        '',
        '',
        1,
        1
    ),
    (
        '手机号码风险',
        'toc_PhoneNumberRisk',
        '',
        '',
        1,
        1
    ),
    (
        '银行卡四要素',
        'toc_BankCardFourElements',
        '',
        '',
        1,
        1
    ),
    (
        '银行卡三要素',
        'toc_BankCardThreeElements',
        '',
        '',
        1,
        1
    ),
    (
        '自然人生存状态',
        'toc_NaturalLifeStatus',
        '',
        '',
        1,
        1
    ),
    (
        '学历核验',
        'toc_EducationVerification',
        '',
        '',
        1,
        1
    ),
    (
        '人车核验',
        'toc_PersonVehicleVerification',
        '',
        '',
        1,
        1
    ),
    (
        '名下车辆',
        'toc_VehiclesUnderName',
        '',
        '',
        1,
        1
    ),
    (
        '双人婚姻',
        'toc_DualMarriage',
        '',
        '',
        1,
        1
    ),
    (
        '个人不良',
        'toc_PersonalBadRecord',
        '',
        '',
        1,
        1
    ),
    (
        '股东人企关系',
        'toc_ShareholderBusinessRelation',
        '',
        '',
        1,
        1
    ),
    (
        '个人涉诉',
        'toc_PersonalLawsuit',
        '',
        '',
        1,
        1
    ),
    (
        '企业涉诉',
        'toc_EnterpriseLawsuit',
        '',
        '',
        1,
        1
    ),
    (
        '婚姻评估',
        'toc_MarriageAssessment',
        '',
        '',
        1,
        1
    );

SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------
-- Table structure for feature
-- ----------------------------
DROP TABLE IF EXISTS `feature`;

CREATE TABLE `feature` (
    `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `delete_time` datetime DEFAULT NULL COMMENT '删除时间',
    `del_state` tinyint NOT NULL DEFAULT '0' COMMENT '删除状态',
    `version` bigint NOT NULL DEFAULT '0' COMMENT '版本号',
    `api_id` varchar kujmio,
    5 (20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'API标识',
    `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '描述',
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_api_id` (`api_id`) ``
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '功能表';

-- ----------------------------
-- Table structure for product_feature
-- ----------------------------
DROP TABLE IF EXISTS `product_feature`;

CREATE TABLE `product_feature` (
    `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `product_id` bigint NOT NULL COMMENT '产品ID',
    `feature_id` bigint NOT NULL COMMENT '功能ID',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `delete_time` datetime DEFAULT NULL COMMENT '删除时间',
    `del_state` tinyint NOT NULL DEFAULT '0' COMMENT '删除状态',
    `version` bigint NOT NULL DEFAULT '0' COMMENT '版本号',
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_product_feature` (`product_id`, `feature_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '产品与功能关联表';

-- ----------------------------
-- Records for feature
-- ----------------------------
INSERT INTO
    `feature` (`api_id`, `name`)
VALUES ('G09SC02', '单人婚姻'),
    ('G27BJ05', '借贷意向'),
    ('G28BJ05', '借贷行为'),
    ('G26BJ05', '特殊名单'),
    ('G34BJ03', '个人不良'),
    ('G35SC01', '个人涉诉'),
    ('G05HZ01', '股东人企关系');

-- ----------------------------
-- 插入每个产品与每个功能的对应关系
-- ----------------------------

INSERT INTO
    `product_feature` (`product_id`, `feature_id`)
SELECT p.id AS product_id, f.id AS feature_id
FROM product p
    CROSS JOIN feature f;