CREATE DEFINER=`root`@localhost PROCEDURE `load_client_data`()
BEGIN
    DECLARE id INT;
    DECLARE wiek INT;
    DECLARE zarobki INT;
    DECLARE kraj TEXT;
    DECLARE plec TEXT;

    DECLARE max_id_staging_area INT DEFAULT 0;
    DECLARE max_id_data_warehouse INT DEFAULT 0;
    DECLARE count_rows INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;

    SET max_id_staging_area = (SELECT MAX(klient_id) FROM staging_area.klient);
    SET max_id_data_warehouse = (SELECT MAX(KLIENT_ID) FROM data_warehouse.KLIENT);

    WHILE i <= max_id_staging_area DO
        SET count_rows = (SELECT COUNT(*) FROM max_id_staging_area.klient);
        IF count_rows > 0 THEN
            SET id = (SELECT klient_id FROM staging_area.klient WHERE klient_id = i);
            SET wiek = (SELECT przedzial_wiekowy FROM staging_area.klient WHERE klient_id = i);
            SET zarobki = (SELECT przedzial_zarobkow FROM staging_area.klient WHERE klient_id = i);
            SET plec = (SELECT plec FROM staging_area.klient WHERE klient_id = i);
            SET kraj = (SELECT kraj FROM staging_area.klient WHERE klient_id = i);

            IF (id IS NOT NULL) AND (wiek IS NOT NULL) AND (zarobki IS NOT NULL) AND (plec IS NOT NULL) AND (kraj IS NOT NULL) THEN
                IF max_id_data_warehouse IS NULL THEN
                    INSERT INTO data_warehouse.klient VALUES (id + max_id_data_warehouse, wiek, zarobki, plec, kraj);
                ELSE
                    INSERT INTO data_warehouse.klient VALUES (id, wiek, zarobki, plec, kraj);
                END IF;
            END IF;
        END IF;
        SET i = i + 1;
    END WHILE;
END