-- 创建数据库
CREATE DATABASE IF NOT EXISTS `loan_system` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE `loan_system`;

-- 1. roles 表（角色表）
CREATE TABLE `roles` (
                         `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                         `role_name` VARCHAR(64) UNIQUE,
                         `description` VARCHAR(255),
                         `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除'
);

-- 2. users 表（用户表）
CREATE TABLE `users` (
                         `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                         `openid` VARCHAR(128),
                         `username` VARCHAR(64) UNIQUE,
                         `password_hash` VARCHAR(255),
                         `real_name` VARCHAR(100),
                         `sex` VARCHAR(10),
                         `mobile` CHAR(11),
                         `role` VARCHAR(255),
                         `ext_role_type` VARCHAR(50),
                         `dept` VARCHAR(100),
                         `status` TINYINT,
                         `created_time` DATETIME,
                         `updated_time` DATETIME,
                         `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除',
                         INDEX `idx_mobile` (`mobile`)
);

-- 3. customers 表（客户表）
CREATE TABLE `customers` (
                             `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                             `openid` VARCHAR(128),
                             `name` VARCHAR(100),
                             `sex` VARCHAR(10),
                             `id_number` CHAR(18),
                             `mobile` CHAR(11),
                             `register_source` VARCHAR(50),
                             `bank_account` VARCHAR(64),
                             `face_auth` BOOLEAN,
                             `create_time` DATETIME,
                             `update_time` DATETIME,
                             `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除',
                             INDEX `idx_id_number` (`id_number`),
                             INDEX `idx_mobile` (`mobile`)
);

-- 4. customers_other 表（其他客户表）
CREATE TABLE `customers_other` (
                                   `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                                   `name` VARCHAR(100),
                                   `id_number` CHAR(18),
                                   `mobile` CHAR(11),
                                   `create_time` DATETIME,
                                   `update_time` DATETIME,
                                   `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除'
);

-- 5. collateral 表（押品表）
CREATE TABLE `collateral` (
                              `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                              `case_id` BIGINT,
                              `collateral_type` VARCHAR(30),
                              `owner_customer_id` BIGINT,
                              `allocated_amount` DECIMAL(18,2),
                              `address` VARCHAR(500),
                              `eval_price` DECIMAL(18,2),
                              `is_involved_in_litigation` BOOLEAN,
                              `staus` VARCHAR(20),
                              `create_time` DATETIME,
                              `update_time` DATETIME,
                              `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除'
);

-- 6. loan_case 表（业务单表）
CREATE TABLE `loan_case` (
                             `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                             `case_no` VARCHAR(50) UNIQUE,
                             `customer_id` BIGINT,
                             `business_type` VARCHAR(20),
                             `business_attrs` VARCHAR(200),
                             `channel_name` BIGINT,
                             `remark_id` VARCHAR(20),
                             `custom1_id` BIGINT,
                             `custom2_id` BIGINT,
                             `requested_amount` DECIMAL(18,2),
                             `total_loan_amount` DECIMAL(18,2),
                             `create_time` DATETIME,
                             `update_time` DATETIME,
                             `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除'
);

-- 8. documents 表（附件表）
CREATE TABLE `documents` (
                             `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                             `case_id` BIGINT,
                             `owner_id` BIGINT,
                             `type_id` BIGINT,
                             `doc_type` VARCHAR(50),
                             `file_path` VARCHAR(500),
                             `file_name` VARCHAR(255),
                              `file_size` BIGINT,
                             `is_current` BOOLEAN,
                             `create_time` DATETIME,
                             `update_time` DATETIME,
                             `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除',
                             INDEX `idx_owner_id` (`owner_id`)
);

-- 9. approval_record 表（审批记录表）
CREATE TABLE `approval_record` (
                                   `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                                   `case_id` BIGINT,
                                   `step_name` VARCHAR(100),
                                   `approver_id` BIGINT,
                                   `decision` VARCHAR(20),
                                   `comments` TEXT,
                                   `create_time` DATETIME,
                                   `update_time` DATETIME,
                                   `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除',
                                   INDEX `idx_case_id` (`case_id`)
);

