/**
 * @author Emmanuel
 * @date 09/03/2022
 * @version 1.0.0
 * @project SIEau
 * @table analyses_*
 */
# --- !Ups
CREATE OR REPLACE FUNCTION public.format_analyses_tables(
    p_stationids integer[],
    p_filtreid character varying,
    p_exec boolean)
  RETURNS double precision AS
$BODY$
DECLARE
  my_cursor_stations REFCURSOR;;
  stations RECORD;;
  v_requete VARCHAR (2000);;
  d_count double precision;;
  d_count_alter double precision;;
  d_id_job double precision;;
  v_requete_filtre VARCHAR(2000);;

BEGIN

  IF p_stationIds is not null THEN
    OPEN my_cursor_stations FOR SELECT codequalitometre, codebss FROM qualitometres WHERE codequalitometre = ANY(p_stationIds);;
  ELSE
    IF p_filtreId is not null THEN
      SELECT selectionfiltre INTO v_requete_filtre FROM utilisateurs_listes
        WHERE module = 'SIQ'
          AND codeliste = p_filtreId;;
      OPEN my_cursor_stations FOR EXECUTE v_requete_filtre;;
    ELSE
      OPEN my_cursor_stations FOR SELECT codequalitometre, codebss FROM qualitometres;;
    END IF;;
  END IF;;

  /* Table de logs */
  DROP TABLE IF EXISTS ebonneau_logs_alter;;
  CREATE TABLE ebonneau_logs_alter (id double precision, commentaire text, valeur text, execution_timestamp timestamp, station varchar(255), job_execution_id integer);;

  d_id_job := 0;;
  d_count_alter := 0;;

    LOOP
      FETCH my_cursor_stations into stations;;
      EXIT WHEN NOT FOUND;;
      BEGIN
	  v_requete := '';;

	 	--CHECK TABLE ANALYSES
        BEGIN
          execute 'select count(*) from analyses_'|| lower(stations.codebss) INTO d_count;;
 		  RAISE NOTICE 'TABLE analyses_% EXISTS : %', lower(stations.codebss), d_count;;
        EXCEPTION
          WHEN undefined_table THEN
            d_count := 0;;
            RAISE NOTICE '[UNDEFINED TABLE] Error on table count : %', lower(stations.codebss);;
            INSERT INTO ebonneau_logs_alter VALUES(d_id_job, '[UNDEFINED TABLE] Error on table count '|| lower(stations.codebss), null, now(), lower(stations.codebss), 99999999);;
            d_id_job := d_id_job +1;;
          WHEN OTHERS THEN
            d_count := 0;;
            RAISE NOTICE '[OTHERS] Error on table count : %', stations.codebss;;
            INSERT INTO ebonneau_logs_alter VALUES(d_id_job, '[OTHERS] Error on table count '|| lower(stations.codebss), null, now(), lower(stations.codebss), 99999999);;
            d_id_job := d_id_job +1;;
        END;;

        --DEBUT INTEGRATION


        IF d_count > 0 THEN
		    BEGIN
			  v_requete := 'ALTER TABLE IF EXISTS analyses_'|| lower(stations.codebss) ||' ADD COLUMN IF NOT EXISTS jobexecutionid bigint';;
			  IF p_exec THEN
				EXECUTE v_requete;;
			  ELSE
				INSERT INTO ebonneau_logs_alter VALUES(d_id_job, v_requete, null, now(), lower(stations.codebss), 99999999);;
			  END IF;;
			  d_count_alter := d_count_alter +1;;
            EXCEPTION
	            WHEN undefined_table THEN
				  INSERT INTO ebonneau_logs_alter VALUES(d_id_job, 'STEP 0 : [UNDEFINED TABLE] Error on altering table : ' || v_requete, null, now(), lower(stations.codebss), 99999999);;
	              RAISE NOTICE 'STEP 0 : [UNDEFINED TABLE] Error on altering table : %', v_requete ;;
	            WHEN OTHERS THEN
				  INSERT INTO ebonneau_logs_alter VALUES(d_id_job, 'STEP 0 : [OTHERS] Error on altering table : ' || v_requete, null, now(), lower(stations.codebss), 99999999);;
	              RAISE NOTICE 'STEP 0 : [OTHERS] Error % on altering table : %', SQLERRM, v_requete ;;
            END;;
		end if;;
    END;;
  END LOOP;;

  RAISE NOTICE 'Alter count : %', d_count_alter;;

  CLOSE my_cursor_stations;;

  RETURN d_count_alter;;
EXCEPTION
            WHEN OTHERS THEN
              INSERT INTO ebonneau_logs_alter VALUES(d_id_job, '[OTHERS] Error on altering data : ' || v_requete, null, now(), coalesce(lower(stations.codebss), 'NO_STATION'), 99999999);;
              RAISE NOTICE '[OTHERS] Error % on altering data : %', SQLERRM, v_requete ;;
END;;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;


# --- !Downs
