docker run --name zzimkkong-mysql -e MYSQL_ROOT_PASSWORD=1234 -d -p 13306:3306 -v zzimkkong-mysql-volume:/var/lib/mysql {docker_image_name:tag}

# MAC m1 기준 예시
docker run --name zzimkkong-mysql -e MYSQL_ROOT_PASSWORD=1234 -d -p 13306:3306 -v zzimkkong-mysql-volume:/var/lib/mysql arm64v8/mysql

docker exec -it zzimkkong-mysql /bin/bash

# 컨테이너 내부 (비번 1234)
mysql -u root -p

# 20230713 기준 PROD 환경 DDL
create database zzimkkong;

create table zzimkkong.member
(
    id             bigint auto_increment
        primary key,
    email          varchar(50)  not null,
    password       varchar(128) null,
    organization   varchar(20)  null,
    oauth_provider varchar(10)  null,
    user_name      varchar(20)  not null,
    emoji          varchar(255) not null,
    constraint email
        unique (email),
    constraint user_name
        unique (user_name)
);

create table zzimkkong.map
(
    id          bigint auto_increment
        primary key,
    map_drawing longtext    not null,
    thumbnail   longtext    not null,
    name        varchar(20) not null,
    member_id   bigint      not null,
    slack_url   longtext    null,
    notice      longtext    null,
    constraint map_ibfk_1
        foreign key (member_id) references zzimkkong.member (id)
);

create index member_id
    on zzimkkong.map (member_id);


create table zzimkkong.preset
(
    id                            bigint auto_increment
        primary key,
    name                          varchar(20)  not null,
    setting_start_time            time         not null,
    setting_end_time              time         not null,
    reservation_time_unit         int          not null,
    reservation_minimum_time_unit int          not null,
    reservation_maximum_time_unit int          not null,
    enabled_day_of_week           varchar(255) null,
    member_id                     bigint       not null,
    constraint preset_ibfk_1
        foreign key (member_id) references zzimkkong.member (id)
);

create index manager_id
    on zzimkkong.preset (member_id);

create table zzimkkong.space
(
    id                 bigint auto_increment
        primary key,
    area               varchar(255) not null,
    color              varchar(25)  null,
    name               varchar(20)  not null,
    reservation_enable bit          not null,
    map_id             bigint       not null,
    constraint space_ibfk_1
        foreign key (map_id) references zzimkkong.map (id)
);

create index map_id
    on zzimkkong.space (map_id);


create table zzimkkong.reservation
(
    id          bigint auto_increment
        primary key,
    description varchar(100) not null,
    start_time  datetime     not null,
    end_time    datetime     not null,
    password    varchar(4)   null,
    user_name   varchar(20)  not null,
    space_id    bigint       not null,
    date        date         not null,
    member_id   bigint       null,
    constraint fk_reservation_member
        foreign key (member_id) references zzimkkong.member (id),
    constraint reservation_ibfk_1
        foreign key (space_id) references zzimkkong.space (id)
)
    collate = utf8mb4_unicode_ci;

create index i_memberid_date
    on zzimkkong.reservation (member_id, date);

create index i_spaceid_date
    on zzimkkong.reservation (space_id, date);

create index i_username_date
    on zzimkkong.reservation (user_name, date);

create index space_id
    on zzimkkong.reservation (space_id);


create table zzimkkong.setting
(
    id                            bigint auto_increment
        primary key,
    space_id                      bigint       not null,
    enabled_day_of_week           varchar(255) null,
    setting_start_time            time         not null,
    setting_end_time              time         not null,
    reservation_maximum_time_unit int          not null,
    reservation_minimum_time_unit int          not null,
    reservation_time_unit         int          not null,
    constraint setting_ibfk_1
        foreign key (space_id) references zzimkkong.space (id)
);

create index space_id
    on zzimkkong.setting (space_id);