sql

drop table if exists rooms;
drop table if exists room_members;
drop table if exists user_account;
drop table if exists users;
drop table if exists balance_send;
drop table if exists balance_receive;

CREATE TABLE `rooms` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `room_id` varchar(3) NOT NULL,
  `room_name` varchar(100) NOT NULL,
  `category1` varchar(100) DEFAULT NULL,
  `category2` varchar(100) DEFAULT NULL,
  `category3` varchar(100) DEFAULT NULL,
  `category4` varchar(100) DEFAULT NULL,
  `category5` varchar(100) DEFAULT NULL,
  `member_count` int(2) DEFAULT NULL,
  `open` enum ('Y','N') NOT NULL DEFAULT 'N' ,
  `owner_id` bigint NOT NULL,
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `room_id_index` (`room_id`),
KEY `open_index` (`open`),
KEY `owner_id_index` (`owner_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `room_members` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `room_id` varchar(3) NOT NULL,
  `user_id` bigint NOT NULL,
  `black` enum ('Y','N') NOT NULL DEFAULT 'N' ,
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
KEY `black_index` (`black`),
KEY `user_id_index` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `balance_send` (
  `balance_send_id` bigint NOT NULL AUTO_INCREMENT,
  `room_id` varchar(3) NOT NULL,
  `sender_user_id` bigint NOT NULL,
  `balance` int(20) NOT NULL DEFAULT 0 ,
  `send_status` enum ('Y','N','C') NOT NULL DEFAULT 'Y' ,
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`balance_send_id`),
KEY `room_id_index` (`room_id`),
KEY `sender_user_id_index` (`sender_user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `balance_receive`(
  `id` bigint NOT NULL AUTO_INCREMENT,
  `balance_send_id` bigint NOT NULL,
  `receive_user_id` bigint NOT NULL,
  `balance` int(20) NOT NULL DEFAULT 0 ,
  `receive_status` enum ('Y','N','C') NOT NULL DEFAULT 'Y' ,
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
KEY `receive_user_id_index` (`receive_user_id`),
KEY `balance_send_id_index` (`balance_send_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `user_account` (
  `account_id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `balance` int(20) NOT NULL DEFAULT 0 ,
  `black` enum ('Y','N') NOT NULL DEFAULT 'N' ,
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`account_id`),
KEY `black_index` (`black`),
KEY `user_id_index` (`user_id`),
KEY `balance_index` (`balance`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `users` (
  `user_id` bigint NOT NULL AUTO_INCREMENT,
  `user_name` varchar(100) NOT NULL,
  `black` enum ('Y','N') NOT NULL DEFAULT 'N' ,
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
KEY `black_index` (`black`),
KEY `user_id_index` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;