/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     16/08/2010 17:21:00                          */
/*==============================================================*/


drop table if exists academic_area;

drop table if exists authority;

drop table if exists authority_charge;

drop table if exists country;

drop table if exists employee;

drop table if exists guardian;

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 state;

drop table if exists student;

drop table if exists student_guardian;

drop table if exists user;

drop table if exists user_profile;

/*==============================================================*/
/* Table: academic_area                                         */
/*==============================================================*/
create table academic_area
(
   serial_aca           int not null auto_increment,
   name_aca             varchar(150) not null,
   description_aca      text not null,
   status_aca           enum('ACTIVE','INACTIVE') not null,
   primary key (serial_aca)
)
type = innodb;

/*==============================================================*/
/* 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: country                                               */
/*==============================================================*/
create table country
(
   serial_cou           int not null auto_increment,
   name_cou             national varchar(100),
   status_cou           enum('ACTIVE','INACTIVE') default 'ACTIVE',
   primary key (serial_cou)
)
type = innodb;

/*==============================================================*/
/* Table: employee                                              */
/*==============================================================*/
create table employee
(
   serial_emp           int not null auto_increment,
   serial_grd           int,
   serial_ste           int,
   serial_cou           int,
   serial_usr           int not null,
   id_emp               varchar(13) not null,
   type_emp             set('TEACHING','ADMINISTRATIVE','SERVICES'),
   status_emp           enum('ACTIVE','INACTIVE') not null default 'ACTIVE',
   first_name_emp       varchar(255) not null,
   last_name_emp        varchar(255) not null,
   degree_emp           varchar(150),
   gender_emp           enum('MALE','FEMALE') not null,
   marital_status       enum('SINGLE','MARRIED','DIVORCED','WIDOWED','UNMARRIED PARTNERS'),
   birthdate_emp        date not null,
   nationality_emp      varchar(150) not null,
   address_emp          text not null,
   parish_emp           varchar(100),
   sector_emp           varchar(50),
   phone_emp            varchar(13) not null,
   phone2_emp           varchar(13),
   phone3_emp           varchar(13),
   cellphone_emp        varchar(13),
   email_emp            varchar(150),
   city_emp             varchar(150) not null,
   primary key (serial_emp)
)
type = innodb;

/*==============================================================*/
/* Table: guardian                                              */
/*==============================================================*/
create table guardian
(
   serial_grd           int not null auto_increment,
   serial_emp           int,
   serial_usr           int not null,
   id_grd               varchar(13) not null,
   primary key (serial_grd)
)
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,
   owner_prf            enum('SYSTEM','USER') not null default 'USER',
   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: state                                                 */
/*==============================================================*/
create table state
(
   serial_ste           int not null auto_increment,
   serial_cou           int,
   name_ste             varchar(100) not null,
   status_ste           enum('ACTIVE','INACTIVE') default 'ACTIVE',
   primary key (serial_ste)
)
type = innodb;

/*==============================================================*/
/* Table: student                                               */
/*==============================================================*/
create table student
(
   serial_std           int not null auto_increment,
   serial_usr           int not null,
   id_std               varchar(13) not null,
   primary key (serial_std)
)
type = innodb;

/*==============================================================*/
/* Table: student_guardian                                      */
/*==============================================================*/
create table student_guardian
(
   serial_grd           int,
   serial_std           int
)
type = innodb;

/*==============================================================*/
/* Table: user                                                  */
/*==============================================================*/
create table user
(
   serial_usr           int not null auto_increment,
   serial_grd           int,
   serial_std           int,
   serial_emp           int,
   username_usr         varchar(150) not null,
   password_usr         varchar(300),
   photo_usr            varchar(1024),
   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 employee add constraint fk_employee_birth_country foreign key (serial_cou)
      references country (serial_cou) on delete restrict on update restrict;

alter table employee add constraint fk_employee_birth_state foreign key (serial_ste)
      references state (serial_ste) on delete restrict on update restrict;

alter table employee add constraint fk_employee_guardian2 foreign key (serial_grd)
      references guardian (serial_grd) on delete restrict on update restrict;

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

alter table guardian add constraint fk_employee_guardian foreign key (serial_emp)
      references employee (serial_emp) on delete restrict on update restrict;

alter table guardian add constraint fk_guardian_user foreign key (serial_usr)
      references user (serial_usr) 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 state add constraint fk_country_state foreign key (serial_cou)
      references country (serial_cou) on delete restrict on update restrict;

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

alter table student_guardian add constraint fk_has_guardian foreign key (serial_std)
      references student (serial_std) on delete restrict on update restrict;

alter table student_guardian add constraint fk_is_guardian foreign key (serial_grd)
      references guardian (serial_grd) on delete restrict on update restrict;

alter table user add constraint fk_employee_user2 foreign key (serial_emp)
      references employee (serial_emp) on delete restrict on update restrict;

alter table user add constraint fk_guardian_user2 foreign key (serial_grd)
      references guardian (serial_grd) on delete restrict on update restrict;

alter table user add constraint fk_student_user2 foreign key (serial_std)
      references student (serial_std) 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;