package main

import (
	"database/sql"
	"fmt"
	_ "github.com/denisenkom/go-mssqldb"
)

type SQLConnection struct {
	db *sql.DB
}

func (conn *SQLConnection) init() error {
	//query := url.Values{}
	//query.Add("app name", "MyAppName")
	//
	//u := &url.URL{
	//	Scheme: "sqlserver",
	//	User:   url.UserPassword("sa", "Datab@sed1337"),
	//	Host:   fmt.Sprintf("%s:%d", "", 1431),
	//	// Path:  instance, // if connecting to an instance instead of a port
	//	RawQuery: query.Encode(),
	//}
	//var err error
	//conn.db, err = sql.Open("sqlserver", u.String())
	//return err
	var err error
	conn.db, err = sql.Open("sqlserver", "server=localhost;user id=sa;password=Datab@sed1337;")
	return err
}

func (conn *SQLConnection) getTables() ([]string, error) {
	result, err := conn.db.Query(
		`SELECT TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME as TABLE_NAME
		FROM shipping.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'`)
	if err != nil {
		return nil, err
	}

	var tables []string
	for result.Next() {
		var row string
		_ = result.Scan(&row)
		tables = append(tables, row)
	}

	return tables, nil
}

func (conn *SQLConnection) getViews() ([]string, error) {
	result, err := conn.db.Query(
		`select name from sys.views where is_ms_shipped=0;`)
	if err != nil {
		return nil, err
	}

	var tables []string
	for result.Next() {
		var row string
		_ = result.Scan(&row)
		tables = append(tables, row)
	}

	return tables, nil
}

func (conn *SQLConnection) getColumns(table string) ([]string, error) {
	result, err := conn.db.Query(fmt.Sprintf("SELECT TOP 1 * FROM %s", table))
	if err != nil {
		return nil, nil
	}
	return result.Columns()
}

func (conn *SQLConnection) getTable(table string) ([][]string, error) {
	var err error
	var msg [][]string

	result, err := conn.db.Query(fmt.Sprintf("SELECT * FROM %s", table))
	if err != nil {
		return msg, err
	}

	cols, _ := result.Columns()
	for result.Next() {
		boxes := make([]string, len(cols))
		dest := make([]interface{}, len(cols))
		for i, _ := range dest {
			dest[i] = &boxes[i]
		}
		_ = result.Scan(dest...)
		msg = append(msg, boxes)
	}
	_ = result.Close()

	return msg, err
}

func (conn *SQLConnection) getOneToManyTable(manyTable string, manyColumn string, oneValue string) ([][]string, error) {
	result, err := conn.db.Query(fmt.Sprintf(
		"SELECT * FROM %s WHERE %s=%s", manyTable, manyColumn, oneValue))
	if err != nil {
		return nil, err
	}

	var msg [][]string
	cols, _ := result.Columns()
	for result.Next() {
		boxes := make([]string, len(cols))
		dest := make([]interface{}, len(cols))
		for i, _ := range dest {
			dest[i] = &boxes[i]
		}
		_ = result.Scan(dest...)
		msg = append(msg, boxes)
	}

	return msg, nil
}

func (conn *SQLConnection) deleteWhere(table string, constraint string) error {
	_, err := conn.db.Query(fmt.Sprintf("DELETE FROM %s WHERE %s", table, constraint))
	return err
}