-- Get stations from old logar database

select 
'insert into stations (id, farm_id, type_id, name, code, mac_address, hash_md5, lat, lng, is_online, timezone, created_at, updated_at) values (' ||
"Id" || ',' || 1 || ',' || 1 || ',!@#$' || "Name" || '!@#$,!@#$' || "Code" || '!@#$,!@#$' || "MacAddress" || '!@#$,!@#$' || "HashMD5" || '!@#$,!@#$' || "Latitude" || '!@#$,!@#$' || "Longitude" || '!@#$,' ||
"OnlineStatus" || ',!@#$' || coalesce("StationTimezone", 'America/Sao_Paulo') || '!@#$,!@#$' || "CreatedAt" || '!@#$,!@#$' || "UpdatedAt" || '!@#$);'
from "Station"
order by "Id" asc;

-- Renomeia as colunas
ALTER TABLE "Measurement" RENAME COLUMN "StationId" TO station_id
ALTER TABLE "Measurement" RENAME COLUMN "Id" TO id
ALTER TABLE "Measurement" RENAME COLUMN "DateUtc" TO dateutc
ALTER TABLE "Measurement" RENAME COLUMN "TemperatureIndoorsF" TO temp_hub
ALTER TABLE "Measurement" RENAME COLUMN "HumidityIndoors" TO humidity_hub;
ALTER TABLE "Measurement" RENAME COLUMN "RelativePressure" TO relative_pressure_hub;
ALTER TABLE "Measurement" RENAME COLUMN "AbsolutePressure" TO absolute_pressure_hub;
ALTER TABLE "Measurement" RENAME COLUMN "TemperatureF" TO temp;
ALTER TABLE "Measurement" RENAME COLUMN "Humidity" TO humidity;
ALTER TABLE "Measurement" RENAME COLUMN "WindDirection" TO wind_direction;
ALTER TABLE "Measurement" RENAME COLUMN "WindSpeedMph" TO wind_speed;
ALTER TABLE "Measurement" RENAME COLUMN "Ultraviolet" TO uv;
ALTER TABLE "Measurement" RENAME COLUMN "HourlyRain" TO hourly_rain;
ALTER TABLE "Measurement" RENAME COLUMN "RainRate" TO rate_rain;
ALTER TABLE "Measurement" RENAME COLUMN "EventRain" TO event_rain;
ALTER TABLE "Measurement" RENAME COLUMN "DailyRain" TO daily_rain;
ALTER TABLE "Measurement" RENAME COLUMN "WeeklyRain" TO weekly_rain;
ALTER TABLE "Measurement" RENAME COLUMN "MonthlyRain" TO monthly_rain;
ALTER TABLE "Measurement" RENAME COLUMN "YearlyRain" TO yearly_rain;
ALTER TABLE "Measurement" RENAME COLUMN "RainDelta" TO rain_accum;
ALTER TABLE "Measurement" RENAME COLUMN "CreatedAt" TO created_at;
ALTER TABLE "Measurement" RENAME COLUMN "UpdatedAt" TO updated_at;
ALTER TABLE "Measurement" RENAME COLUMN "SolarRadiation" TO solar_radiation;

-- Adiciona coluna wind_gust que estava faltando
ALTER TABLE "Measurement" ADD COLUMN wind_gust double precision;

-- Remove as colunas não utilizadas
ALTER TABLE "Measurement" DROP COLUMN "HashedMacAddress"
ALTER TABLE "Measurement" DROP COLUMN "DeletedAt";
ALTER TABLE "Measurement" DROP COLUMN "IsDeleted";
ALTER TABLE "Measurement" DROP COLUMN "PlotId";
ALTER TABLE "Measurement" DROP COLUMN "UserId";

-- Atualiza a coluna de dateutc para conter a timestamp sem fuso horário
ALTER TABLE "Measurement" ADD COLUMN coluna_temp timestamp without time zone;
UPDATE "Measurement" SET coluna_temp = dateutc AT TIME ZONE 'UTC';
ALTER TABLE "Measurement" DROP COLUMN dateutc;
ALTER TABLE "Measurement" RENAME COLUMN coluna_temp TO dateutc;
ALTER TABLE "Measurement" ADD COLUMN dateutc_aux timestamp without time zone;
UPDATE "Measurement" set dateutc_aux = dateutc;

-- Renomeia a tabela
ALTER TABLE "Measurement" RENAME TO measurements;

ALTER TABLE measurements
ADD CONSTRAINT measurements_station_id_foreign
FOREIGN KEY (station_id)
REFERENCES public.stations (id)
ON UPDATE NO ACTION
ON DELETE NO ACTION;


alter table measurements alter column temp_hub type double precision;
alter table measurements alter column humidity_hub type double precision;
alter table measurements alter column relative_pressure_hub type double precision;
alter table measurements alter column absolute_pressure_hub type double precision;
alter table measurements alter column temp type double precision;
alter table measurements alter column humidity type double precision;
alter table measurements alter column wind_speed type double precision;
alter table measurements alter column wind_gust type double precision;
alter table measurements alter column max_daily_gust type double precision;
alter table measurements alter column solar_radiation type double precision;
alter table measurements alter column uv type double precision;
alter table measurements alter column rate_rain type double precision;
alter table measurements alter column event_rain type double precision;
alter table measurements alter column hourly_rain type double precision;
alter table measurements alter column daily_rain type double precision;
alter table measurements alter column weekly_rain type double precision;
alter table measurements alter column monthly_rain type double precision;
alter table measurements alter column yearly_rain type double precision;
alter table measurements alter column rain_accum type double precision;

