set character set utf8;
SET NAMES 'utf8';
############################
# delete all table/view ###
############################
DROP TABLE IF EXISTS t_coupon ;
DROP TABLE IF EXISTS t_wallet ;
DROP TABLE IF EXISTS t_book;
DROP TABLE IF EXISTS t_user ;

CREATE TABLE IF NOT EXISTS `t_user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(32) DEFAULT NULL,
 `birthdate` date DEFAULT NULL,
 `id_number` varchar(32) UNIQUE DEFAULT NULL,
 `passport_no` varchar(32) UNIQUE DEFAULT NULL,
 `user_type` tinyint DEFAULT NULL DEFAULT 0 COMMENT 'X@NAME:证件类型@X,0:未知,1:身份证,2:护照,3:台胞证,4:港澳通行证,5:军官证,6:外国人居留证,7:员工卡,8:其他TYPE@enum:sql2java.test.UserType@EPYT',
 `user_type_name` varchar(32) DEFAULT NULL DEFAULT 'UNKNOW' COMMENT 'X@NAME:证件类型名字@X,TYPE@enum:sql2java.test.UserType@EPYT',
 `phone` varchar(32) DEFAULT NULL,
 `address` varchar(64) DEFAULT NULL,
 `create_time` timestamp DEFAULT CURRENT_TIMESTAMP COMMENT 'X@NAME:记录创建时间@X', 
 `update_time` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'X@NAME:记录修改时间@X',
 PRIMARY KEY (`id`),
 UNIQUE KEY `phone_UNIQUE` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `t_book` (
 `id` int(11) NOT NULL,
 `name` varchar(32) DEFAULT NULL,
 `author` varchar(32) DEFAULT NULL,
 `publisher` varchar(32) DEFAULT NULL,
 `publish_date` date DEFAULT NULL,
 `isbn` varchar(32) UNIQUE DEFAULT NULL,
 `borrower` int(11) DEFAULT NULL,
 `discont` float DEFAULT NULL,
 `price` decimal(10,2) DEFAULT NULL,
 `props` json DEFAULT NULL COMMENT '[JSON_STR,obj]JSON格式的扩展字段(最大64KB),用于定义扩展信息',
 `create_time` timestamp DEFAULT CURRENT_TIMESTAMP COMMENT 'X@NAME:记录创建时间@X', 
 PRIMARY KEY (`id`),
 CONSTRAINT `fk_borrower` FOREIGN KEY (`borrower`) REFERENCES `t_user` (`id`) ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t_wallet` (
 `wallet_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'X@NAME:钱包ID@X',
 `org_id` int(11) DEFAULT NULL COMMENT 'X@NAME:关联的组织ID@X',
 `wallet_type` int(4) DEFAULT NULL COMMENT 'X@NAME:钱包类型@X, 0-主钱包,1-推广钱包TYPE@enum:sql2java.test.WalletType@EPYT',
 `available_balance` DECIMAL(15,2) NOT NULL DEFAULT 0.00 COMMENT 'X@NAME:可用金额(元)@X',
 `lock_version` bigint NOT NULL DEFAULT '0' COMMENT 'X@NAME:乐观锁版本号@X LOCK@OPTIMISTIC@KCOL',
 `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'X@NAME:创建时间@X',
 `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'X@NAME:更新时间@X',
 PRIMARY KEY (`wallet_id`),
 UNIQUE KEY `uk_user_currency` (`org_id`,`wallet_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='钱包表(乐观锁测试)';

CREATE TABLE `t_coupon` (
 `coupon_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT 'X@NAME:钱包ID@X',
 `coupon_name` varchar(32) DEFAULT NULL COMMENT 'X@NAME:优惠券名@X',
 `user_id` int(11) DEFAULT NULL COMMENT 'X@NAME:用户名@X',
 `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'X@NAME:创建时间@X'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='优惠券表(悲观锁测试)';