72 lines
3.1 KiB
SQL
72 lines
3.1 KiB
SQL
-- 创建 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;
|
||
|