// 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) 2018, 2019 by via donau
//   – Österreichische Wasserstraßen-Gesellschaft mbH
// Software engineering by Intevation GmbH
//
// Author(s):
//  * Sascha Wilde <sascha.wilde@intevation.de>
//  * Sascha L. Teichmann <sascha.teichmann@intevation.de>
//  * Bernhard Reiter <bernhard.reiter@intevation.de>

package controllers

import (
	"database/sql"
	"net/http"

	"gemma.intevation.de/gemma/pkg/models"
	"github.com/gorilla/mux"
)

const (
	// Use the same logic as in schema/gemma.sql to find out
	// which is the reference gauge and which from its values is meant.
	// The LEFT for the JOIN is necessary because we still want to list
	// the surveys even if the corresponding value cannot be found because the
	// depth_reference and reference_water_level strings do not match.
	//
	// As this information could be vary from survey entry to survey,
	// in theory even for the same bottleneck
	// it seems to be the right place to return it from the /surveys endpoint.
	listSurveysSQL = `
SELECT
  s.bottleneck_id,
  s.date_info::text,
  s.depth_reference,
  bg.objname AS gauge_objname,
  r.value AS waterlevel_value
FROM
  (
	( SELECT * FROM waterway.bottlenecks AS b, waterway.gauges AS g
		WHERE b.fk_g_fid = g.location
	) AS bg
	JOIN waterway.sounding_results AS s
	ON bg.id = s.bottleneck_id
  )
LEFT JOIN waterway.gauges_reference_water_levels AS r
ON s.depth_reference = r.reference_water_level AND bg.location = r.gauge_id
WHERE bg.objnam=$1`
)

func listSurveys(
	_ interface{},
	req *http.Request,
	db *sql.Conn,
) (jr JSONResult, err error) {

	bottleneckName := mux.Vars(req)["bottleneck"]

	var rows *sql.Rows

	rows, err = db.QueryContext(req.Context(), listSurveysSQL, bottleneckName)
	if err != nil {
		return
	}
	defer rows.Close()

	surveys := []*models.Survey{}

	// as we do not use the values here, we could simply the code here
	// to work without an explicit mdels/surverys.go
	// (like done in controllers/search.go)
	for rows.Next() {
		var survey models.Survey
		var level sql.NullInt64
		if err = rows.Scan(
			&survey.BottleneckID,
			&survey.DateInfo,
			&survey.DepthReference,
			&survey.ReferenceGauge,
			&level,
		); err != nil {
			return
		}
		if level.Valid {
			survey.WaterLevelValue = &level.Int64
		}
		surveys = append(surveys, &survey)
	}

	if err = rows.Err(); err != nil {
		return
	}

	jr = JSONResult{
		Result: struct {
			Surveys []*models.Survey `json:"surveys"`
		}{surveys},
	}
	return
}