-- This is Free Software under GNU Affero General Public License v >= 3.0
-- without warranty, see README.md and license for details.

-- SPDX-License-Identifier: AGPL-3.0-or-later
-- License-Filename: LICENSES/AGPL-3.0.txt

-- Copyright (C) 2019 by via donau
--   – Österreichische Wasserstraßen-Gesellschaft mbH
-- Software engineering by Intevation GmbH

-- Author(s):
--  * Sascha Wilde <sascha.wilde@intevation.de>

--
-- This is is an dummy update which does not change the data base
-- schema but does fill in missing data.  The goal is to add missing
-- surtyp in sounding reults by extracting it from the import logs.
--

WITH loginfo AS (
    SELECT i.enqueued,
           (i.data::json -> 'date')::text::date AS date_info,
           array_agg(l.msg ORDER BY l.msg) AS msgs
      FROM import.imports i,
           import.import_logs l
      WHERE i.kind='sr' AND i.state IN ('accepted','pending')
        AND l.import_id = i.id
        AND ( l.msg LIKE 'Bottleneck: %' OR l.msg LIKE 'Processing as %' )
      GROUP BY i.enqueued, i.data
  ),
  typeinfo AS (
    SELECT DISTINCT ON (date_info, msgs[1]) enqueued, date_info,
           substring(msgs[1] from 13) AS bn_name,
           CASE WHEN msgs[2] = 'Processing as multi beam scan.'
                THEN 'multi' ELSE 'single' END AS surtype
      FROM loginfo
      ORDER BY date_info, msgs[1], enqueued DESC
  ),
  bnid_type AS (
    SELECT DISTINCT t.*, bn.bottleneck_id
      FROM typeinfo t, waterway.bottlenecks bn
      WHERE t.bn_name = bn.objnam
  )
UPDATE waterway.sounding_results AS sr
  SET surtyp = bt.surtype
  FROM bnid_type bt
  WHERE sr.bottleneck_id = bt.bottleneck_id
    AND sr.date_info = bt.date_info
    AND surtyp IS NULL ;