-- 10. contract 表（合同表）
CREATE TABLE `contract` (
                            `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                            `business_attr` VARCHAR(200),
                            `case_id` BIGINT,
                            `contract_no` VARCHAR(50) UNIQUE,
                            `contract_name` VARCHAR(50),
                            `contract_version` INT,
                            `contract_amount` DECIMAL(15,2),
                            `interest_rate` DECIMAL(5,4),
                            `loan_period` INT,
                            `content` TEXT,
                            `signed_by_customer` BOOLEAN,
                            `sifned_id` BIGINT,
                            `signed_time` DATETIME,
                            `create_time` DATETIME,
                            `update_time` DATETIME,
                            `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除',
                            INDEX `idx_case_id` (`case_id`)
);

-- 11. disbursement 表（出款表）
CREATE TABLE `disbursement` (
                                `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                                `case_id` BIGINT,
                                `payout_approve_user_id` BIGINT,
                                `payout_operator_user_id` BIGINT,
                                `apply_by` BIGINT,
                                `apply_at` DATETIME,
                                `disbursement_type` VARCHAR(10),
                                `planned_amount` DECIMAL(18,2),
                                `planned_location` VARCHAR(255),
                                `disbursement_status` VARCHAR(30),
                                `contract_id` BIGINT,
                                `payout_time` DATETIME,
                                `create_time` DATETIME,
                                `update_time` DATETIME,
                                `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除',
                                INDEX `idx_case_id` (`case_id`),
                                INDEX `idx_disbursement_status` (`disbursement_status`)
);

-- disbursement_record 表（出款记录表）
CREATE TABLE `disbursement_record` (
                                       `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                                       `disbursement_id` BIGINT,
                                       `amount` DECIMAL(18,2),
                                       `create_time` DATETIME,
                                       `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除'
);

-- 12. repayment 表（回款表）
CREATE TABLE `repayment` (
                             `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                             `case_id` BIGINT,
                             `contract_id` BIGINT,
                             `repay_by_customer_id` BIGINT,
                             `repayment_plan_user_id` BIGINT,
                             `repayment_operator_user_id` BIGINT,
                             `repayment_type` VARCHAR(10),
                             `repay_amount` DECIMAL(18,2),
                             `repay_at` DATETIME,
                             `repay_bank` VARCHAR(2000),
                             `repay_record_id` BIGINT,
                             `confirmed_by` BIGINT,
                             `confirmed_at` DATETIME,
                             `interest` DECIMAL(18,2),
                             `is_cleared` BOOLEAN,
                             `create_time` DATETIME,
                             `update_time` DATETIME,
                             `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除',
                             INDEX `idx_case_id` (`case_id`),
                             INDEX `idx_repay_at` (`repay_at`),
                             INDEX `idx_is_cleared` (`is_cleared`)
);

