------------------------------------------------------------
  -- Query - Fishing effort aggregated to 0.1° resolution
  -- author: Nate & Cian and a bit of Jorge Cornejo
  -- date: Nov 13, 2023
  ------------------------------------------------------------
CREATE TEMP FUNCTION
  start_date() AS (CAST('2021-01-01' AS DATE));
CREATE TEMP FUNCTION
  end_date() AS (CAST('2023-10-31' AS DATE)); #Fecha de corte, considerando el delay de GFW para tener los datos
WITH
  ----------------------------------------------------------
  -- Load shapefiles of MPAs
  ----------------------------------------------------------
  mpa AS (
  SELECT
    ST_GEOGFROMTEXT(WKT, make_valid => TRUE) AS polygon,
    Codigo AS zone_id, nombre as Designacion
  FROM
    `world-fishing-827.scratch_JorgeCornejo.galapagosMPA_zoning` ),
  ----------------------------------------------------------
  -- This subquery identifies good segments
  ----------------------------------------------------------
  good_segments AS (
  SELECT
    seg_id
  FROM
    `pipe_production_v20201001.research_segs`
  WHERE
    good_seg
    AND positions > 10
    AND NOT overlapping_and_short),
  ----------------------------------------------------------
  -- Get the list of active fishing vessels that pass the noise filters
  ----------------------------------------------------------
  fishing_vessels AS (
  SELECT
    ssvid,
    year,
    best_flag,
    best_vessel_class
  FROM
    -- IMPORTANT: change below to most up to date table
    `gfw_research.fishing_vessels_ssvid_v20230901` ),
  ----------------------------------------------------------
  -- Query fishing effort but include original lon and lat
  -- positions for spatial joining with mpas
  ----------------------------------------------------------
  fishing AS (
  SELECT
    ssvid,
    lon,
    lat,
    ## We need more resolution for the zoning to works
    #FLOOR(lat * 0.1) AS lat_bin,
    round(lat, 2) AS lat_bin,
    #FLOOR(lon * 0.1) AS lon_bin,
    round(lon, 2) AS lon_bin,
    EXTRACT(date
    FROM
      _partitiontime) AS date,
    EXTRACT(year
    FROM
      _partitiontime) AS year,
    hours,
    nnet_score,
    night_loitering
    -- eez
  FROM
    `pipe_production_v20201001.research_messages`--, aoi
    -- LEFT JOIN UNNEST(regions.eez) AS eez
    -- Restrict query to specific time range
  WHERE
    is_fishing_vessel = TRUE 
    AND CAST(_partitiontime AS DATE) BETWEEN start_date()
    AND end_date()
    -- Use spatial join to restrict to aoi
    --AND ST_CONTAINS(aoi.polygon, ST_GEOGPOINT(lon, lat))
    -- Use good_segments subquery to only include positions from good segments
    AND seg_id IN (
    SELECT
      seg_id
    FROM
      good_segments) ),
  ----------------------------------------------------------
  -- Spatial join with mpas
  ----------------------------------------------------------
  fishing_mpas AS (
  SELECT
    *
  FROM
    fishing a
  LEFT JOIN (
    SELECT
      *
    FROM
      mpa ) b
  ON
    ST_WITHIN(ST_GEOGPOINT(a.lon, a.lat), b.polygon) ),
  ----------------------------------------------------------
  -- Filter fishing to just the list of active fishing vessels in that year
  ----------------------------------------------------------
  fishing_filtered AS (
  SELECT
    *
  FROM
    fishing_mpas
  JOIN
    fishing_vessels
    -- Only keep positions for fishing vessels active that year
  USING
    (ssvid,
      year) ),
  ----------------------------------------------------------
  -- Create fishing_hours attribute. Use night_loitering instead of nnet_score as indicator of fishing for squid jiggers
  ----------------------------------------------------------
  fishing_hours_filtered AS (
  SELECT
    *,
    CASE
      WHEN best_vessel_class = 'squid_jigger' AND night_loitering = 1 THEN hours
      WHEN best_vessel_class != 'squid_jigger'
    AND nnet_score > 0.5 THEN hours
    ELSE
    NULL
  END
    AS fishing_hours
  FROM
    fishing_filtered ),
  ----------------------------------------------------------
  -- This subquery sums fishing hours and converts coordinates back to
  -- decimal degrees
  -- drop lon and lat but keep mpa
  ----------------------------------------------------------
  fishing_binned AS (
  SELECT
    date,
    -- keep ssvid as we're interested in counting number of vessels
    ssvid,
    lat_bin , #/ 0.1 AS lat_bin,
    lon_bin , #/ 0.1 AS lon_bin,
    best_vessel_class,
    best_flag,
    zone_id,
    Designacion,
    SUM(hours) AS hours,
    SUM(fishing_hours) AS fishing_hours,
  FROM
    fishing_hours_filtered
  GROUP BY
    date,
    ssvid,
    lat_bin,
    lon_bin,
    best_vessel_class,
    best_flag,
    zone_id,
    Designacion )
  ----------------------------------------------------------
  -- Return fishing data
  ----------------------------------------------------------
SELECT
  *
FROM
  fishing_binned
WHERE
  zone_id IS NOT NULL -- only within one of the zones.

  ------------------------------------------------------------
  -- Query - Fishing effort aggregated to 0.1° resolution
  -- author: Nate & Cian and a bit of Jorge Cornejo
  -- date: Nov 13, 2023
  ------------------------------------------------------------
CREATE TEMP FUNCTION
  start_date() AS (CAST('2021-01-01' AS DATE));
CREATE TEMP FUNCTION
  end_date() AS (CAST('2023-12-31' AS DATE)); #Fecha de corte, considerando el delay de GFW para tener los datos
