/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     06/08/2010 15:47:05                          */
/*==============================================================*/


drop table if exists authority;

drop table if exists authority_charge;

drop table if exists process;

drop table if exists profile;

drop table if exists profile_process;

drop table if exists school_setup;

drop table if exists user;

drop table if exists user_profile;

/*==============================================================*/
/* Table: authority                                             */
/*==============================================================*/
create table authority
(
   serial_aut           int not null auto_increment,
   serial_atc           int,
   degree_aut           varchar(50) not null,
   first_name_aut       varchar(100) not null,
   last_name_aut        varchar(100) not null,
   in_charge_aut        enum('YES','NO') not null,
   primary key (serial_aut)
)
type = innodb;

/*==============================================================*/
/* Table: authority_charge                                      */
/*==============================================================*/
create table authority_charge
(
   serial_atc           int not null auto_increment,
   name_atc             varchar(100) not null,
   description_atc      text,
   status_atc           enum('ACTIVE','INACTIVE') not null,
   primary key (serial_atc)
)
type = innodb;

/*==============================================================*/
/* Table: process                                               */
/*==============================================================*/
create table process
(
   serial_prc           int not null auto_increment,
   prc_serial_prc       int,
   name_prc             varchar(150) not null,
   link_prc             varchar(1024) not null,
   weight_prc           decimal(5,1) not null,
   primary key (serial_prc)
)
type = innodb;

/*==============================================================*/
/* Table: profile                                               */
/*==============================================================*/
create table profile
(
   serial_prf           int not null auto_increment,
   name_prf             varchar(150) not null,
   type_prf             enum('INTERNAL','EXTERNAL') not null,
   status_prf           enum('ACTIVE','INACTIVE') not null,
   primary key (serial_prf)
)
type = innodb;

/*==============================================================*/
/* Table: profile_process                                       */
/*==============================================================*/
create table profile_process
(
   serial_prf           int,
   serial_prc           int
)
type = innodb;

/*==============================================================*/
/* Table: school_setup                                          */
/*==============================================================*/
create table school_setup
(
   name_sst             varchar(1024) not null,
   country_sst          varchar(100) not null,
   state_sst            varchar(100) not null,
   city_sst             varchar(100) not null,
   address_sst          text not null,
   phone1_sst           varchar(20) not null,
   phone2_sst           varchar(20),
   phone3_sst           varchar(20),
   currency_sst         varchar(10) not null,
   contact_name_sst     varchar(250) not null,
   contact_phone_sst    varchar(20) not null,
   logo_sst             varchar(100) not null,
   slogan_sst           varchar(300),
   main_color_sst       varchar(7) not null,
   secondary_color_sst  varchar(7) not null
)
type = innodb;

/*==============================================================*/
/* Table: user                                                  */
/*==============================================================*/
create table user
(
   serial_usr           int not null auto_increment,
   username_usr         varchar(150) not null,
   password_usr         varchar(300),
   first_name_usr       varchar(150) not null,
   last_name_usr        varchar(150) not null,
   primary key (serial_usr)
)
type = innodb;

/*==============================================================*/
/* Table: user_profile                                          */
/*==============================================================*/
create table user_profile
(
   serial_prf           int,
   serial_usr           int
)
type = innodb;

alter table authority add constraint fk_has_charge foreign key (serial_atc)
      references authority_charge (serial_atc) on delete restrict on update restrict;

alter table process add constraint fk_parent_process foreign key (prc_serial_prc)
      references process (serial_prc) on delete restrict on update restrict;

alter table profile_process add constraint fk_has_process foreign key (serial_prf)
      references profile (serial_prf) on delete restrict on update restrict;

alter table profile_process add constraint fk_in_profile foreign key (serial_prc)
      references process (serial_prc) on delete restrict on update restrict;

alter table user_profile add constraint fk_for_user foreign key (serial_prf)
      references profile (serial_prf) on delete restrict on update restrict;

alter table user_profile add constraint fk_has_profile foreign key (serial_usr)
      references user (serial_usr) on delete restrict on update restrict;