--
-- Task Manager DDL file
--

--
-- taskmng_projects
--

drop table if exists taskmng_projects;
create /* or replace */ table taskmng_projects
(
	-- table fields
	id int not null auto_increment,
	customer_id int not null, -- FK to taskmng_customers
	name varchar(255),
	state enum('sheduled', 'processed', 'deferred', 'done', 'abandoned'),
	started datetime,
	finished datetime,
	-- table keys
	primary key                   (id),
	key taskmng_tasks_customer_id (customer_id),
	key taskmng_projects_state    (state)
);

--
-- taskmng_tasks
--

drop table if exists taskmng_tasks;
create /* or replace */ table taskmng_tasks
(
	-- table fields
	id int not null auto_increment,
	project_id int not null, -- FK to taskmng_projects
	user_id int not null,    -- FK to taskmng_users
	name varchar(255),
	state enum('scheduled', 'processed', 'deferred',
	           'waiting-task', 'waiting-customer', 'done', 'abandoned'),
	duration int,
	percentage int,
	deadline datetime,
	-- table keys
	primary key                  (id),
	key taskmng_tasks_project_id (project_id),
	key taskmng_tasks_user_id    (user_id),
	key taskmng_tasks_state      (state)
);

--
-- taskmng_customers
--

drop table if exists taskmng_customers;
create /* or replace */ table taskmng_customers
(
	-- table fields
	id int not null auto_increment,
	name varchar(255),
	street varchar(255),
	city varchar(255),
	-- table keys
	primary key (id)
);

--
-- taskmng_users
--

drop table if exists taskmng_users;
create /* or replace */ table taskmng_users
(
	-- table fields
	id int not null auto_increment,
	name varchar(255),
	position varchar(60),
	-- table keys
	primary key (id)
);