package main

import (
	"database/sql"
	"fmt"

	_ "github.com/lib/pq"
	_ "github.com/mattn/go-sqlite3"
)

const (
	host     = "localhost"
	port     = 5432
	user     = "postgres"
	password = "test"
	dbname   = "testdb"
)

var liteDB *sql.DB
var psqlDB *sql.DB

func connectSQLite() (*sql.DB, error) {
	db, err := sql.Open("sqlite3", "/home/morten/Downloads/testdb.dbv")
	if err != nil {
		return nil, err
	}

	fmt.Println("Loaded S57 database")
	return db, nil
}

func connectPsql() (*sql.DB, error) {
	psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
		"password=%s dbname=%s sslmode=disable",
		host, port, user, password, dbname)

	db, err := sql.Open("postgres", psqlInfo)
	if err != nil {
		return nil, err
	}

	err = db.Ping()
	if err != nil {
		return nil, err
	}

	fmt.Println("Connected to PostGresQL")
	return db, nil
}

func getGeometryData() ([]string, error) {
	fmt.Println("fetching geometry data...")

	var ret []string

	rows, err := liteDB.Query("SELECT wkt_geometry FROM MX_CHART")
	if err != nil {
		panic(err)
	}

	var geom string

	for rows.Next() {
		err = rows.Scan(&geom)
		if err != nil {
			panic(err)
		}
		ret = append(ret, geom)
	}

	rows.Close()

	return ret, nil
}

func setupPsql() {
	sqlStatement := `
	CREATE TABLE IF NOT EXISTS wkt_geometry (
		id SERIAL PRIMARY KEY,
		geometry TEXT NOT NULL
	 );`
	_, err := psqlDB.Exec(sqlStatement)
	if err != nil {
		panic(err)
	}
}

func insertGeom(geom string) {
	sqlStatement := `
	INSERT INTO wkt_geometry(geometry) VALUES ($1)`
	_, err := psqlDB.Exec(sqlStatement, geom)
	if err != nil {
		panic(err)
	}
}

func truncate(db *sql.DB, tablename string) error {
	fmt.Println("Truncating table:", tablename)
	sqlStatement := fmt.Sprintf("TRUNCATE %s", tablename)
	_, err := db.Exec(sqlStatement)
	if err != nil {
		return err
	}

	return nil
}

func postGIS(geom string) {
	_, err := psqlDB.Exec("CREATE EXTENSION IF NOT EXISTS postgis")
	if err != nil {
		panic(err)
	}
	// SELECT POINT_LOCATION
	// FROM LOCATIONS_TABLE
	// WHERE ST_Contains(ST_GEOMFROMTEXT('POLYGON((P1.X P1.Y, P2.X P2.Y, ...))'), LOCATIONS_TABLE.POINT_LOCATION);
	sqlStatement := `
	SELECT geometry
	FROM wkt_geometry
	WHERE ST_Contains(ST_GEOMFROMTEXT('$1'), wkt_geometry.geometry);
	`
	rows, err := psqlDB.Query(sqlStatement, geom)
	if err != nil {
		panic(err)
	}

	var res string

	for rows.Next() {
		err = rows.Scan(&res)
		if err != nil {
			panic(err)
		}
		fmt.Println("POSTGIS: ", res)
	}

	rows.Close()

	// SELECT ST_Contains(polygon.geom, point.geom)
	// FROM public."LOCATIONS_TABLE" point, public."POLYGON" polygon
}

func main() {
	var err error

	liteDB, err = connectSQLite()
	if err != nil {
		panic(err)
	}

	psqlDB, err = connectPsql()
	if err != nil {
		panic(err)
	}

	defer liteDB.Close()
	defer psqlDB.Close()

	setupPsql()

	wktGeometry, err := getGeometryData()
	if err != nil {
		panic(err)
	}

	err = truncate(psqlDB, "wkt_geometry")
	if err != nil {
		panic(err)
	}

	for _, geom := range wktGeometry {
		insertGeom(geom)
	}
	fmt.Println("Inserted elements")
	fmt.Println(len(wktGeometry))

	postGIS(wktGeometry[0])
}