

-- clients
alter table clients add FOREIGN KEY (activite_code) REFERENCES activites(code)
alter table clients add FOREIGN KEY (user_code) REFERENCES users(code)
alter table clients add FOREIGN KEY (routing_code) REFERENCES routings(code)

alter table clients add FOREIGN KEY (gouvernorat) REFERENCES gouvernorats(code)
alter table clients add FOREIGN KEY (delegation) REFERENCES delegations(code)
alter table clients add FOREIGN KEY (localite) REFERENCES localites(code)


-- gouvernorats localites delegations
alter table delegations add FOREIGN KEY (gouvernerat_code) REFERENCES gouvernorats(code)
alter table localites add FOREIGN KEY (gouvernerat_code) REFERENCES gouvernorats(code)
alter table localites add FOREIGN KEY (delegation_code) REFERENCES delegations(code)





INSERT into recensement_proxim (id, code, image, latitude, longitude, adresse, gouvernorat_code, gouvernorat, delegation_code, delegation, localite_code, localite, code_postale, activite_code, activite, ferme) 
select c.id, c.code, c.profile,c.latitude,c.longitude, c.adresse_facturation, c.gouvernorat, g.libelle, c.delegation, d.libelle, c.localite, l.libelle, l.cp, c.activite_code, a.libelle, c.ferme
from clients as c 
LEFT JOIN gouvernorats as g on g.code = c.gouvernorat
LEFT JOIN delegations as d on d.code = c.delegation
LEFT JOIN localites as l on l.code = c.localite
LEFT JOIN activites as a on a.code = c.activite_code


alter table recensement add FOREIGN KEY (gouvernorat_code) REFERENCES gouvernorats(code)
alter table recensement add FOREIGN KEY (delegation_code) REFERENCES delegations(code)
alter table recensement add FOREIGN KEY (localite_code) REFERENCES localites(code)



/***
 
*/

update recensement_proxim set categorie1='cafe', categorie1_libelle='Café', 
                             categorie2='chips', categorie2_libelle='Chips' , 
                             categorie3='nouilles', categorie3_libelle='Nouilles'


/**
 *
 */

 update recensement_proxim as r set 
	dn_categorie1 = (SELECT exist from rs_resultats_merchs as rm where r.code = rm.client_code and famille_code = 'cafe' order by rm.id DESC Limit 1 ),	
    dn_categorie2 = (SELECT exist from rs_resultats_merchs as rm where r.code = rm.client_code and famille_code = 'chips' order by rm.id DESC Limit 1),
    dn_categorie3 = (SELECT exist from rs_resultats_merchs as rm where r.code = rm.client_code and famille_code = 'nouilles' order by rm.id DESC Limit 1),

    dn_marque_categorie1 = (SELECT rd.exist 
                            FROM rs_resultats_merchs as rm 
                            LEFT JOIN rs_resultats_details as rd on  rm.code = rd.resultats_code
                            where r.code = rm.client_code and rm.famille_code = 'cafe' and rd.marque_code = 'brava' 
                            order by rm.id DESC Limit 1 ),	

    dn_marque_categorie2 = (SELECT rd.exist 
                            FROM rs_resultats_merchs as rm 
                            LEFT JOIN rs_resultats_details as rd on  rm.code = rd.resultats_code
                            where r.code = rm.client_code and rm.famille_code = 'chips' and rd.marque_code = 'lays' 
                            order by rm.id DESC Limit 1 ),	

    dn_marque_categorie3 = (SELECT rd.exist 
                            FROM rs_resultats_merchs as rm 
                            LEFT JOIN rs_resultats_details as rd on  rm.code = rd.resultats_code
                            where r.code = rm.client_code and rm.famille_code = 'nouilles' and rd.marque_code = 'indomie' 
                            order by rm.id DESC Limit 1 )


-- after add table recensement_activites
alter table recensement_proxim add FOREIGN key (activite_code) references recensement_activites(code)


-- gouvernorats
ALTER TABLE `gouvernorats` ADD `dn_categorie1` INT NULL AFTER `nbr_clients_rs`, 
                           ADD `dn_marque_categorie1` INT NULL AFTER `dn_categorie1`, 
                           ADD `dn_categorie2` INT NULL AFTER `dn_marque_categorie1`, 
                           ADD `dn_marque_categorie2` INT NULL AFTER `dn_categorie2`, 
                           ADD `dn_categorie3` INT NULL AFTER `dn_marque_categorie2`, 
                           ADD `dn_marque_categorie3` INT NULL AFTER `dn_categorie3`;
-- delegations
ALTER TABLE `delegations` ADD `dn_categorie1` INT NULL AFTER `nbr_clients_rs`, 
                           ADD `dn_marque_categorie1` INT NULL AFTER `dn_categorie1`, 
                           ADD `dn_categorie2` INT NULL AFTER `dn_marque_categorie1`, 
                           ADD `dn_marque_categorie2` INT NULL AFTER `dn_categorie2`, 
                           ADD `dn_categorie3` INT NULL AFTER `dn_marque_categorie2`, 
                           ADD `dn_marque_categorie3` INT NULL AFTER `dn_categorie3`;