-- repayment_record 表（回款记录表）
CREATE TABLE `repayment_record` (
                                    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                                    `repayment_id` BIGINT,
                                    `amount` DECIMAL(18,2),
                                    `create_time` DATETIME,
                                    `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除'
);

-- 14. biz_recommender 表（推荐人表）
CREATE TABLE `biz_recommender` (
                                   `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                                   `recommender_code` VARCHAR(50) UNIQUE,
                                   `recommender_name` VARCHAR(100),
                                   `recommender_type` VARCHAR(50),
                                   `phone` VARCHAR(20),
                                   `channel` VARCHAR(50),
                                   `id_card` VARCHAR(20),
                                   `bank_account` VARCHAR(50),
                                   `commission_rate` DECIMAL(5,4),
                                   `company` VARCHAR(50),
                                   `remark` TEXT,
                                   `create_time` DATETIME,
                                   `update_time` DATETIME,
                                   `create_user_id` BIGINT,
                                   `update_user_id` BIGINT,
                                   `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除'
);

-- 15. sys_message 表（消息表）
CREATE TABLE `sys_message` (
                               `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                               `user_id` BIGINT,
                               `message_title` VARCHAR(200),
                               `message_content` TEXT,
                               `message_type` VARCHAR(50),
                               `read_status` TINYINT,
                               `related_id` BIGINT,
                               `related_type` VARCHAR(50),
                               `create_time` DATETIME,
                               `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除'
);

-- 16. stat_business_snapshot 表（业务统计快照表）
CREATE TABLE `stat_business_snapshot` (
                                          `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                                          `stat_date` DATE,
                                          `channel` VARCHAR(50),
                                          `biz_type` VARCHAR(50),
                                          `application_count` INT,
                                          `total_loan_amount` DECIMAL(15,2),
                                          `user_id` BIGINT,
                                          `create_time` DATETIME,
                                          `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除'
);

-- 17. stat_fund_efficiency 表（资金效率统计表）
CREATE TABLE `stat_fund_efficiency` (
                                        `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                                        `stat_month` VARCHAR(7),
                                        `daily_loan_balance` DECIMAL(15,2),
                                        `actual_days` INT,
                                        `interest_income` DECIMAL(15,2),
                                        `create_time` DATETIME,
                                        `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除'
);

-- 18. dict_business_type 表（业务类型字典）
CREATE TABLE `dict_business_type` (
                                      `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                                      `code` VARCHAR(50) UNIQUE,
                                      `name` VARCHAR(100),
                                      `enabled` BOOLEAN,
                                      `sort_order` INT,
                                      `created_at` DATETIME,
                                      `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除',
                                      INDEX `idx_enabled` (`enabled`)
);

-- 19. dict_channel 表（渠道字典）
CREATE TABLE `dict_channel` (
                                `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                                `code` VARCHAR(50) UNIQUE,
                                `name` VARCHAR(100),
                                `enabled` BOOLEAN,
                                `sort_order` INT,
                                `created_at` DATETIME,
                                `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除',
                                INDEX `idx_enabled` (`enabled`)
);

-- 20. dict_attribute 表（业务属性字典）
CREATE TABLE `dict_attribute` (
                                  `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                                  `code` VARCHAR(50) UNIQUE,
                                  `name` VARCHAR(100),
                                  `enabled` BOOLEAN,
                                  `sort_order` INT,
                                  `created_at` DATETIME,
                                  `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除',
                                  INDEX `idx_enabled` (`enabled`)
);

-- 21. dict_location 表（位置字典）
CREATE TABLE `dict_location` (
                                 `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                                 `code` VARCHAR(50) UNIQUE,
                                 `name` VARCHAR(100),
                                 `enabled` BOOLEAN,
                                 `sort_order` INT,
                                 `created_at` DATETIME,
                                 `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除',
                                 INDEX `idx_enabled` (`enabled`)
);

-- 22. dict_message 表（消息类型字典）
CREATE TABLE `dict_message` (
                                `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                                `code` INT UNIQUE,
                                `name` VARCHAR(100),
                                `enabled` BOOLEAN,
                                `sort_order` INT,
                                `created_at` DATETIME,
                                `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除',
                                INDEX `idx_enabled` (`enabled`)
);

-- 23. dict_type 表（资料类型表）
CREATE TABLE `dict_type` (
                             `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                             `code` INT UNIQUE,
                             `name` VARCHAR(100),
                             `enabled` BOOLEAN,
                             `sort_order` INT,
                             `created_at` DATETIME,
                             `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除',
                             INDEX `idx_enabled` (`enabled`)
);

-- 25. dict_step 表（环节类型）
CREATE TABLE `dict_step` (
                             `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
                             `code` INT UNIQUE,
                             `name` VARCHAR(50),
                             `is_parent` BIGINT,
                             `enabled` BOOLEAN,
                             `sort_order` INT,
                             `created_at` DATETIME,
                             `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除',
                             INDEX `idx_enabled` (`enabled`)
);

-- 26. steps 表
CREATE TABLE `steps` (
                         `id` BIGINT PRIMARY KEY  AUTO_INCREMENT,
                         `step_name` VARCHAR(50),
                         `case_id` BIGINT,
                         `status` VARCHAR(50),
                         `user_id1` BIGINT,
                         `user_id2` BIGINT,
                         `begin_time` DATETIME,
                         `parent_id` BIGINT,
                         `pre_id` BIGINT,
                         `next_id` BIGINT,
                         `create_time` DATETIME,
                         `update_time` DATETIME,
                         `is_delete` TINYINT(1) DEFAULT 0 COMMENT '是否删除'
);