create schema if not exists goa_dev collate utf8_general_ci;

create table if not exists job_log
(
	i_d bigint auto_increment
		primary key,
	user_id bigint null,
	project text null,
	type text null,
	title varchar(255) null,
	content varchar(1000) null,
	start_time datetime null,
	end_time datetime null,
	create_at datetime null,
	update_at datetime null,
	nounull varchar(1000) null,
	user text not null,
	progress double null
);

create table if not exists job_project
(
	i_d bigint auto_increment
		primary key,
	name varchar(255) null,
	status int null,
	description varchar(255) null,
	create_at datetime null,
	update_at datetime null,
	constraint UQE_job_project_name
		unique (name)
);

create table if not exists job_type
(
	i_d bigint auto_increment
		primary key,
	name varchar(255) null,
	alias varchar(255) null,
	description varchar(200) null,
	create_at datetime null,
	update_at datetime null,
	constraint UQE_job_type_name
		unique (name)
);

create table if not exists message
(
	i_d bigint auto_increment
		primary key,
	sender text null,
	receiver text null,
	status int default 0 not null,
	message_body text not null,
	create_at datetime null,
	update_at datetime null
);

create table if not exists message_body
(
	i_d bigint auto_increment
		primary key,
	title varchar(50) not null,
	type int default 0 not null,
	detail varchar(255) not null,
	create_at datetime null
);

create table if not exists monthly_report
(
	i_d bigint auto_increment
		primary key,
	user text not null,
	report_time datetime not null,
	summary varchar(500) not null,
	undone varchar(400) null,
	consult varchar(400) null,
	create_at datetime null,
	update_at datetime null
);

create table if not exists organ
(
	i_d bigint auto_increment
		primary key,
	name varchar(50) not null,
	description varchar(255) null,
	parent bigint null,
	manager bigint not null,
	create_at datetime null,
	update_at datetime null,
	constraint UQE_organ_name
		unique (name)
);

create table if not exists user
(
	i_d bigint auto_increment
		primary key,
	name varchar(255) not null,
	password blob null,
	display_name varchar(255) null,
	enabled tinyint(1) null,
	is_admin tinyint(1) null,
	create_at datetime null,
	update_at datetime null,
	organ_id bigint null,
	organ text null,
	constraint UQE_user_name
		unique (name)
);

create table if not exists weekly_report
(
	i_d bigint auto_increment
		primary key,
	user text not null,
	report_time datetime not null,
	summary varchar(500) not null,
	undone varchar(400) null,
	next_work varchar(400) null,
	consult varchar(400) null,
	create_at datetime null,
	update_at datetime null
);