-- localites
ALTER TABLE `localites` ADD `dn_categorie1` INT NULL AFTER `nbr_clients_rs`, 
                           ADD `dn_marque_categorie1` INT NULL AFTER `dn_categorie1`, 
                           ADD `dn_categorie2` INT NULL AFTER `dn_marque_categorie1`, 
                           ADD `dn_marque_categorie2` INT NULL AFTER `dn_categorie2`, 
                           ADD `dn_categorie3` INT NULL AFTER `dn_marque_categorie2`, 
                           ADD `dn_marque_categorie3` INT NULL AFTER `dn_categorie3`;
-- update dn by gouvernorat

update gouvernorats as g set g.dn_categorie1 = (select count(IF(r.dn_categorie1 = 1,r.dn_categorie1, null ))
                                                    from recensement as r 
                                                    where g.code = r.gouvernorat_code),
                            g.dn_categorie2 = (select count(IF(r.dn_categorie2 = 1,r.dn_categorie2, null ))
                                                    from recensement as r 
                                                    where g.code = r.gouvernorat_code),
                            g.dn_categorie3 = (select count(IF(r.dn_categorie3 = 1,r.dn_categorie3, null ))
                                                    from recensement as r 
                                                    where g.code = r.gouvernorat_code),

                            g.dn_marque_categorie1 = (select count(IF(r.dn_marque_categorie1 = 1,r.dn_marque_categorie1, null ))
                                                    from recensement as r 
                                                    where g.code = r.gouvernorat_code),
                            g.dn_marque_categorie2 = (select count(IF(r.dn_marque_categorie2 = 1,r.dn_marque_categorie2, null ))
                                                    from recensement as r 
                                                    where g.code = r.gouvernorat_code),
                            g.dn_marque_categorie3 = (select count(IF(r.dn_marque_categorie3 = 1,r.dn_marque_categorie3, null ))
                                                    from recensement as r 
                                                    where g.code = r.gouvernorat_code);

-- update dn by delegation

update delegations as g set g.dn_categorie1 = (select count(IF(r.dn_categorie1 = 1,r.dn_categorie1, null ))
                                                    from recensement as r 
                                                    where g.code = r.delegation_code),
                            g.dn_categorie2 = (select count(IF(r.dn_categorie2 = 1,r.dn_categorie2, null ))
                                                    from recensement as r 
                                                    where g.code = r.delegation_code),
                            g.dn_categorie3 = (select count(IF(r.dn_categorie3 = 1,r.dn_categorie3, null ))
                                                    from recensement as r 
                                                    where g.code = r.delegation_code),

                            g.dn_marque_categorie1 = (select count(IF(r.dn_marque_categorie1 = 1,r.dn_marque_categorie1, null ))
                                                    from recensement as r 
                                                    where g.code = r.delegation_code),
                            g.dn_marque_categorie2 = (select count(IF(r.dn_marque_categorie2 = 1,r.dn_marque_categorie2, null ))
                                                    from recensement as r 
                                                    where g.code = r.delegation_code),
                            g.dn_marque_categorie3 = (select count(IF(r.dn_marque_categorie3 = 1,r.dn_marque_categorie3, null ))
                                                    from recensement as r 
                                                    where g.code = r.delegation_code);

-- update dn by delegation

update localites as g set g.dn_categorie1 = (select count(IF(r.dn_categorie1 = 1,r.dn_categorie1, null ))
                                                    from recensement as r 
                                                    where g.code = r.localite_code),
                            g.dn_categorie2 = (select count(IF(r.dn_categorie2 = 1,r.dn_categorie2, null ))
                                                    from recensement as r 
                                                    where g.code = r.localite_code),
                            g.dn_categorie3 = (select count(IF(r.dn_categorie3 = 1,r.dn_categorie3, null ))
                                                    from recensement as r 
                                                    where g.code = r.localite_code),

                            g.dn_marque_categorie1 = (select count(IF(r.dn_marque_categorie1 = 1,r.dn_marque_categorie1, null ))
                                                    from recensement as r 
                                                    where g.code = r.localite_code),
                            g.dn_marque_categorie2 = (select count(IF(r.dn_marque_categorie2 = 1,r.dn_marque_categorie2, null ))
                                                    from recensement as r 
                                                    where g.code = r.localite_code),
                            g.dn_marque_categorie3 = (select count(IF(r.dn_marque_categorie3 = 1,r.dn_marque_categorie3, null ))
                                                    from recensement as r 
                                                    where g.code = r.localite_code);

 -- UPDATE NBR_PDV gouvernorat
 UPDATE gouvernorats set nbr_pdv = nbr_clients_rs