CREATE TABLE `sessions`(
    `id` INT(11)  NOT NULL AUTO_INCREMENT,
    `ipaddress` VARCHAR(12) NULL,
    `state` VARCHAR(4) NULL,
    `username` INT DEFAULT NULL,
    `signal_id` INT DEFAULT NULL,
    `datesignal` DATETIME DEFAULT NULL,
    `created_at` DATETIME DEFAULT NULL,
    `updated_at` DATETIME DEFAULT NULL,
    PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `tmp_formstep1` 
 (
    `id` INT(11)  NOT NULL AUTO_INCREMENT,
    `sessions_id` INT(11)   NULL,
    `ref_inc`    VARCHAR(50) NULL,
    `code_surv`  VARCHAR(50) NULL,
    `code_user`  VARCHAR(50) NULL,
    `date_ent`   DATETIME DEFAULT NULL,
    `date_inc`   DATETIME DEFAULT NULL,
    `dec_inc`    VARCHAR(50) NULL,
    `surv`       VARCHAR(50) NULL,
    `autr_per`   VARCHAR(50) NULL,
    `created_at` DATETIME DEFAULT NULL,
    `updated_at` DATETIME DEFAULT NULL,
    PRIMARY KEY (`id`)
 )ENGINE=InnoDB DEFAULT CHARSET=utf8;

 CREATE TABLE `tmp_formstep2` 
 (
    `id` INT AUTO_INCREMENT NOT NULL,
    `date_nais`     DATE NULL,
    `sexe`          VARCHAR(5) NULL,
    `ethnie`        VARCHAR(50) NULL,
    `pays_org`      VARCHAR(50) NULL,
    `nation`        VARCHAR(50) NULL,
    `religion`      VARCHAR(50) NULL,
    `etat_civ`      VARCHAR(50) NULL,
    `nbre_enf`      INT NULL,
    `prof`          VARCHAR(50) NULL,
    `stat_depl`     VARCHAR(50) NULL,
    `est_hand`      VARCHAR(50) NULL,
    `est_min`       VARCHAR(50) NULL,
    `surv_viseul`   VARCHAR(50) NULL,
    `lien_parent`   VARCHAR(50) NULL,
    `autre_lien`    VARCHAR(50) NULL,
    `activ_princ`   VARCHAR(50) NULL,
    `created_at`    DATETIME DEFAULT NULL,
    `updated_at`    DATETIME DEFAULT NULL
 );


CREATE TABLE `tmp_formstep3` 
 (
    `id` INT AUTO_INCREMENT NOT NULL,
    `desc_inc`          TEXT NULL,
    `phase_depl`        VARCHAR(50) NULL,
    `autre_phsdepl`     VARCHAR(50) NULL,
    `heure_inc`         VARCHAR(50) NULL,
    `lieu_inc`          VARCHAR(50) NULL,
    `autlieu_inc`       VARCHAR(50) NULL,
    `zone_inc`          VARCHAR(50) NULL,
    `autzone_inc`       VARCHAR(50) NULL,
    `souzon_inc`        VARCHAR(50) NULL,
    `autsouzon_inc`     VARCHAR(50) NULL,
    `camvil_inc`        VARCHAR(50) NULL,
    `autcamvil_inc`     VARCHAR(50) NULL,
    `typ_viol`          VARCHAR(50) NULL,
    `inc_prej`          VARCHAR(50) NULL,
    `echange`           VARCHAR(50) NULL,
    `typ_enlev`         VARCHAR(50) NULL,
    `inc_declaill`      VARCHAR(50) NULL,
    `type_prestdecl`    VARCHAR(50) NULL,
    `vict_prec`         VARCHAR(50) NULL,
    `desc_cons`         TEXT NULL,
    `created_at`        DATETIME DEFAULT NULL,
    `updated_at`        DATETIME DEFAULT NULL
 );


CREATE TABLE `tmp_formstep4` 
 (
    `id` INT AUTO_INCREMENT NOT NULL,
    `nbre_prsum`        INT NULL,
    `sex_agress`        VARCHAR(5) NULL,
    `nat_agress`        VARCHAR(50) NULL,
    `ethn_agress`       VARCHAR(50) NULL,
    `trch_ageagres`     VARCHAR(50) NULL,
    `lienp_agress`      VARCHAR(50) NULL,
    `activ_agress`      VARCHAR(50) NULL,  
    `created_at`        DATETIME DEFAULT NULL,
    `updated_at`        DATETIME DEFAULT NULL
 );



CREATE TABLE `tmp_formstep5` 
 (
    `id` INT AUTO_INCREMENT NOT NULL,
    `addres_benef`      VARCHAR(50) NULL,
    `prec_addben`       VARCHAR(50) NULL,
    `renv_refg`         VARCHAR(50) NULL,
    `pourq_refg`        VARCHAR(50) NULL,
    `date_rdvrfg`       VARCHAR(50) NULL,
    `nlieu_rfg`         VARCHAR(50) NULL,
    `note_lieurfg`      TEXT NULL,
    `renv_servsant`     VARCHAR(50) NULL,
    `pourq_servsant`    VARCHAR(50) NULL,
    `date_rdvsant`      VARCHAR(50) NULL,
    `nlieu_sant`        VARCHAR(50) NULL,
    `note_lieusant`     TEXT NULL,
    `renv_servpsych`    VARCHAR(50) NULL,
    `pourq_soutpsych`   VARCHAR(50) NULL,
    `date_rdvsoutpsych` DATETIME NULL,
    `lieu_rdvsoutpsych` VARCHAR(50) NULL,
    `note_rdvsoutpsych` VARCHAR(50) NULL,
    `eng_just`          VARCHAR(50) NULL,
    `renv_servjurd`     VARCHAR(50) NULL,
    `pourq_servjurd`    VARCHAR(50) NULL,
    `date_rdvservjurd`  VARCHAR(50) NULL,
    `lieu_rdvservjurd`  VARCHAR(50) NULL,
    `note_rdvservjurd`  VARCHAR(50) NULL,
    `renv_police`       VARCHAR(50) NULL,
    `pourq_renvpol`     VARCHAR(50) NULL,
    `date_rdvrenpol`    VARCHAR(50) NULL,
    `lieu_rdvrenpol`    VARCHAR(50) NULL,
    `note_rdvrenpol`    VARCHAR(50) NULL,
    `renv_prgsout`      VARCHAR(50) NULL,
    `pourq_pgmsout`     VARCHAR(50) NULL,
    `date_revpgmsoute`   VARCHAR(50) NULL,
    `lieu_revpgmsoute`   VARCHAR(50) NULL,
    `note_revpgmsoute`   VARCHAR(50) NULL,
    `created_at`        DATETIME DEFAULT NULL,
    `updated_at`        DATETIME DEFAULT NULL
 );


CREATE TABLE `tmp_formstep6` 
 (
    `id` INT AUTO_INCREMENT NOT NULL,
    `etatem_debent`     TEXT NULL,
    `ben_ensec`         VARCHAR(5) NULL,
    `pq_passec`         TEXT NULL,
    `mes_prise`         TEXT NULL,
    `etatem_finent`     TEXT NULL,
    `qsoutem_ben`       TEXT NULL,
    `info_pert`         TEXT NULL,
    `exp_consviolb`     VARCHAR(50) NULL,
    `exp_consviolp`     VARCHAR(50) NULL,
    `created_at`        DATETIME DEFAULT NULL,
    `updated_at`        DATETIME DEFAULT NULL
 );


DELIMITER |
CREATE  TRIGGER `update_etat`
AFTER INSERT ON etat_cas 
FOR EACH ROW
BEGIN
    DECLARE etat_code VARCHAR(50);
    
    select `code` into etat_code from etat e where e.id=new.etat_id;
    
    update `signal` set dernetat=etat_code, ddernetat=now(), userc=new.user1 where id=NEW.cas_id;
END |