/*==============================================================*/
/* GID - MySQL 5.0                                              */
/*==============================================================*/

alter database `gidy` default character set utf8 collate utf8_general_ci;
set foreign_key_checks = 0;
drop table if exists t_img_inclou_sw;
drop table if exists t_model_inclou_hw;
drop table if exists t_pc_inclou_hw;
drop table if exists t_pc_inclou_sw;
drop table if exists t_swi_x_swi;
drop table if exists t_adsl;
drop table if exists t_armari;
drop table if exists t_centraleta;
drop table if exists t_compatible;
drop table if exists t_consumeix;
drop table if exists t_consumible;
drop table if exists t_disp_personal;
drop table if exists t_edifici;
drop table if exists t_estadistica;
drop table if exists t_hardware;
drop table if exists t_imatge;
drop table if exists t_model_equip;
drop table if exists t_pc_portatil;
drop table if exists t_periferic;
drop table if exists t_planta;
drop table if exists t_pt_acces_wifi;
drop table if exists t_pt_centraleta;
drop table if exists t_pt_pp;
drop table if exists t_pt_switch;
drop table if exists t_p_centraleta;
drop table if exists t_p_panel;
drop table if exists t_roseta;
drop table if exists t_router;
drop table if exists t_segment;
drop table if exists t_servidor;
drop table if exists t_software;
drop table if exists t_switch;
drop table if exists t_s_panel;
drop table if exists t_telefons;
drop table if exists t_ubicacio;
drop table if exists t_usuari;
drop table if exists t_vlan;
set foreign_key_checks = 1;


