72 lines
3.1 KiB
MySQL
72 lines
3.1 KiB
MySQL
|
-- 创建 bank_ 前缀业务表（无DROP，避免覆盖现有表）
|
|||
|
|
|
|||
|
|
CREATE TABLE IF NOT EXISTS bank_roles (
|
|||
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|||
|
|
name VARCHAR(50) NOT NULL UNIQUE,
|
|||
|
|
display_name VARCHAR(100) NOT NULL,
|
|||
|
|
description TEXT NULL,
|
|||
|
|
level INT NOT NULL DEFAULT 1,
|
|||
|
|
is_system TINYINT(1) NOT NULL DEFAULT 0,
|
|||
|
|
status ENUM('active','inactive') NOT NULL DEFAULT 'active',
|
|||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|||
|
|
|
|||
|
|
CREATE TABLE IF NOT EXISTS bank_users (
|
|||
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|||
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|||
|
|
email VARCHAR(100) NOT NULL UNIQUE,
|
|||
|
|
password VARCHAR(255) NOT NULL,
|
|||
|
|
phone VARCHAR(20) NULL,
|
|||
|
|
real_name VARCHAR(50) NOT NULL,
|
|||
|
|
id_card VARCHAR(18) NOT NULL UNIQUE,
|
|||
|
|
avatar VARCHAR(255) NULL,
|
|||
|
|
role_id INT NOT NULL,
|
|||
|
|
status ENUM('active','inactive','suspended','locked') NOT NULL DEFAULT 'active',
|
|||
|
|
last_login DATETIME NULL,
|
|||
|
|
login_attempts INT NOT NULL DEFAULT 0,
|
|||
|
|
locked_until DATETIME NULL,
|
|||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|||
|
|
CONSTRAINT fk_bank_users_role_id FOREIGN KEY (role_id) REFERENCES bank_roles(id)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|||
|
|
|
|||
|
|
CREATE TABLE IF NOT EXISTS bank_accounts (
|
|||
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|||
|
|
account_number VARCHAR(20) NOT NULL UNIQUE,
|
|||
|
|
user_id INT NOT NULL,
|
|||
|
|
account_type ENUM('savings','checking','credit','loan') NOT NULL DEFAULT 'savings',
|
|||
|
|
balance BIGINT NOT NULL DEFAULT 0,
|
|||
|
|
available_balance BIGINT NOT NULL DEFAULT 0,
|
|||
|
|
frozen_amount BIGINT NOT NULL DEFAULT 0,
|
|||
|
|
currency VARCHAR(3) NOT NULL DEFAULT 'CNY',
|
|||
|
|
interest_rate DECIMAL(5,4) NULL,
|
|||
|
|
status ENUM('active','inactive','frozen','closed') NOT NULL DEFAULT 'active',
|
|||
|
|
opened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
closed_at DATETIME NULL,
|
|||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|||
|
|
CONSTRAINT fk_bank_accounts_user_id FOREIGN KEY (user_id) REFERENCES bank_users(id)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|||
|
|
|
|||
|
|
CREATE TABLE IF NOT EXISTS bank_transactions (
|
|||
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|||
|
|
transaction_number VARCHAR(32) NOT NULL UNIQUE,
|
|||
|
|
account_id INT NOT NULL,
|
|||
|
|
transaction_type ENUM('deposit','withdrawal','transfer_in','transfer_out','interest','fee','loan','repayment') NOT NULL,
|
|||
|
|
amount BIGINT NOT NULL,
|
|||
|
|
balance_before BIGINT NOT NULL,
|
|||
|
|
balance_after BIGINT NOT NULL,
|
|||
|
|
counterparty_account VARCHAR(20) NULL,
|
|||
|
|
counterparty_name VARCHAR(100) NULL,
|
|||
|
|
description VARCHAR(255) NULL,
|
|||
|
|
reference_number VARCHAR(50) NULL,
|
|||
|
|
status ENUM('pending','completed','failed','cancelled','reversed') NOT NULL DEFAULT 'pending',
|
|||
|
|
processed_at DATETIME NULL,
|
|||
|
|
reversed_at DATETIME NULL,
|
|||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|||
|
|
CONSTRAINT fk_bank_transactions_account_id FOREIGN KEY (account_id) REFERENCES bank_accounts(id)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|||
|
|
|