DROP VIEW IF EXISTS pigments_view;

CREATE VIEW pigments_view AS
    SELECT
        EXTRACT(year FROM Samples.date)                             as year,
        Water_area.name                                             as waterArea,
        Photosynthetic_pigments_samples.serial_number               as seqNo
        Station.name                                                as station
        Station_coordinates.latitude                                as latitude
        Station_coordinates.longitude                               as longitude
        to_char(Samples.date, 'YYYY-MM-DD')                         as date
        to_char(Samples.date, 'HH24:MI')                            as time
        COALESCE(Horizon_levels.name,
                 Horizon_levels.upper_horizon_level,
                 Horizon_levels.lower_horizon_level)                as horizon
        Physical_properties.max_depth                               as depth
        CASE
          WHEN
            Physical_props_at_horizon.id_horizon = (
              SELECT id_horizon
              FROM Horizon_levels
              WHERE name like "%пов%")
          THEN Physical_props_at_horizon.upper_temperature
        END                                                         as surfaceTemperature
        CASE
          WHEN
            Physical_props_at_horizon.id_horizon = (
              SELECT id_horizon
              FROM Horizon_levels
              WHERE name like "%дно%")
          THEN Physical_props_at_horizon.lower_temperature
        END                                                         as bottomTemperature
        Physical_properties.transparency                            as transparency
        Photosynthetic_pigments_samples.A(665K)                     as a(665k)
        Photosynthetic_pigments_samples.volume_of_filtered_water    as volumeOfFilteredWater
        Photosynthetic_pigments_samples.chlorophyll_a_concentration as chlA
        Photosynthetic_pigments_samples.chlorophyll_b_concentration as chlB
        Photosynthetic_pigments_samples.chlorophyll_c_concentration as chlC
        Photosynthetic_pigments_samples.pigment_index               as pigmentIndex
        Photosynthetic_pigments_samples.pheopigments                as pheopigments
        Photosynthetic_pigments_samples.ratio_of_chl_a_to_chl_c     as chlAToChlC
        Trophic_characterization_of_water.name                      as trophicCharacteristics
        Photosynthetic_pigments_samples.comment                     as notes

    FROM Photosynthetic_pigments_samples
    INNER JOIN Samples ON
        Samples.id_sample = Photosynthetic_pigments_samples.id_sample
    INNER JOIN Station ON
        Station.id_station = Samples.id_station
    INNER JOIN Water_area ON
        Water_area.id_water_area = Station.id_water_area
    INNER JOIN Station_coordinates ON
        Station_coordinates.id_sample = Station.id_station
    INNER JOIN Horizon_levels ON
        Horizon_levels.id_horizon = Photosynthetic_pigments_samples.id_horizon
    INNER JOIN Physical_properties ON
        Physical_properties.id_sample = Samples.id_sample
    INNER JOIN Physical_props_at_horizon ON
        Physical_props_at_horizon.id_physical_props =
        Physical_properties.id_physical_props
    INNER JOIN Trophic_characterization_of_water ON
        Trophic_characterization_of_water.id_trophic_characterization =
        Photosynthetic_pigments_samples.id_trophic_characterization


    WHERE
        /*    for latitude and longitude      */
        Station_coordinates.date <= Samples.date;