CREATE DEFINER=`routines_user`@`localhost` PROCEDURE `_Duplicate_site`(IN in_site_id INT, IN in_new_site_name VARCHAR(45), IN in_site_uuid VARCHAR(36))
BEGIN
    DECLARE new_site_id INT;

    INSERT INTO psn.sites (site_uuid, attente, display_waiting, remplissage, display_occupation, calendar_type, time_zone, country_code)
    SELECT in_site_uuid, attente, display_waiting, remplissage, display_occupation, calendar_type, time_zone, country_code
    FROM psn.sites
    WHERE site_id = in_site_id;

    SET new_site_id = LAST_INSERT_ID();

    INSERT INTO portail.contacts (type, firstname, lastname, phone_number, email, site_id)
    SELECT type, firstname, lastname, phone_number, email, new_site_id
    FROM portail.contacts
    WHERE site_id = in_site_id;

    INSERT INTO portail.site_created_by (user_id, site_id)
    SELECT user_id, new_site_id
    FROM portail.site_created_by
    WHERE site_id = in_site_id;

    INSERT INTO psn.details (detail_code, detail, url, site_id)
    SELECT detail_code, detail, url, new_site_id
    FROM psn.details
    WHERE site_id = in_site_id;

    INSERT INTO psn.informations (site_name, secondary_name, site_type, capacity, latitude, longitude, address, zip_code, city, region, country, web_site, email, phone_number, site_id)
    SELECT in_new_site_name, secondary_name, site_type, capacity, latitude, longitude, address, zip_code, city, region, country, web_site, email, phone_number, new_site_id
    FROM psn.informations
    WHERE site_id = in_site_id;

    INSERT INTO psn.services (service_code, available, site_id)
    SELECT service_code, available, new_site_id
    FROM psn.services
    WHERE site_id = in_site_id;

    INSERT INTO psn.timetables_default (opening_day, opening_time, closing_day, closing_time, site_id)
    SELECT opening_day, opening_time, closing_day, closing_time, new_site_id
    FROM psn.timetables_default
    WHERE site_id = in_site_id;
END