DDL

drop database upbrella_dev;
create database upbrella_dev;
use upbrella_dev;

create table condition_report (
       id bigint AUTO_INCREMENT,
        content varchar(255) NOT NULL,
        etc varchar(255),
        history_id integer NOT NULL,
        primary key (id)
    );

create table history (
       id integer AUTO_INCREMENT,
        etc varchar(255),
        rented_at datetime(6) NOT NULL,
        returned_at datetime(6),
        payment_id bigint,
        rent_store_meta_id integer NOT NULL,
        return_store_meta_id integer,
        umbrella_id integer NOT NULL,
        user_id integer NOT NULL,
				bank varchar(45),
				account_number varchar(45),
				refunded_at datetime(6),
				refunded_by integer,
        primary key (id)
    );


create table improvement_report (
       id bigint AUTO_INCREMENT,
        content varchar(255) NOT NULL,
        etc varchar(255),
        history_id integer NOT NULL,
        primary key (id)
    );

create table store_detail (
       id integer AUTO_INCREMENT,
        address varchar(255) NOT NULL,
				address_detail varchar(255), 
        contact_info varchar(255),
        content varchar(255),
        insta_url varchar(255),
        working_hour varchar(255),
        store_meta_id integer NOT NULL,
				umbrella_location varchar(255),
        primary key (id)
    );

create table store_image (
       id integer AUTO_INCREMENT,
        image_url varchar(2000) NOT NULL,
        store_detail_id integer NOT NULL,
        primary key (id)
    );

create table store_meta (
       id integer AUTO_INCREMENT,
				classification_id bigint,
				sub_classification_id bigint,
        activated tinyint not null,
        deleted tinyint not null DEFAULT 0,
        name varchar(255) NOT NULL,
				category varchar(255),
				latitude double,
				longitude double,
				password varchar(45),
        primary key (id)
    );

create table business_hour (
				id integer auto_increment,
				store_meta_id integer,
				date varchar(45),
				open_at time,
				close_at time,
				primary key (id)
		); 
				

create table umbrella (
       id integer AUTO_INCREMENT,
        deleted tinyint not null DEFAULT 0,
        rentable tinyint not null,
        uuid varchar(255) NOT NULL,
        store_meta_id integer NOT NULL,
        primary key (id)
    );

create table user (
       id integer AUTO_INCREMENT,
        admin_status tinyint not null DEFAULT 0,
        name varchar(255) NOT NULL,
        phone_number varchar(255) NOT NULL,
				social_id bigint(11),
				bank varchar(45),
				account_number varchar(45),
        primary key (id)
    );

CREATE TABLE classification (
    id BIGINT AUTO_INCREMENT,
    type VARCHAR(45),
    name VARCHAR(45),
    latitude DOUBLE,
    longitude DOUBLE,
    PRIMARY KEY (id)
);

alter table store_meta
			add constraint adflkajsdfdfkj
			foreign key (classification_id)
			references classification (id);

alter table store_meta
			add constraint dvasdawerawer
			foreign key (sub_classification_id)
			references classification (id);

alter table condition_report 
       add constraint FKhorkbsomtvu5iapf6xhlj9jyh 
       foreign key (history_id) 
       references history (id);

alter table history 
       add constraint FKocr8hbhntkav93852032aw5ec 
       foreign key (rent_store_meta_id) 
       references store_meta (id);

alter table history 
       add constraint FKb015cj3kulnihjnw59erd2xm7 
       foreign key (return_store_meta_id) 
       references store_meta (id);

alter table history 
       add constraint FK5t3ld5ur9jj24tcy073e0ylao 
       foreign key (umbrella_id) 
       references umbrella (id);

alter table history 
       add constraint FKn4gjyu69m6xa5f3bot571imbe 
       foreign key (user_id) 
       references user (id);

alter table improvement_report 
       add constraint FK79ghc39dwhd2rh5j2x5ey94rn 
       foreign key (history_id) 
       references history (id);

alter table store_detail 
       add constraint FKcvljq0mxnvic5uqh2plkkhi49 
       foreign key (store_meta_id) 
       references store_meta (id);

alter table store_image 
       add constraint FKcqwhbqj0enp8e9un5rkql15ta 
       foreign key (store_detail_id) 
       references store_detail (id);

alter table umbrella 
       add constraint FKsc5sfu6dbwbaxwnvvbp9caaya 
       foreign key (store_meta_id) 
       references store_meta (id);

alter table history
			add constraint asdlfkjwenvcjcjj
			foreign key (refunded_by)
			references user (id);

alter table business_hour 
			add constraint asdflkjenncmvnwerwersd
			foreign key (store_meta_id)
			references store_meta (id);