package sql

import (
	"database/sql"
	"errors"
	"strings"

	// _ "github.com/go-sql-driver/mysql"
	_ "github.com/go-sql-driver/mysql"
)

//SQL ...
type SQL struct {
	db  *sql.DB
	err error
}

// NewSQL ...
func NewSQL(server, login, mdp, base string) *SQL {
	db, err := Connect(server, login, mdp, base)
	if err != nil {
		panic(err.Error())
	}
	s := new(SQL)
	s.db = db
	s.err = err
	return s
}

func Connect(server, login, mdp, base string) (*sql.DB, error) {
	db, err := sql.Open("mysql", login+":"+mdp+"@tcp("+server+")/"+base)
	return db, err
}

/* INSERT
return nombre d'insert
*/
func (s *SQL) Insert(table string, cols []string, values [][]string) error {
	if cols != nil {
		for _, v := range values {
			if len(v) != len(cols) {
				return errors.New("") // Il n'y a pas le meme nombre de cols dans une ou plusieurs ligne de values
			}
		}
	}
	flgA := 0
	flgB := 0
	lines := ""
	for _, value := range values {
		if flgB != 0 {
			lines += ", "
		}
		lines += "("
		for i := 0; i < len(value); i++ {
			if flgA != 0 {
				lines += ", "
			}
			if value[i] != "" {
				lines += "'" + value[i] + "'"
			} else {
				lines += "null"
			}
			flgA = 1
		}
		lines += ")"
		flgB = 1
		flgA = 0
	}
	sql := "INSERT INTO " + table + " VALUES " + lines + ";"
	insert, err := s.db.Query(sql)
	if err != nil || insert.Err() != nil {
		return errors.New("error syntax : " + sql) // Error syntax sql
	}
	defer insert.Close()
	return nil
}

func (s *SQL) DeleteAND(table string, wheres [][]string) error {
	if wheres == nil {
		return errors.New("") // Aucune condition
	}
	lines := ""
	flag := 0
	for _, where := range wheres {
		if flag != 0 {
			lines += " AND "
		}
		lines += where[0] + " = '" + where[1] + "'"
		flag = 1
	}
	sql := "DELETE FROM " + table + " WHERE " + lines + ";"
	delete, err := s.db.Query(sql)
	if err != nil || delete.Err() != nil {
		return errors.New("error syntax : " + sql) // Error syntax
	}
	defer delete.Close()
	return nil
}

func (s *SQL) UpdateAND(table string, sets [][]string, wheres [][]string) error {
	if wheres == nil {
		return errors.New("") // Aucune condition
	}
	conds := ""
	flag := 0
	for _, where := range wheres {
		if flag != 0 {
			conds += " AND "
		}
		conds += where[0] + " = '" + where[1] + "'"
		flag = 1
	}
	vls := ""
	flagA := 0
	for _, set := range sets {
		if flagA != 0 {
			vls += ", "
		}
		vls += set[0] + " = '" + set[1] + "'"
		flagA = 1
	}
	sql := "UPDATE " + table + " SET " + vls + " WHERE " + conds + ";"
	update, err := s.db.Query(sql)
	if err != nil || update.Err() != nil {
		return errors.New("error syntax : " + sql) // Error syntax
	}
	defer update.Close()
	return nil
}

func (s *SQL) Select(query string) error {
	if (query == "") || (!strings.Contains(query, "SELECT")) ||
		(strings.Count(query, ";") != 1 && strings.Index(query, ";")+1 != len(query)) {
		return errors.New("") // query format incorrect
	}
	slct, err := s.db.Query(query)
	if err != nil || slct.Err() != nil {
		return errors.New("") // retour select incorrect
	}
	//
	return nil
}

func (s *SQL) Close() {

}