create table query

create database jumanji;
drop database jumanji;
use jumanji;
#테이블 드랍할때 외래키 검사를 0으로 만들고 드랍 후 다시 1로 변경.
set foreign_key_checks =0;
drop table users cascade ;
set foreign_key_checks =1;
create table users(
id varchar(30),
password varchar(100) not null ,
name varchar(15) not null,
email varchar(70),
address varchar(90),
address_detail varchar(90),
birthday date,
phone varchar(11) not null,
is_wdrw char(1),
role varchar(10) default 'ROLE_USER', -- user
provider varchar(255) default 'jumin',
provider_id varchar(255),
sign_date date not null default now(),
lv int(1) default 1,
point int(6) not null default 0,
constraint users_id_pk primary key (id),
constraint users_phone_uk unique (phone)
);
# drop table coupons;
create table coupons(
id bigint AUTO_INCREMENT,
name varchar(30),
limit_time timestamp,
constraint coupons_id_pk primary key (id)
-- pk의 이름은 항상 무시되고 primary key가 된다.
);
create table userHasCoupon(
id varchar(30),
coupon_id bigint,
quantity int(2),
constraint memHasCou_id_coupon_id_pk primary key (id, coupon_id),
constraint memHasCou_id_fk foreign key(id) references users(id),
constraint memHasCou_coupon_id_fk foreign key(coupon_id) references coupons(id)
);


create table penalties(
id bigint AUTO_INCREMENT,
user_id varchar(30),
reg_time timestamp,
name varchar(60),
reason varchar(90),
constraint penalties_id_pk primary key (id),
constraint penalties_user_id_fk foreign key users(user_id) references users(id)
);

create table shops(
id varchar(10), -- 사업자번호
name varchar(60),
intro varchar(250),
open_time int(4),
close_time int(4),
address varchar(90),
address_detail varchar(90),
is_rs_pos char(1),
owner_id varchar(30),
constraint shops_shop_id_pk primary key (id),
constraint shops_owner_id_fk foreign key (owner_id) references users(id)
);

create table chatbots(
id bigint AUTO_INCREMENT,
question varchar(250),
answer varchar(250),
shop_id varchar(30),
constraint chatbots_bot_id_pk primary key (id),
constraint chatbots_shop_id_fk foreign key (shop_id) references shops(id)
);

create table messages(
id bigint AUTO_INCREMENT,
reg_time timestamp,
content text, -- 65535 문자
shop_id varchar(10),
user_id varchar(30),
constraint messages_id_pk primary key (id),
constraint messages_shop_id_fk foreign key (shop_id) references shops(id),
constraint messages_user_id_fk foreign key (user_id) references users(id)
);

# select length('한글테스트') from dual; -- 한글 -> 3Byte
create table reviews(
id bigint AUTO_INCREMENT,
shop_id varchar(10),
content text,
reg_time timestamp,
parent_id bigint,
score int(1),
img_url varchar(250),
user_id varchar(30),
constraint reviews_rev_id_pk primary key (id),
constraint reviews_id_fk foreign key (user_id) references users(id)
);

create table menus(
id int(3) auto_increment,
shop_id varchar(10),
name varchar(90) not null ,
description varchar(100) not null ,
is_sale char(1) not null default 'Y',
is_popular char(1) default 'N',
price int(5) not null default 100,
duration int(2),
img_url varchar(250),
constraint primary key (id, shop_id),
constraint menus_shop_id_fk foreign key (shop_id) references shops(id)
);

create table tables(
id int(2),
shop_id varchar(10),
seat_qty int(2),
constraint primary key (id, shop_id),
constraint tables_shop_id_fk foreign key (shop_id) references shops(id)
);

create table orders(
id bigint auto_increment,
quantity int(2),
request varchar(120),
order_time timestamp,
shop_id varchar(10),
user_id varchar(30),
menu_id int(3),
table_id int(2),
constraint primary key (id),
constraint orders_shop_id_fk foreign key (shop_id) references shops(id),
constraint orders_id_fk foreign key (user_id) references users(id),
constraint orders_menu_id_fk foreign key (menu_id) references menus(id),
constraint orders_tab_id_fk foreign key (table_id) references tables(id)
);

create table reservations(
order_id bigint auto_increment,
people int(2) not null,
delay char(1) not null default 0, -- 0 이면 0~9분 1이면 10~19분...?
arrive_time timestamp, -- 이 값이 없으면 노쇼인걸로 판정하면 됨.
is_cancel char(1),
res_time timestamp not null,
constraint primary key (order_id),
constraint reservations_order_id_fk foreign key (order_id) references orders(id)
);

create table payments(
order_id bigint,
use_point int(5) default 0,
pay_time timestamp not null default now(),
pg varchar(8) not null,
comple_pay int(7),
is_refund char(1) default 'N',
constraint primary key(order_id)
);


select * from user;
select * from chatbot;
select * from coupon;
select * from menu;
select * from message;
select * from ordr;
select * from payment;
select * from reservation;
select * from review;
select * from shop;
select * from tab;

desc user;
SELECT schema_name, default_character_set_name FROM information_schema.schemata; -- encoding check - jumanji - latin1 -> korean error.
alter database jumanji default character set utf8;
set foreign_key_checks=0; -- foreign key check...
ALTER TABLE user convert to charset utf8;
ALTER TABLE message convert to charset utf8;

commit;

alter table user
CHANGE id id VARCHAR(30);

alter table user
change auth role varchar(6);

alter table user
change id id varchar(100);

alter table user
change provider provider varchar(255) default 'jumin';

# alter table user
# modify is_wdrw char(1) default ''