/*
 * Copyright (C) 2022 Associated Universities, Inc. Washington DC, USA.
 *
 * This file is part of NRAO Workspaces.
 *
 * Workspaces is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * Workspaces is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with Workspaces.  If not, see <https://www.gnu.org/licenses/>.
 */

/*
Package wrest looks up contact authors for a given project.

If this tool isn't working, the same information can be obtained by logging
into the OPT, opening the project in question, and clicking on the coauthors.
*/
package wrest

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	_ "github.com/lib/pq"
	"gitlab.nrao.edu/ssa/gocapo/capo/config"
	"strings"
)

// getCapoConfig creates a CapoConfig instance from a CAPO property file
func getCapoConfig(capoProfile string, capoPath string) config.CapoConfig {
	properties, err := config.InitConfig(capoProfile, capoPath)
	if err != nil {
		println("Unable to load CAPO properties")
		panic(err)
	}

	return properties
}

// RetrieveContacts for a given project code
func RetrieveContacts(projectCode string) []string {
	// Get the designated contact(s) for this project.

	// step 1: retrieve the contact author globalIDs from the OPT
	globalIds := retrieveGlobalIds(projectCode)

	// step 2: look up email addresses in the my.nrao.edu database
	return retrieveEmailsForGlobalIds(globalIds)
}

// retrieveGlobalIds for a given project code
func retrieveGlobalIds(projectCode string) []int {
	// connect to the database
	connection := GetArchiveDBConnection()

	// build the query
	query := `select globalid
				 from project
				 join projectauthor p on project.pi = p.id
				 where projectcode = $1 and receivesemail
				 union
				 select globalid
				 from project
				 join projectauthor p on project.contactauthor = p.id
				 where projectcode = $1 and receivesemail
				 union
				 select globalid
				 from project
				 join coauthors c on project.id = c.project_id
				 join projectauthor p on c.projectauthor_id = p.id
				 where projectcode = $1 and receivesemail`

	// close the database when we're done
	defer func(connection *sql.DB) {
		err := connection.Close()
		if err != nil {
			println("Unable to close the database connection!")
			panic(err)
		}
	}(connection)

	// run the query and get the result object
	rows, err := connection.Query(query, projectCode)
	if err != nil {
		println("Unable to execute the project code query!")
		panic(err)
	}

	// make a result slice
	globalIds := make([]int, 0)

	// clean up
	defer rows.Close()

	// walk through the list, scanning into a variable and appending to the result slice
	for rows.Next() {
		globalId := 0
		err := rows.Scan(&globalId)
		if err != nil {
			println("Unable to scan the global ID from the query!")
			panic(err)
		}
		globalIds = append(globalIds, globalId)
	}

	// done, return
	return globalIds
}

// retrieveEmailsForGlobalIds obtains the emails for the given slice of global IDs
func retrieveEmailsForGlobalIds(globalIds []int) []string {
	// Now that we have the global IDs, we can use them to look up contacts' email addresses
	connection := GetProposalsDBConnection()

	// build the IN list, this is so gross
	inClause := strings.Trim(strings.Join(strings.Fields(fmt.Sprint(globalIds)), ","), "[]")

	// build the query
	query := fmt.Sprintf(`select email
			  from email
			  where person_id IN (%s) and defaultEmail`, inClause)

	// close the database when we're done
	defer func(connection *sql.DB) {
		err := connection.Close()
		if err != nil {
			println("Unable to close the database connection!")
			panic(err)
		}
	}(connection)

	// run the query and get the result object
	rows, err := connection.Query(query)
	if err != nil {
		println("Unable to execute the project code query!")
		panic(err)
	}

	// make a result slice
	emails := make([]string, 0)

	// clean up
	defer rows.Close()

	// walk through the list, scanning into a variable and appending to the result slice
	for rows.Next() {
		var email string
		err := rows.Scan(&email)
		if err != nil {
			println("Unable to scan the global ID from the query!")
			panic(err)
		}
		emails = append(emails, email)
	}

	// done, return
	return emails
}