WITH
  ----------------------------------------------------------
  -- Load shapefiles of MPAs
  ----------------------------------------------------------
  mpa AS (
  SELECT
    ST_GEOGFROMTEXT(WKT, make_valid => TRUE) AS polygon,
    Codigo AS zone_id, nombre as Designacion
  FROM
    `world-fishing-827.scratch_JorgeCornejo.galapagosMPA_zoning` ),
  ----------------------------------------------------------
  -- This subquery identifies good segments
  ----------------------------------------------------------
  good_segments AS (
  SELECT
    seg_id
  FROM
    `pipe_production_v20201001.research_segs`
  WHERE
    good_seg
    AND positions > 10
    AND NOT overlapping_and_short
    ),
----------------------------------------------------------
-- Add galapagos fishing vessel list with manual MMSI
----------------------------------------------------------
galapagos_vessel_list AS (
  SELECT
  CAST(MMSI AS STRING) AS ssvid
  FROM
  scratch_nate.galapagos_vessel_list_v20240116 --               <-- HERE!! #############
),
  ----------------------------------------------------------
  -- Get the list of active fishing vessels that pass the noise filters
  ----------------------------------------------------------
  fishing_vessels AS (
  SELECT
    ssvid,
    year,
    IFNULL(gfw_best_flag, mmsi_flag) AS best_flag,
    IFNULL(best_vessel_class, prod_geartype) AS best_vessel_class
  FROM
    -- IMPORTANT: change below to most up to date table
   pipe_production_v20201001.all_vessels_byyear_v2_v20231201 -- <- New Table ###########
    --`gfw_research.fishing_vessels_ssvid_v20230901`
    WHERE 
    (on_fishing_list_best OR 
    on_fishing_list_sr OR 
    --#potential_fishing OR 
    ssvid IN (SELECT ssvid FROM galapagos_vessel_list))),
-- SELECT * FROM fishing_vessels WHERE ssvid = '735059559'
  ----------------------------------------------------------
  -- Query fishing effort but include original lon and lat
  -- positions for spatial joining with mpas
  ----------------------------------------------------------
  fishing AS (
  SELECT
    ssvid,
    lon,
    lat,
    ## We need more resolution for the zoning to works
    FLOOR(lat * 100) / 100 AS lat_bin,
    #round(lat, 2) AS lat_bin,
    FLOOR(lon * 100) / 100 AS lon_bin,
    #round(lon, 2) AS lon_bin,
    EXTRACT(date
    FROM
      _partitiontime) AS date,
    EXTRACT(year
    FROM
      _partitiontime) AS year,
    EXTRACT(day
    FROM
      _partitiontime) AS day,
    hours,
    nnet_score,
    night_loitering
    -- eez
  FROM
    `pipe_production_v20201001.research_messages`--, aoi
    -- LEFT JOIN UNNEST(regions.eez) AS eez
    -- Restrict query to specific time range
  WHERE
    lat > -4 and lat < 4 and
    lon > -95 and lon < -87 and
    CAST(_partitiontime AS DATE) BETWEEN start_date()
    AND end_date()
    -- Use spatial join to restrict to aoi
    --AND ST_CONTAINS(aoi.polygon, ST_GEOGPOINT(lon, lat))
    -- Use good_segments subquery to only include positions from good segments
    AND seg_id IN (
    SELECT
      seg_id
    FROM
      good_segments) 
    ),
  ----------------------------------------------------------
  -- Spatial join with mpas
  ----------------------------------------------------------
  fishing_mpas AS (
  SELECT
    *
  FROM
    fishing a
  LEFT JOIN (
    SELECT
      *
    FROM
      mpa ) b
  ON
    ST_WITHIN(ST_GEOGPOINT(a.lon, a.lat), b.polygon)),
----------------------------------------------------------
-- Filter fishing to just the list of active fishing vessels in that year
----------------------------------------------------------
  fishing_filtered AS (
  SELECT
    *
  FROM
    fishing_mpas
  JOIN
    fishing_vessels
    -- Only keep positions for fishing vessels active that year
  USING
    (ssvid,
      year) 
 ),
  ----------------------------------------------------------
  -- Create fishing_hours attribute. Use night_loitering instead of nnet_score as indicator of fishing for squid jiggers
  ----------------------------------------------------------
  fishing_hours_filtered AS (
  SELECT
    * ,
      CASE
         WHEN best_vessel_class = 'squid_jigger'  AND night_loitering = 1 THEN hours
         WHEN best_vessel_class != 'squid_jigger' AND nnet_score >= 0.5    THEN hours
        ELSE
          NULL
      END
    AS fishing_hours
  FROM
    fishing_filtered ),
  ----------------------------------------------------------
  -- This subquery sums fishing hours and converts coordinates back to
  -- decimal degrees
  -- drop lon and lat but keep mpa
  ----------------------------------------------------------
  fishing_binned AS (
  SELECT
    date,
    #day,
    -- keep ssvid as we're interested in counting number of vessels
    ssvid,
    lat_bin , #/ 0.1 AS lat_bin,
    lon_bin , #/ 0.1 AS lon_bin,
    best_vessel_class,
    best_flag,
    zone_id,
    Designacion,
    nnet_score,
    SUM(hours) AS hours,
    SUM(fishing_hours) AS fishing_hours,
  FROM
    fishing_hours_filtered
  GROUP BY
    date,
    #day,
    ssvid,
    lat_bin,
    lon_bin,
    best_vessel_class,
    best_flag,
    nnet_score,
    zone_id,
    Designacion )
  ----------------------------------------------------------
  -- Return fishing data
  ----------------------------------------------------------
SELECT
*
FROM
  fishing_binned
WHERE
--#fishing_hours > 0 AND
  zone_id IS NOT NULL -- only within one of the zones.