CREATE TABLE category (
  id int(5) PRIMARY KEY auto_increment,
  name varchar(255),
) TYPE=INNODB;


CREATE TABLE region(
  region_id int(2) PRIMARY KEY auto_increment,
  region_name varchar(250))TYPE=INNODB;

insert into region values(1,'ALL')


CREATE TABLE department (
  id int(5)  auto_increment,
  region_id int(2),
  name varchar(255),
  PRIMARY KEY  (id),
  index(region_id),
  FOREIGN KEY(region_id) REFERENCES region(region_id)
) TYPE=INNODB;


CREATE TABLE role(
  role_id int(5) PRIMARY KEY AUTO_INCREMENT,
  role_name varchar(50),
  role_desc varchar(200),
  role_created_on datetime,
  role_priority int(2))TYPE=INNODB;


CREATE TABLE task(
  task_id int(5) primary key auto_increment,
  task_title varchar(150),
  task_desc varchar(200),
  url_ varchar(250)
)Type=Innodb;


CREATE TABLE role_task(
  role_id int(5),
  task_id int(5),
  primary key(role_id,task_id),
  index(role_id),
  index(task_id),
  foreign key(role_id) references role(role_id),
  foreign key(task_id) references task(task_id))TYPE=INNODB;


CREATE TABLE user (
  id varchar(30) PRIMARY KEY auto_increment,
  username varchar(25),
  password varchar(100),
  department int(5),
  contact_no varchar(20),
  email varchar(50),
  last_name varchar(50),
  first_name varchar(50),
  address varchar(255),
  index(department),
  FOREIGN KEY  (department) REFERENCES department(id)
) TYPE=INNODB;


CREATE TABLE user_role_task(
  user_id varchar(30),
  role_id int(5),
  task_id int(5),
  primary key(user_id,role_id,task_id),
  index(user_id),
  index(role_id),
  index(task_id),
  foreign key(user_id) references user(id),
  foreign key(role_id) references role(role_id),
  foreign key(task_id) references task(task_id))TYPE=INNODB;


CREATE TABLE rights (
  rightid int(2) PRIMARY KEY auto_increment,
  description varchar(255)) TYPE=INNODB;


CREATE TABLE file_status(
  id int(5) PRIMARY KEY auto_increment,
  status varchar(100))TYPE=INNODB;


CREATE TABLE data (
  id int(11) PRIMARY KEY auto_increment,
  category int(5) ,
  owner int(11) ,
  realname varchar(255) ,
  created datetime NOT NULL default '0000-00-00 00:00:00',
  description varchar(255) default NULL,
  comment varchar(255) NOT NULL default '',
  status int(6),
  department int(5) ,
  publishable bool,
  reviewer int(11) ,
  reviewer_comments varchar(255) default NULL,
  version varchar(20),
  index(category),
  index(owner),
  index(status),
  index(department),
  FOREIGN KEY(category) REFERENCES category(id),
  FOREIGN KEY(owner) REFERENCES user(id),
  FOREIGN KEY(status) REFERENCES file_status(id),
  FOREIGN KEY(department) REFERENCES department(id)
) TYPE=INNODB;


CREATE TABLE user_file_perms (
  file_id int(11),
  user_id varchar(30),
  rights int(2),
  PRIMARY KEY (file_id,user_id,rights),
  index(file_id ),
  index(user_id),
  index(rights),
  FOREIGN KEY(file_id) REFERENCES data(id),
  FOREIGN KEY(user_id ) REFERENCES user(id),
  FOREIGN KEY(rights) REFERENCES data(rightid)
) TYPE=INNODB;


CREATE TABLE log_type(
  id int(2) PRIMARY KEY auto_increment,
  log_type varchar(30))TYPE=INNODB;

CREATE TABLE log (
  id int(11) PRIMARY KEY auto_increment,
  log_type int(2)
  logged_on datetime NOT NULL default '0000-00-00 00:00:00',
  logged_by varchar(25) default NULL,
  index(lag_type),
  FOREIGN KEY log_type REFERENCES log_type (id)) TYPE=INNODB;