alter table measurements add column max_daily_gust double precision default null;

alter table measurements alter column uv drop not null;
alter table measurements alter column rate_rain drop not null;
alter table measurements alter column event_rain drop not null;
alter table measurements alter column hourly_rain drop not null;
alter table measurements alter column daily_rain drop not null;
alter table measurements alter column weekly_rain drop not null;
alter table measurements alter column monthly_rain drop not null;
alter table measurements alter column yearly_rain drop not null;


-- change sequence check current sequence value
SELECT setval('stations_id_seq', 216, true);

CREATE INDEX idx_station_id ON measurements(station_id);





------------------------------ TABELA DE USERS  ----------------------------------
-- Renomeia as colunas
ALTER TABLE "Users" RENAME COLUMN "Id" TO id;
ALTER TABLE "Users" RENAME COLUMN "Email" TO email;
ALTER TABLE "Users" RENAME COLUMN "Password" TO password;
ALTER TABLE "Users" RENAME COLUMN "Name" TO name;
ALTER TABLE "Users" RENAME COLUMN "CreatedAt" TO created_at;
ALTER TABLE "Users" RENAME COLUMN "UpdatedAt" TO updated_at;
ALTER TABLE "Users" RENAME COLUMN "Role" TO group_id;

-- Altera os roles de cada usuário para seguir o novo ENUM, 
UPDATE "Users"
SET group_id = CASE
    WHEN group_id = 2 THEN 1
    ELSE 2
END;

-- Retira as colunas não mais utilizadas
ALTER TABLE "Users" DROP COLUMN "IsAlreadyOnboard";
ALTER TABLE "Users" DROP COLUMN "Uid";
ALTER TABLE "Users" DROP COLUMN "Photo";
ALTER TABLE "Users" DROP COLUMN "IsDeleted";
ALTER TABLE "Users" DROP COLUMN "DeletedAt";

-- Atualiza os tipos das colunas
ALTER TABLE "Users" ALTER COLUMN name TYPE VARCHAR(255);
ALTER TABLE "Users" ALTER COLUMN password TYPE VARCHAR(255);

-- Converte o updated_at e created_at para não utilizar a timezone
ALTER TABLE "Users" ADD COLUMN coluna_temp timestamp without time zone;
UPDATE "Users" SET coluna_temp = created_at AT TIME ZONE 'UTC';
ALTER TABLE "Users" DROP COLUMN created_at;
ALTER TABLE "Users" RENAME COLUMN coluna_temp TO created_at;

ALTER TABLE "Users" ADD COLUMN coluna_temp timestamp without time zone;
UPDATE "Users" SET coluna_temp = updated_at AT TIME ZONE 'UTC';
ALTER TABLE "Users" DROP COLUMN updated_at;
ALTER TABLE "Users" RENAME COLUMN coluna_temp TO updated_at;

-- Adiciona as novas colunas
ALTER TABLE "Users" ADD COLUMN is_active BOOLEAN;
ALTER TABLE "Users" ADD COLUMN profile_url VARCHAR(255);
ALTER TABLE "Users" ADD COLUMN email_verified_at timestamp without time zone;
ALTER TABLE "Users" ADD COLUMN reset_password BOOLEAN;
ALTER TABLE "Users" ADD COLUMN remember_token VARCHAR(100);

-- Adiciona os valores padrão
UPDATE "Users" set is_active = true;
UPDATE "Users" set reset_password = true;

-- Atualiza os usuários com emails duplicados para concaternar um numero ao final de copias
update "Users"
   set email = concat(t.email, t.rn)
from (
  select 
  	id,
	email, 
    row_number(*) over (partition by email order by id) as rn
  from "Users"
) t
where t.id = "Users".id
  and t.rn > 1;

-- Adiciona as CONSTRAINTS

ALTER TABLE "Users" 
ADD CONSTRAINT users_group_id_foreign 
FOREIGN KEY ( group_id ) 
REFERENCES public.groups (id)
ON UPDATE NO ACTION
ON DELETE NO ACTION;

ALTER TABLE "Users"
ADD CONSTRAINT users_email_unique UNIQUE (email); 

ALTER TABLE "Users" ALTER COLUMN id SET NOT NULL;
ALTER TABLE "Users" ALTER COLUMN group_id SET NOT NULL;
ALTER TABLE "Users" ALTER COLUMN name SET NOT NULL;
ALTER TABLE "Users" ALTER COLUMN email SET NOT NULL;
ALTER TABLE "Users" ALTER COLUMN is_active SET NOT NULL;
ALTER TABLE "Users" ALTER COLUMN reset_password SET NOT NULL;


ALTER TABLE "Users" ALTER COLUMN is_active SET DEFAULT true;
ALTER TABLE "Users" ALTER COLUMN reset_password SET DEFAULT true;

-- Renomeia a tabela
alter table "Users" rename to users
