package main

import (
	"database/sql"
	"fmt"
	"strings"
)

import _ "github.com/bmizerany/pq"

// NOTE:  This application won't work because of a deadlock bug in Go's database/sql:
// https://code.google.com/p/go/issues/detail?id=3857

func main() {
	fmt.Println("Processing USGS data in the database...")

	if db, err := sql.Open("postgres", "user=postgres dbname=hauntings sslmode=disable"); err == nil {
		defer db.Close()

		if tx, err := db.Begin(); err == nil {

			// If we throw an error, we'll just panic and roll everything back
			defer func() {
				if err := recover(); err != nil {
					fmt.Println("Failed to migrate NOAA cities:", err)
					tx.Rollback()
				}
			}()

			checkCommunity, _ := tx.Prepare("select * from community_gnis where gnis = $1")
			createCommunity, _ := tx.Prepare(`
					insert into communities (name, longitude, latitude, center) 
					values ($1, $2, $3, st_setsrid(st_makepoint($4, $5), 4326)) 
					returning id`)
			createCommunityGnis, _ := tx.Prepare("insert into community_gnis values ($1, $2)")

			if cities, err := tx.Query("select name, state, id, lon, lat from noaa.cities where id is not null"); err == nil {

				var name, state, gnis string
				var longitude, latitude float32

				var communityId uint

				for cities.Next() {
					if err = cities.Scan(&name, &state, &gnis, &longitude, &latitude); err == nil {

						// Make sure we don't already have this city as a community
						if existing, err := checkCommunity.Query(gnis); err == nil {
							if !existing.Next() {

								// The name of the community is the name of the city, the full name of the state
								communityName := strings.Title(fmt.Sprintf("%s, %s", strings.ToLower(name), strings.ToLower(state)))
								fmt.Println("Creating", communityName)

								// Create the community, then associate it with a GNIS 
								community := createCommunity.QueryRow(communityName, longitude, latitude, longitude, latitude)
								if err = community.Scan(&communityId); err == nil {

									fmt.Println("Created community", communityId)

									if _, err = createCommunityGnis.Exec(communityId, gnis); err != nil {
										panic(fmt.Sprintf("Unable to link community %d to GNIS %d: %s", communityId, gnis, err.Error()))
									}
								} else {
									panic(fmt.Sprintf("Unable to create community \"%s\": %s", communityName, err.Error()))
								}
							}
						} else {
							panic(fmt.Sprintf("Failed to check for community %d: %s", gnis, err.Error()))
						}
					} else {
						fmt.Println("Failed:", err.Error())
					}
				}
			}

			if err = tx.Commit(); err != nil {
				fmt.Println("Failed to commit changes to the database:", err.Error())
			}

		} else {
			fmt.Println("Failed to start a transaction:", err.Error())
		}
	} else {
		fmt.Println("Unable to open a connection to the database:", err.Error())
	}
}