-- Create stations table
CREATE TABLE public.stations (
    station_id text NOT NULL,
    "name" text NOT NULL,
    timezone text NULL,
    latitude float8 NULL,
    longitude float8 NULL,
    created_at timestamptz NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamptz NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT stations_pkey PRIMARY KEY (station_id)
);

-- Create stations_measurements table
CREATE TABLE public.stations_measurements (
    station_id text NOT NULL,
    measurement_timestamp timestamptz NOT NULL,
    humidity float8 NULL,
    temperature float8 NULL,
    wind_speed float8 NULL,
    created_at timestamptz NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamptz NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT stations_measurements_pkey PRIMARY KEY (station_id, measurement_timestamp),
    CONSTRAINT stations_measurements_station_id_fkey FOREIGN KEY (station_id) REFERENCES public.stations(station_id) ON DELETE CASCADE
);

-- Create triggers for automatic updated_at column updates
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_stations_updated_at 
    BEFORE UPDATE ON public.stations 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_stations_measurements_updated_at 
    BEFORE UPDATE ON public.stations_measurements 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Drop existing view if it exists
DROP VIEW IF EXISTS view_measurements_summary;

-- Create measurements summary view
CREATE VIEW view_measurements_summary AS
WITH
-- CTE for calculating the average temperature for the last full week (Monday to Sunday)
last_week_temp AS (
    SELECT
        station_id,
        ROUND(AVG(temperature)::numeric, 2) AS average_temperature
    FROM
        stations_measurements
    WHERE
        measurement_timestamp >= date_trunc('week', NOW()) - INTERVAL '7 days'
        AND measurement_timestamp < date_trunc('week', NOW())
    GROUP BY
        station_id
),
-- CTE for calculating the maximum wind speed change in the last 7 rolling days
last_7_days_wind AS (
    WITH consecutive_measurements AS (
        SELECT
            station_id,
            ABS(wind_speed - LAG(wind_speed, 1) OVER (PARTITION BY station_id ORDER BY measurement_timestamp)) AS wind_speed_change
        FROM
            stations_measurements
        WHERE
            measurement_timestamp >= NOW() - INTERVAL '7 days'
    )
    SELECT
        station_id,
        MAX(wind_speed_change) AS max_wind_speed_change
    FROM
        consecutive_measurements
    GROUP BY
        station_id
)
-- Final SELECT joining the results from the two CTEs
SELECT
    COALESCE(temp.station_id, wind.station_id) AS station_id,
    temp.average_temperature,
    wind.max_wind_speed_change
FROM
    last_week_temp temp
FULL OUTER JOIN
    last_7_days_wind wind ON temp.station_id = wind.station_id;