/*==============================================================*/
/* Table: T_ADSL                                                */
/*==============================================================*/
create table t_adsl
(
   dsl_id               smallint not null auto_increment,
   tel_id               smallint,
   dsl_vel              numeric(4,2),
   primary key (dsl_id),
   unique key uk_dsl_tel (tel_id)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_ARMARI                                              */
/*==============================================================*/
create table t_armari
(
   arm_id               smallint not null auto_increment,
   arm_num              char(2) not null,
   ed_id                smallint not null,
   ubi_id               smallint,
   arm_mid              char(3),
   arm_sai              bool,
   primary key (arm_id),
   unique key uk_arm_num (arm_num, ed_id)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_CENTRALETA                                          */
/*==============================================================*/
create table t_centraleta
(
   cen_id               smallint not null auto_increment,
   cen_mar              varchar(50),
   cen_mod              varchar(50),
   cen_ext              tinyint,
   primary key (cen_id)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_COMPATIBLE                                          */
/*==============================================================*/
create table t_compatible
(
   com_id               smallint not null auto_increment,
   img_id               smallint not null,
   mod_id               smallint not null,
   primary key (com_id),
   unique key uk_img_mod (img_id, mod_id)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_CONSUMEIX                                           */
/*==============================================================*/
create table t_consumeix
(
   conx_id              smallint not null auto_increment,
   per_id               smallint not null,
   con_id               smallint not null,
   conx_data            timestamp not null default current_timestamp,
   conx_copy            numeric(6,0),
   primary key (conx_id),
   unique key ak_perif_consum (per_id, con_id, conx_data)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_CONSUMIBLE                                          */
/*==============================================================*/
create table t_consumible
(
   con_id               smallint not null auto_increment,
   con_codi		varchar(15) not null,
   con_des              varchar(100),
   con_col              enum('Black','Tricolor','Yellow','Magenta','Cyan'),
   con_cap              numeric(6,0),
   primary key (con_id)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_DISP_PERSONAL                                       */
/*==============================================================*/
create table t_disp_personal
(
   dp_id                smallint not null auto_increment,
   dp_desc              text,
   dp_usu               varchar(50),
   dp_mail              varchar(50) not null,
   dp_mac               char(12) not null,
   dp_ip                char(15) not null,
   dp_mask              char(15),
   dp_mac2              char(12),
   primary key (dp_id),
   unique key uk_dp_mail (dp_mail),
   unique key uk_dp_mac (dp_mac),
   unique key uk_dp_ip (dp_ip)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_EDIFICI                                             */
/*==============================================================*/
create table t_edifici
(
   ed_id                smallint not null auto_increment,
   ed_nom               varchar(50) not null,
   ed_adre              varchar(200),
   primary key (ed_id),
   unique key uk_ed_nom (ed_nom)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_ESTADISTICA                                         */
/*==============================================================*/
create table t_estadistica
(
   est_id               smallint not null auto_increment,
   seg_id               smallint,
   est_data             date not null,
   est_hora             time not null,
   est_promig           numeric(7,2),
   primary key (est_id)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_HARDWARE                                            */
/*==============================================================*/
create table t_hardware
(
   hw_id                smallint not null auto_increment,
   hw_nom               varchar(50) not null,
   hw_desc              varchar(200),
   hw_vel               numeric(7,2),
   hw_cap               numeric(7,2),
   hw_int               varchar(6),
   primary key (hw_id),
   unique key uk_hw_nom (hw_nom)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_IMATGE                                              */
/*==============================================================*/
create table t_imatge
(
   img_id               smallint not null auto_increment,
   img_nom              varchar(50) not null,
   img_data             timestamp not null default current_timestamp,
   img_so               varchar(20),
   img_desc             longtext,
   primary key (img_id)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_IMG_INCLOU_SW                                       */
/*==============================================================*/
create table t_img_inclou_sw
(
   iis_id               smallint not null auto_increment,
   sw_id                smallint not null,
   img_id               smallint not null,
   primary key (iis_id),
   unique key uk_img_sw (sw_id, img_id)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_MODEL_EQUIP                                         */
/*==============================================================*/
create table t_model_equip
(
   mod_id               smallint not null auto_increment,
   mod_mar              varchar(50) not null,
   mod_mod              varchar(50) not null,
   mod_cpu              char(15),
   mod_chi              char(15),
   mod_vga              varchar(50),
   mod_ram              numeric(4,0),
   mod_hd               numeric(4,0),
   primary key (mod_id),
   unique key uk_marca_mod (mod_mar, mod_mod)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_MODEL_INCLOU_HW                                     */
/*==============================================================*/
create table t_model_inclou_hw
(
   mh_id                smallint not null auto_increment,
   hw_id                smallint not null,
   mod_id               smallint not null,
   primary key (mh_id),
   unique key uk_hw_mod (hw_id, mod_id)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_PC_INCLOU_HW                                        */
/*==============================================================*/
create table t_pc_inclou_hw
(
   pih_id               smallint not null auto_increment,
   pc_id                smallint,
   hw_id                smallint not null,
   primary key (pih_id),
   unique key uk_pc_hw (pc_id, hw_id)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_PC_INCLOU_SW                                        */
/*==============================================================*/
create table t_pc_inclou_sw
(
   pis_id               smallint not null auto_increment,
   pc_id                smallint,
   sw_id                smallint not null,
   primary key (pis_id),
   unique key uk_pc_sw (pc_id, sw_id)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_PC_PORTATIL                                         */
/*==============================================================*/
create table t_pc_portatil
(
   pc_id                smallint not null auto_increment,
   pc_cod               varchar(10) not null,
   ubi_id               smallint,
   img_id               smallint,
   mod_id               smallint,
   host_id              smallint,
   ros_id               smallint,
   pc_mac               char(12) not null,
   pc_ip                char(15) not null,
   pc_mac2              char(12),
   pc_pas               char(15),
   pc_est               char(3) default 'ok',
   primary key (pc_id),
   unique key uk_mac (pc_mac),
   unique key uk_ip (pc_ip),
   unique key uk_mac2 (pc_mac2),
   unique key uk_roseta (ros_id),
   unique key uk_ubi_codi (pc_cod, ubi_id)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_PERIFERIC                                           */
/*==============================================================*/
create table t_periferic
(
   per_id               smallint not null auto_increment,
   ser_id               smallint,
   pc_id                smallint,
   ros_id               smallint,
   per_nom              varchar(50),
   per_mar              varchar(50),
   per_mod              varchar(50),
   per_con              char(6),
   per_con2             char(6),
   per_est              char(3),
   per_ip               char(15),
   per_mask             char(15),
   primary key (per_id)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_PLANTA                                              */
/*==============================================================*/
create table t_planta
(
   pla_id               smallint not null auto_increment,
   ed_id                smallint not null,
   pla_num              char(2) not null,
   pla_nom              varchar(200),
   primary key (pla_id),
   unique key uk_pla_nom (ed_id, pla_num)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_PT_ACCES_WIFI                                       */
/*==============================================================*/
create table t_pt_acces_wifi
(
   paw_id               smallint not null auto_increment,
   ubi_id               smallint,
   ros_id               smallint,
   paw_ma               varchar(200),
   paw_mo               varchar(200),
   paw_firm             char(15),
   paw_poe              bool,
   paw_pro              char(3),
   paw_mac              char(12) not null,
   paw_ip4              char(15) not null,
   paw_mask             char(15),
   paw_carac            varchar(200),
   primary key (paw_id),
   unique key uk_paw_ip (paw_ip4),
   unique key uk_paw_mac (paw_mac)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_PT_CENTRALETA                                       */
/*==============================================================*/
create table t_pt_centraleta
(
   ptc_id               smallint not null auto_increment,
   ptc_num              tinyint not null,
   pce_id               smallint,
   tel_id               smallint,
   ptc_ext              char(3),
   primary key (ptc_id),
   unique key uk_num_pce (ptc_num, pce_id)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_PT_PP                                               */
/*==============================================================*/
create table t_pt_pp
(
   ppp_id               smallint not null auto_increment,
   ppp_num              numeric(3,0) not null,
   pp_id                smallint,
   ptc_id               smallint comment 'si esta en pt switch no pot estar a pt centraleta',
   pts_id               smallint comment 'si esta en pt centraleta no pot estar a pt switch',
   ppp_est              char(3),
   primary key (ppp_id),
   unique key uk_num_pp (ppp_num, pp_id)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_PT_SWITCH                                           */
/*==============================================================*/
create table t_pt_switch
(
   pts_id               smallint not null auto_increment,
   pts_num              tinyint not null,
   sp_id                smallint,
   vl_id                smallint,
   pts_hab              bool,
   primary key (pts_id),
   unique key uk_sp_num (pts_num, sp_id)
)
type = InnoDB auto_increment = 1;


/*==============================================================*/
/* Table: T_P_CENTRALETA                                        */
/*==============================================================*/
create table t_p_centraleta
(
   pce_id               smallint not null auto_increment,
   pce_num              smallint not null,
   cen_id               smallint not null,
   arm_id               smallint not null,
   primary key (pce_id),
   unique key uk_armari (arm_id, pce_num)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_P_PANEL                                             */
/*==============================================================*/
create table t_p_panel
(
   pp_id                smallint not null auto_increment,
   pp_num               smallint not null,
   arm_id               smallint not null,
   pp_tip               char(3) default 'eth',
   primary key (pp_id),
   unique key uk_armari (arm_id, pp_num)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_ROSETA                                              */
/*==============================================================*/
create table t_roseta
(
   ros_id               smallint not null auto_increment,
   ubi_id               smallint,
   ppp_id               smallint,
   ros_pos              varchar(6) not null,
   primary key (ros_id),
   unique key uk_ubi_ppp (ppp_id)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_ROUTER                                              */
/*==============================================================*/
create table t_router
(
   rou_id               smallint not null auto_increment,
   ser_id               smallint,
   dsl_id               smallint,
   rou_mar              varchar(50),
   rou_mod              varchar(50),
   rou_ip4i             char(15) not null,
   rou_maski            char(15),
   rou_ip4e             char(15),
   rou_maske            char(15),
   rou_pro              char(3),
   rou_por              tinyint,
   primary key (rou_id),
   unique key uk_rou_ipe (rou_ip4e),
   unique key uk_rou_ipi (rou_ip4i)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_SEGMENT                                             */
/*==============================================================*/
create table t_segment
(
   seg_id               smallint not null auto_increment,
   ed_id                smallint not null,
   seg_ip               char(15) not null,
   seg_mask             char(15) not null,
   seg_desc             varchar(200),
   vl_id                smallint,
   primary key (seg_id),
   unique key uk_edifici_ip (ed_id, seg_ip)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_SERVIDOR                                            */
/*==============================================================*/
create table t_servidor
(
   ser_id               smallint not null auto_increment,
   ubi_id               smallint,
   ros_id               smallint,
   ser_des              varchar(200),
   ser_mac              char(12) not null,
   ser_ip               char(15) not null,
   ser_mask             char(15),
   ser_mac2             char(12) not null,
   ser_ip2              char(15) not null,
   ser_mask2            char(15),
   primary key (ser_id),
   key ak_ser_mac_uk (ser_mac),
   key ak_ser_ip_uk (ser_ip),
   key ak_ser_mac2_uk (ser_mac2),
   key ak_ser_ip2_uk (ser_ip2)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_SOFTWARE                                            */
/*==============================================================*/
create table t_software
(
   sw_id                smallint not null auto_increment,
   sw_nom               varchar(50),
   sw_desc              varchar(200),
   sw_tipus             enum('Disseny','Educatiu','Internet','Multimedia','Ofimatic','Sistema','S.O.'),
   sw_llic              varchar(50),
   sw_preu              numeric(7,2),
   sw_req               varchar(200),
   sw_obs               longtext,
   primary key (sw_id)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_SWITCH                                              */
/*==============================================================*/
create table t_switch
(
   swi_id               smallint not null auto_increment,
   arm_id               smallint,
   pts_id               smallint,
   swi_sn               varchar(50) not null,
   swi_mac              char(12) not null,
   swi_mar              varchar(15),
   swi_mod              varchar(15),
   swi_fr               bool,
   swi_poe              bool,
   swi_ip               char(15) not null,
   swi_mask             char(15),
   swi_conf             longtext,
   primary key (swi_id),
   unique key uk_swi_sn (swi_sn),
   unique key uk_swi_mac (swi_mac)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_SWI_X_SWI                                           */
/*==============================================================*/
create table t_swi_x_swi
(
   sxs_id               smallint not null auto_increment,
   swi_ida              smallint,
   swi_idb              smallint,
   primary key (sxs_id),
   unique key uk_swi_swi (swi_ida, swi_idb)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_S_PANEL                                             */
/*==============================================================*/
create table t_s_panel
(
   sp_id                smallint not null auto_increment,
   sp_num               char(1),
   swi_id               smallint,
   sp_vel               enum('10','100','1gb'),
   sp_tip               enum('Fibra','Eth'),
   sp_ok                bool,
   sp_ref               varchar(50) not null,
   primary key (sp_id),
   unique key uk_num_swi (sp_num, swi_id),
   unique key uk_sp_ref (sp_ref)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_TELEFONS                                            */
/*==============================================================*/
create table t_telefons
(
   tel_id               smallint not null auto_increment,
   tel_num              char(9) not null,
   cen_id               smallint,
   primary key (tel_id),
   unique key uk_tel_num (tel_num)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_UBICACIO                                            */
/*==============================================================*/
create table t_ubicacio
(
   ubi_id               smallint not null auto_increment,
   ed_id                smallint not null,
   ubi_cod              char(6) not null,
   pla_id               smallint not null,
   ubi_nom              varchar(50) not null,
   ubi_des              varchar(200),
   primary key (ubi_id),
   unique key uk_edif_ubi (ed_id, ubi_cod)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_USUARI                                              */
/*==============================================================*/
create table t_usuari
(
   us_id                smallint not null auto_increment,
   us_mail              varchar(50) not null,
   us_nom               varchar(50) not null,
   us_pass		varchar(50) not null,
   us_per               char(3),
   primary key (us_id),
   key uk_us_email (us_mail)
)
type = InnoDB auto_increment = 1;

/*==============================================================*/
/* Table: T_VLAN                                                */
/*==============================================================*/
create table t_vlan2
(
   vl_id                smallint not null auto_increment,
   ed_id                smallint not null,
   vl_nom               varchar(50) not null,
   vl_desc              varchar(200),
   primary key (vl_id),
   unique key uk_nom_edif (ed_id, vl_nom)
)
type = InnoDB auto_increment = 1;

alter table t_adsl add constraint fk_telf_adsl foreign key (tel_id)
      references t_telefons (tel_id) on delete restrict on update cascade;

alter table t_armari add constraint fk_edif_armari foreign key (ed_id)
      references t_edifici (ed_id) on delete restrict on update cascade;

alter table t_armari add constraint fk_ubica_armari foreign key (ubi_id)
      references t_ubicacio (ubi_id) on delete restrict on update cascade;

alter table t_compatible add constraint fk_img_compatible foreign key (img_id)
      references t_imatge (img_id) on delete restrict on update cascade;

alter table t_compatible add constraint fk_mod_compatible foreign key (mod_id)
      references t_model_equip (mod_id) on delete restrict on update cascade;

alter table t_consumeix add constraint fk_con_consumeix foreign key (con_id)
      references t_consumible (con_id) on delete restrict on update cascade;

alter table t_consumeix add constraint fk_perif_consumeix foreign key (per_id)
      references t_periferic (per_id) on delete restrict on update cascade;

alter table t_estadistica add constraint fk_seg_estadist foreign key (seg_id)
      references t_segment (seg_id) on delete restrict on update cascade;

alter table t_img_inclou_sw add constraint fk_sw_iisw foreign key (sw_id)
      references t_software (sw_id) on delete restrict on update cascade;

alter table t_img_inclou_sw add constraint fk_img_iisw foreign key (img_id)
      references t_imatge (img_id) on delete restrict on update cascade;

alter table t_model_inclou_hw add constraint fk_hw_mihw foreign key (hw_id)
      references t_hardware (hw_id) on delete restrict on update cascade;

alter table t_model_inclou_hw add constraint fk_mod_mihw foreign key (mod_id)
      references t_model_equip (mod_id) on delete restrict on update cascade;

alter table t_pc_inclou_hw add constraint fk_pc_pcihw foreign key (pc_id)
      references t_pc_portatil (pc_id) on delete restrict on update cascade;

alter table t_pc_inclou_hw add constraint fk_hw_pcihw foreign key (hw_id)
      references t_hardware (hw_id) on delete restrict on update cascade;

alter table t_pc_inclou_sw add constraint fk_pc_pcisw foreign key (pc_id)
      references t_pc_portatil (pc_id) on delete restrict on update cascade;

alter table t_pc_inclou_sw add constraint fk_sw_pcisw foreign key (sw_id)
      references t_software (sw_id) on delete restrict on update cascade;

alter table t_pc_portatil add constraint fk_img_pc foreign key (img_id)
      references t_imatge (img_id) on delete restrict on update cascade;

alter table t_pc_portatil add constraint fk_mod_pc foreign key (mod_id)
      references t_model_equip (mod_id) on delete restrict on update cascade;

alter table t_pc_portatil add constraint fk_roseta_pc foreign key (ros_id)
      references t_roseta (ros_id) on delete restrict on update cascade;

alter table t_pc_portatil add constraint fk_ubica_pc foreign key (ubi_id)
      references t_ubicacio (ubi_id) on delete restrict on update cascade;

alter table t_pc_portatil add constraint fk_virtual_pc foreign key (host_id)
      references t_pc_portatil (pc_id) on delete restrict on update cascade;

alter table t_periferic add constraint fk_pc_perif foreign key (pc_id)
      references t_pc_portatil (pc_id) on delete restrict on update cascade;

alter table t_periferic add constraint fk_roseta_perif foreign key (ros_id)
      references t_roseta (ros_id) on delete restrict on update cascade;

alter table t_periferic add constraint fk_serv_perif foreign key (ser_id)
      references t_servidor (ser_id) on delete restrict on update cascade;

alter table t_planta add constraint fk_edif_planta foreign key (ed_id)
      references t_edifici (ed_id) on delete restrict on update cascade;

alter table t_pt_acces_wifi add constraint fk_roseta_paw foreign key (ros_id)
      references t_roseta (ros_id) on delete restrict on update cascade;

alter table t_pt_acces_wifi add constraint fk_ubica_paw foreign key (ubi_id)
      references t_ubicacio (ubi_id) on delete restrict on update cascade;

alter table t_pt_centraleta add constraint fk_pce_ptc foreign key (pce_id)
      references t_p_centraleta (pce_id) on delete restrict on update cascade;

alter table t_pt_centraleta add constraint fk_tel_ptc foreign key (tel_id)
      references t_telefons (tel_id) on delete restrict on update cascade;

alter table t_pt_pp add constraint fk_panel_ppp foreign key (pp_id)
      references t_p_panel (pp_id) on delete restrict on update cascade;

alter table t_pt_pp add constraint fk_ptc_ppp foreign key (ptc_id)
      references t_pt_centraleta (ptc_id) on delete restrict on update cascade;

alter table t_pt_pp add constraint fk_pts_ppp foreign key (pts_id)
      references t_pt_switch (pts_id) on delete restrict on update cascade;

alter table t_pt_switch add constraint fk_sp_pts foreign key (sp_id)
      references t_s_panel (sp_id) on delete restrict on update cascade;

alter table t_pt_switch add constraint fk_vlan_pts foreign key (vl_id)
      references t_vlan (vl_id) on delete restrict on update cascade;

alter table t_p_centraleta add constraint fk_armari_pce foreign key (arm_id)
      references t_armari (arm_id) on delete restrict on update cascade;

alter table t_p_centraleta add constraint fk_centraleta_pcen foreign key (cen_id)
      references t_centraleta (cen_id) on delete restrict on update cascade;

alter table t_p_panel add constraint fk_armari_ppanel foreign key (arm_id)
      references t_armari (arm_id) on delete restrict on update cascade;

alter table t_roseta add constraint fk_ppp_roseta foreign key (ppp_id)
      references t_pt_pp (ppp_id) on delete restrict on update cascade;

alter table t_roseta add constraint fk_ubi_roseta foreign key (ubi_id)
      references t_ubicacio (ubi_id) on delete restrict on update cascade;

alter table t_router add constraint fk_adsl_router foreign key (dsl_id)
      references t_adsl (dsl_id) on delete restrict on update cascade;

alter table t_router add constraint fk_proxy_router foreign key (ser_id)
      references t_servidor (ser_id) on delete restrict on update cascade;

alter table t_segment add constraint fk_edif_seg foreign key (ed_id)
      references t_edifici (ed_id) on delete restrict on update cascade;

alter table t_segment add constraint fk_vlan_seg foreign key (vl_id)
      references t_vlan (vl_id) on delete restrict on update cascade;

alter table t_servidor add constraint fk_roseta_serv foreign key (ros_id)
      references t_roseta (ros_id) on delete restrict on update cascade;

alter table t_servidor add constraint fk_ubica_serv foreign key (ubi_id)
      references t_ubicacio (ubi_id) on delete restrict on update cascade;

alter table t_switch add constraint fk_armari_swi foreign key (arm_id)
      references t_armari (arm_id) on delete restrict on update cascade;

alter table t_switch add constraint fk_monitor_swi foreign key (pts_id)
      references t_pt_switch (pts_id) on delete restrict on update cascade;

alter table t_swi_x_swi add constraint fk_swia_sxs foreign key (swi_ida)
      references t_switch (swi_id) on delete restrict on update cascade;

alter table t_swi_x_swi add constraint fk_swib_sxs foreign key (swi_idb)
      references t_switch (swi_id) on delete restrict on update cascade;

alter table t_s_panel add constraint fk_swi_sp foreign key (swi_id)
      references t_switch (swi_id) on delete restrict on update cascade;

alter table t_telefons add constraint fk_cen_telf foreign key (cen_id)
      references t_centraleta (cen_id) on delete restrict on update cascade;

alter table t_ubicacio add constraint fk_edif_ubi foreign key (ed_id)
      references t_edifici (ed_id) on delete restrict on update cascade;

alter table t_ubicacio add constraint fk_pla_ubica foreign key (pla_id)
      references t_planta (pla_id) on delete restrict on update cascade;

alter table t_vlan add constraint fk_edif_vlan foreign key (ed_id)
      references t_edifici (ed_id) on delete restrict on update cascade;

/*==============================================================*/
/* view: v_ubicacio                                             */
/*==============================================================*/
create or replace view v_ubicacio as
select ed_nom Edifici, pla_num Planta, ubi_id Id, ubi_cod Codi, ubi_nom Nom, ubi_des Descripcio
from t_ubicacio u, t_planta p, t_edifici e
where u.pla_id = p.pla_id
and u.ed_id = e.ed_id
order by u.ed_id, u.pla_id, u.ubi_cod
;

/*==============================================================*/
/* View: v_armari                                               */
/*==============================================================*/
create or replace view v_armari as
select Edifici, Planta, Codi Ubicacio, arm_id Id, arm_num Numero, arm_mid Mida, arm_sai SAI
from v_ubicacio vu, t_armari a
where vu.id = a.ubi_id
order by ??
;

/*==============================================================*/
/* View: v_pc                                                   */
/*==============================================================*/
create or replace view v_pc as
select Edifici, Planta, Codi Ubicacio, PC_ID Id, PC_COD Codi, img_nom Imatge, mod_mar Marca, mod_mod Model, pc_mac MAC, pc_ip IP, pc_mac2 MAC2, pc_pas Password, pc_est Estat
from v_ubicacio vu, t_pc_portatil p, t_imatge i, t_model_equip m
where vu.id = p.ubi_id
and p.img_id = i.img_id
and p.mod_id = m.mod_id
order by ??
;

/*==============================================================*/
/* view: v_pc_sw                                                */
/*==============================================================*/
create or replace view v_pc_sw as
select p.pc_id Id, pc_cod Codi, sw_nom sw
from t_pc_portatil p, t_imatge i, t_img_inclou_sw iis, t_software s
where p.img_id = i.img_id
and i.img_id = iis.img_id
and iis.sw_id = s.sw_id
UNION
select p.pc_id Id, pc_cod Codi, sw_nom sw
from t_pc_portatil p, t_pc_inclou_sw pis, t_software s
where p.pc_id = pis.pc_id
and pis.sw_id = s.sw_id
order by ??
;

/*==============================================================*/
/* view: v_pc_hw                                                */
/*==============================================================*/
create or replace view v_pc_hw as
select p.pc_id Id, pc_cod Codi, hw_nom hw
from t_pc_portatil p, t_model_equip m, t_model_inclou_hw mih, t_hardware h
where p.mod_id = m.mod_id
and m.mod_id = mih.mod_id
and mih.hw_id = h.hw_id
UNION
select p.pc_id Id, pc_cod Codi, hw_nom hw
from t_pc_portatil p, t_pc_inclou_hw pih, t_hardware h
where p.pc_id = pih.pc_id
and pih.hw_id = h.hw_id
order by ??
;

/*==============================================================*/
/* Trigger: tgr_ppp_soc_bu                                      */
/*==============================================================*/
/* Si els 2 camps estan informats es deixa el valor antic       */
/*==============================================================*/
delimiter //
CREATE TRIGGER tgr_ppp_soc_bu BEFORE UPDATE ON t_pt_pp
  FOR EACH ROW 
  BEGIN
    IF NEW.pts_id IS NOT NULL AND NEW.ptc_id IS NOT NULL THEN
    	SET NEW.pts_id = OLD.pts_id;
        SET NEW.ptc_id = OLD.ptc_id;
    END IF;
  END//
delimiter ;

/*==============================================================*/
/* Trigger: tgr_ppp_soc_bi                                      */
/*==============================================================*/
/* Si els 2 camps estan informats nomes es deixa 1 camp         */
/*==============================================================*/
delimiter //
CREATE TRIGGER tgr_ppp_soc_bi BEFORE INSERT ON t_pt_pp
  FOR EACH ROW 
  BEGIN
    IF NEW.pts_id IS NOT NULL AND NEW.ptc_id IS NOT NULL THEN
        SET NEW.ptc_id = NULL;
    END IF;
  END//
delimiter ;

/*==============================================================*/
/* Trigger: tgr_perif_spe_bi                                    */
/*==============================================================*/
/* Si els 3 camps estan informats no es deixa cap camp          */
/*==============================================================*/
delimiter //
CREATE TRIGGER tgr_perif_spe_bi BEFORE INSERT ON t_periferic
  FOR EACH ROW 
  BEGIN
    DECLARE cont numeric;
    SET cont = 0;
    IF NEW.ser_id IS NOT NULL THEN SET cont = cont + 1; END IF;
    IF NEW.pc_id IS NOT NULL THEN SET cont = cont + 1; END IF;
    IF NEW.ros_id IS NOT NULL THEN SET cont = cont + 1; END IF;
    IF cont > 1 THEN
    	SET NEW.ser_id = NULL;
        SET NEW.pc_id = NULL;
	SET NEW.ros_id = NULL;
    END IF;
  END//
delimiter ;

/*==============================================================*/
/* Trigger: tgr_perif_spe_bu                                    */
/*==============================================================*/
/* Si els 3 camps estan informats es deixa el valor antic       */
/*==============================================================*/
delimiter //
CREATE TRIGGER tgr_perif_spe_bu BEFORE UPDATE ON t_periferic
  FOR EACH ROW 
  BEGIN
    DECLARE cont numeric;
    SET cont = 0;
    IF NEW.ser_id IS NOT NULL THEN SET cont = cont + 1; END IF;
    IF NEW.pc_id IS NOT NULL THEN SET cont = cont + 1; END IF;
    IF NEW.ros_id IS NOT NULL THEN SET cont = cont + 1; END IF;
    IF cont > 1 THEN
    	SET NEW.ser_id = OLD.ser_id;
        SET NEW.pc_id = OLD.pc_id;
	SET NEW.ros_id = OLD.ros_id;
    END IF;
  END//
delimiter ;