create table 'utilisateur' (
`id_ut` bigint(20) NOT NULL AUTO_INCREMENT,
 `login` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
   `passe` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `nom` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `prenom` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
   `type` char(6) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id_ut`),
  constraint CK_type  check( type in ( 'admin' , 'client ','vendeur' ) )
  constraint fk_utilisateur foreign key (id_comp)
references compte 
constraint fk_utilisateur foreign key (id_con)
references contact
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

create table 'admin' (
`id_ut` bigint(20) NOT NULL AUTO_INCREMENT,
 constraint PK_admin primary key (id_ut),
   constraint fk_admin foreign key (id_ut)
references utilisateur 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

  create table 'client' (
`id_ut` bigint(70) NOT NULL AUTO_INCREMENT,
 `adresse` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `date_naissance` date  COLLATE utf8_unicode_ci  NULL,
    `telephone` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
   constraint PK_client primary key (id_ut),
   constraint fk_client foreign key (id_ut)
references utilisateur 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

create table 'vendeur' (
`id_ut` bigint(70) NOT NULL AUTO_INCREMENT,
 `pays` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `ville` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
   `telephone_profes` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `telephone_personal` varchar(30) COLLATE utf8_unicode_ci  NULL,
  `type_societe` varchar(20) COLLATE utf8_unicode_ci  NULL,
   `adresse_site` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `domaine_vente` varchar(30) COLLATE utf8_unicode_ci  NULL,
     constraint PK_vendeur primary key (id_ut),
   constraint fk_vendeur foreign key (id_ut)
references utilisateur 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

create table 'compte' (
`id_comp` bigint(20) NOT NULL AUTO_INCREMENT,
`solde` double(50) 0,000 COLLATE utf8_unicode_ci NOT NULL,
 primary key (id_comp),
 constraint fk_compte foreign key (id_m)
references message 

  
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
create table 'operation_compte' (
`id_comp` bigint(20) NOT NULL AUTO_INCREMENT,
`id_o` bigint(20) NOT NULL AUTO_INCREMENT,
 constraint pk_op_comp primary key (id_comp,id_o)
 constraint fk_compte foreign key (id_comp)
references compte
 constraint fk_operation foreign key (id_o)
references compte
  
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

create table 'operation' (
`id_o` bigint(20) NOT NULL AUTO_INCREMENT,
 `type_operation` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `etat` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
    `date` date COLLATE utf8_unicode_ci  NOT NULL,
    primary key (id_o),
   constraint fk_operation foreign key (id_comp)
references compte
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

create table 'debit' (
`id_d` bigint(20) NOT NULL AUTO_INCREMENT,
 `type_d` varchar(20) COLLATE utf8_unicode_ci NOT NULL,

    primary key (id_d),
   constraint fk_debit foreign key (id_o)
references operation
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

create table 'credit' (
`id_d` bigint(20) NOT NULL AUTO_INCREMENT,
 `type_c` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
    primary key (id_d),
   constraint fk_credit foreign key (id_o)
references operation
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

create table 'transfert' (
`id_t` bigint(20) NOT NULL AUTO_INCREMENT,
 `numero_compte` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `montant` double(20) COLLATE utf8_unicode_ci NOT NULL,
    `date` date COLLATE utf8_unicode_ci  NOT NULL,
    primary key (id_t),
   constraint fk_transfert foreign key (id_c)
references credit
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



create table 'transaction' (
`id_s` bigint(20) NOT NULL AUTO_INCREMENT,
 `montant` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `etat_s` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
    
    primary key (id_s),
   constraint fk_transaction foreign key (id_o)
references operation
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

create table 'cheque' (
`id_ch` bigint(20) NOT NULL AUTO_INCREMENT,
 `nom_destinateur` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `montant` double(20) COLLATE utf8_unicode_ci NOT NULL,
 `numero_cheque` int(20) COLLATE utf8_unicode_ci NOT NULL,
 `numero_compte` int(20) COLLATE utf8_unicode_ci NOT NULL,
 `lieu` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
    `date` date COLLATE utf8_unicode_ci  NOT NULL,
    primary key (id_ch),
   constraint fk_cheque foreign key (id_d)
references debit
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


create table 'carte' (
`id_car` bigint(20) NOT NULL AUTO_INCREMENT,
 `type_carte` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `numero_carte` bigint(20) COLLATE utf8_unicode_ci NOT NULL,
 `mot_passe` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `code` int(3) COLLATE utf8_unicode_ci NOT NULL,
 `date_expire` date COLLATE utf8_unicode_ci  NOT NULL,
    primary key (id_car),
   constraint fk_carte foreign key (id_d)
references debit
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

create table 'virement' (
`id_v` bigint(20) NOT NULL AUTO_INCREMENT,
 `delai` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `numero_compte` int(20) COLLATE utf8_unicode_ci NOT NULL,
 `montant` double(20) COLLATE utf8_unicode_ci NOT NULL,
    
    primary key (id_v),
   constraint fk_virement foreign key (id_d)
references debit
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


create table 'contact' (
`id_cont` bigint(20) NOT NULL AUTO_INCREMENT,
 `email` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `nom` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `sujet` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `message` text(20) COLLATE utf8_unicode_ci NOT NULL,
    primary key (id_cont),
   
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

create table 'message`' (
`id_m` bigint(20) NOT NULL AUTO_INCREMENT,
 `email` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `sujet` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `message` text(20) COLLATE utf8_unicode_ci NOT NULL,
    primary key (id_m),
   
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


create table 'banque' (
`id_b` bigint(20) NOT NULL AUTO_INCREMENT,
 `numero_compte` bigint(20) COLLATE utf8_unicode_ci NOT NULL,
 `nom_de_banque` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `agence` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 primary key (id_b),
   constraint fk_banque foreign key (id_comp)
references compte
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;