// RecoverSQLLogError re-executes failed sql queries in sql error log file from specified path.
// It returns the number of failed -reexec queries, and new failed
// queries will be written to the file
//
// The format of error file is:
// 	Error description - $$$error$$$SQL_QUERY$$$error$$$
// Therefore only get statements enclosed by special `$$$error$$$`
func RecoverSQLLogError(sqlErrFilePath dna.String, db *sqlpg.DB) dna.Int {
	var errCount = 0
	var errStrings = dna.StringArray{}
	b, err := ioutil.ReadFile(sqlErrFilePath.String())
	if err != nil {
		panic(err)
	}
	data := dna.String(string(b))
	// dna.Log("\n", data.Length())
	sqlArr := data.FindAllString(`(?mis)\$\$\$error\$\$\$.+?\$\$\$error\$\$\$`, -1)
	// dna.Log("\nTOTAL SQL STATEMENTS FOUND:", sqlArr.Length())
	for _, val := range sqlArr {
		sqlStmtArr := val.FindAllStringSubmatch(`(?mis)\$\$\$error\$\$\$(.+?)\$\$\$error\$\$\$`, -1)
		if len(sqlStmtArr) > 0 {
			_, err := db.Exec(sqlStmtArr[0][1].String())
			if err != nil {
				if dna.String(err.Error()).Contains(`duplicate key value violates unique constraint`) == false {
					errCount += 1
					errStrings.Push("$$$error$$$" + sqlStmtArr[0][1] + "$$$error$$$")
				}
			}
		}
	}
	if errCount == 0 {
		err = ioutil.WriteFile(sqlErrFilePath.String(), []byte{}, 0644)
	} else {
		err = ioutil.WriteFile(sqlErrFilePath.String(), []byte(errStrings.Join("\n").String()), 0644)
	}
	if err != nil {
		panic(err)
	}
	return dna.Int(errCount)
}

func DumpFiles() {
	var ret = dna.StringArray{}
	for _, table := range GetAlbumTables().Concat(GetSongTables()).Concat(GetVideoTables()) {
		ret.Push(`"` + table + `"`)
	}
	dna.Log(ret.Join(","))
}

// This function will decode a cipher string into id.
func Decrypt(cipher dna.String) dna.Int {
	arr := dna.StringArray{cipher[0:2], cipher[2:3], cipher[3:4], cipher[4:6], cipher[6:7], cipher[7:8], cipher[8:10]}.Filter(
		func(value dna.String, index dna.Int) dna.Bool {
			return value != ""
		})
	return dna.IntArray(arr.Map(func(v dna.String, i dna.Int) dna.Int {
		return ns[6-i].IndexOf(v)
	}).([]dna.Int)).Join("").ToInt()
}

func GetArtistTables() dna.StringArray {
	var tblArr = dna.StringArray{}
	for key, _ := range RefTables {
		if RefTables.HasArtist(key) {
			tblArr.Push(key + "artists")
		}
	}
	return tblArr
}

func GetSongTables() dna.StringArray {
	var tblArr = dna.StringArray{}
	for key, _ := range RefTables {
		if RefTables.HasSong(key) {
			tblArr.Push(key + "songs")
		}
	}
	return tblArr
}

func GetVideoTables() dna.StringArray {
	var tblArr = dna.StringArray{}
	for key, _ := range RefTables {
		if RefTables.HasVideo(key) {
			tblArr.Push(key + "videos")
		}
	}
	return tblArr
}

func GetAlbumTables() dna.StringArray {
	var tblArr = dna.StringArray{}
	for key, _ := range RefTables {
		if RefTables.HasAlbum(key) {
			tblArr.Push(key + "albums")
		}
	}
	return tblArr
}

func (apiSong *APIFullSong) Save(db *sqlpg.DB) error {

	var queries = dna.StringArray{}
	var err error

	// Getting artist queries
	artists := apiSong.ToArtists()
	for _, artist := range artists {
		queries.Push(sqlpg.GetInsertIgnoreStatement(sqlpg.GetTableName(artist), artist, "id", artist.Id, false))
	}

	// Getting album query
	album := apiSong.ToAlbum()
	queries.Push(sqlpg.GetInsertIgnoreStatement(sqlpg.GetTableName(album), album, "id", album.Id, false))

	// Getting song query
	song := apiSong.ToSong()
	queries.Push(sqlpg.GetInsertStatement(sqlpg.GetTableName(song), song, false))

	for _, query := range queries {
		_, err = db.Exec(query.String())
	}

	if err != nil {
		errQueries := dna.StringArray(queries.Map(func(val dna.String, idx dna.Int) dna.String {
			return "$$$error$$$" + val + "$$$error$$$"
		}).([]dna.String))
		return errors.New(err.Error() + errQueries.Join("\n").String())
	} else {
		return nil
	}

}

// splitAndTruncateArtists splits stringarray by the key "feat:"
// and filter only string elements not equal to empty string.
func splitAndTruncateArtists(artists dna.StringArray) dna.StringArray {
	return dna.StringArray(artists.SplitWithRegexp("feat:").Map(func(val dna.String, idx dna.Int) dna.String {
		return val.Trim()
	}).([]dna.String)).Filter(func(val dna.String, idx dna.Int) dna.Bool {
		if val != "" {
			return true
		} else {
			return false
		}
	})
}

// GetFormats returns proper formatStr for a song or a video.
// If it is a song, IsSong will be set to true. Otherwise, it will set to false false.
func GetFormats(urls dna.StringArray) (formatStr dna.String, IsSong dna.Bool) {
	switch getType(urls.Join("")) {
	case "mp3", "m4a", "flac":
		formatStr, IsSong = getStringifiedSongUrls(urls), IS_SONG
		return formatStr, IsSong
	case "mp4", "flv":
		formatStr, IsSong = getStringifiedVideoUrls(urls), IS_VIDEO
		return formatStr, IsSong
	default:
		panic("Wrong type. Cannot indentify song or video")
	}
	return "", false
}

func (q Question) ToRecord() []string {

	if q.Cat.Subject == "non-verbal-reasoning/questions-and-answers" {
		imagesArr := q.QuestionContent.FindAllString(`<img.+?src=.+?>`, -1)
		images := dna.StringArray{}
		for _, image := range imagesArr {
			images.Push("http://indiabix.com" + image.GetTagAttributes("src"))
		}
		return []string{"Non Verbal Reasoning", q.Cat.Name.String(), q.Description.String(), images.Join(",").String(), q.QuestionContent.String(), q.OptionA.String(), q.OptionB.String(), q.OptionC.String(), q.OptionD.String(), q.OptionE.String(), q.Answer.String(), q.Explaination.String(), q.ExerciseNo.ToString().String()}
	} else {
		return []string{q.Cat.Subject.String(), q.Cat.Name.String(), q.Description.String(), q.QuestionContent.String(), q.OptionA.String(), q.OptionB.String(), q.OptionC.String(), q.OptionD.String(), q.OptionE.String(), q.Answer.String(), q.Explaination.String(), q.ExerciseNo.ToString().String()}
	}
}

// GetUpdateStatement returns an update statement from specified snake-case columns.
// If columns's names are not found, it will return an error.
// It updates some fields from a struct.
//
// 	* tbName : A name of update table.
// 	* structValue : A struct-typed value being scanned. Its fields have to be dna basic type or time.Time.
// 	* conditionColumn : A snake-case column name in the condition, usually it's an id
// 	* columns : A list of args of column names in the table being updated.
// 	* Returns an update statement.
func GetUpdateStatement(tbName dna.String, structValue interface{}, conditionColumn dna.String, columns ...dna.String) (dna.String, error) {
	if reflect.TypeOf(structValue).Kind() != reflect.Ptr {
		panic("StructValue has to be pointer")
		if reflect.TypeOf(structValue).Elem().Kind() != reflect.Struct {
			panic("StructValue has to be struct type")
		}
	}
	query := "UPDATE " + tbName + " SET\n"
	result := dna.StringArray{}
	for _, column := range columns {
		result.Push(getPairValue(structValue, column))
	}
	conditionRet := "\nWHERE " + getPairValue(structValue, conditionColumn) + ";"
	return query + result.Join(",\n") + conditionRet, nil
}

func convertCategoryToStringArray(catStr dna.String) dna.StringArray {
	var cats []Category
	var ret = dna.StringArray{}
	err := json.Unmarshal([]byte(string(catStr)), &cats)
	if err != nil {
		dna.Log(catStr)
		dna.Log(err.Error())
		panic("Invalid category string input")
	} else {
		for _, cat := range cats {
			ret.Push(cat.Name)
		}
	}
	return ret
}

func (sf *APISongFreaksTrack) ToSong() (*Song, error) {
	if sferr := sf.HasError(); sferr != nil {
		return nil, sferr
	}
	song := NewSong()
	song.Id = sf.Track.Id
	song.TrackGroupId = sf.Track.TrackGroupId
	song.AMG = sf.Track.AMG
	song.UrlSlug = sf.Track.UrlSlug
	song.IsInstrumental = sf.Track.IsInstrumental
	song.Viewable = sf.Track.Viewable
	song.Duration = utils.ToSeconds(sf.Track.Duration)
	song.Lyricid = sf.Track.LyricId
	song.HasLrc = sf.Track.HasLrc
	song.TrackNumber = sf.Track.TrackNumber
	song.DiscNumber = sf.Track.DiscNumber
	song.Title = sf.Track.Title
	song.Rating = dna.IntArray{sf.Track.Rating.AverageRating.ToInt(), sf.Track.Rating.UserRating, sf.Track.Rating.TotalRatings}
	song.Albumid = sf.Track.Album.Id

	artistIds := dna.IntArray{}
	artists := dna.StringArray{}
	for _, artist := range sf.Track.Artists {
		artistIds.Push(artist.Id)
		artists.Push(artist.Name)
	}
	song.Artistids = artistIds
	song.Artists = artists

	if sf.Track.Lrc.Lines != nil && len(sf.Track.Lrc.Lines) > 0 {
		lines, err := json.Marshal(sf.Track.Lrc.Lines)
		if err == nil {
			song.Lrc = dna.String(string(lines))

		}
	}

	song.Link = sf.Track.Link
	song.Lyric = sf.Track.Lyrics
	if song.Lyric != "" {
		song.HasLyric = true
	}
	song.Copyright = sf.Track.Copyright
	song.Writer = sf.Track.Writer
	song.SubmittedLyric = sf.Track.SubmittedLyric
	song.Checktime = time.Now()
	return song, nil
}

func getTSGM(data *dna.String, kind dna.String) dna.String {
	var itemArr dna.StringArray
	switch kind {
	case "genres":
		itemArr = data.FindAllString(`(?mis)<h4>Genre</h4>(.+?)</div>`, 1)
	case "styles":
		itemArr = data.FindAllString(`(?mis)<h4>Styles</h4>(.+?)</div>`, 1)
	case "moods":
		itemArr = data.FindAllString(`(?mis)<h4>Album Moods</h4>(.+?)</div>`, 1)
	case "themes":
		itemArr = data.FindAllString(`(?mis)<h4>Themes</h4>(.+?)</div>`, 1)
	default:
		panic("Wrong kind!!!")
	}
	if itemArr.Length() > 0 {
		catArr := itemArr[0].FindAllString(`<a href=.+?</a>`, -1)
		categories := catArr.Map(func(val dna.String, idx dna.Int) Category {
			var idArr []dna.StringArray
			var id dna.Int = 0
			name := val.RemoveHtmlTags("")
			if kind == "moods" {
				idArr = val.FindAllStringSubmatch(`xa([0-9]+)`, 1)
			} else {
				idArr = val.FindAllStringSubmatch(`ma([0-9]+)`, 1)
			}
			if len(idArr) > 0 {
				id = idArr[0][1].ToInt()
			}
			return Category{id, name}
		}).([]Category)

		if len(categories) > 0 {
			bCat, merr := json.Marshal(categories)
			if merr != nil {
				return "[]"
			} else {
				return dna.String(string(bCat))
			}
		} else {
			return "[]"
		}
	} else {
		return "[]"
	}

}

// ExecQueriesInTransaction executes queries in a transaction.
// If one statement fails, the whole queries cannot commit.
//
// The returned error is nil if there is no error.
// If an error occurs, each statement will be enclosed in
// format $$$error$$$.
// 	$$$error$$$ Your Custom Query $$$error$$$
//
// This function is seen in songfreaks and allmusic sites.
func ExecQueriesInTransaction(db *DB, queries *dna.StringArray) error {
	var err error
	globalSqlTransactoNo += 1
	// tx, err := db.Begin()
	// if err != nil {
	// 	dna.Log("Transaction No:" + dna.Sprintf("%v", globalSqlTransactoNo).String() + err.Error() + " Could not create transaction\n")
	// }

	for idx, query := range *queries {
		_, err = db.Exec(query.String())
		// _, err = tx.Exec(query.String())
		if err != nil {
			dna.Log(dna.Sprintf("DNAError: Query series No: %v - %v - %v - %v\n", dna.Sprintf("%v", globalSqlTransactoNo), idx, err.Error(), "Could not execute the statement"))
		}
		// stmt, err := tx.Prepare(query.String())
		// if err != nil {
		// 	dna.Log(dna.Sprintf("DNAError Transaction No: %v - %v - %v - %v \n", dna.Sprintf("%v", globalSqlTransactoNo), idx, err.Error(), "Could not prepare"))
		// } else {
		// 	_, err = stmt.Exec()
		// 	if err != nil {
		// 		dna.Log(dna.Sprintf("DNAError: Transaction No: %v - %v - %v - %v\n", dna.Sprintf("%v", globalSqlTransactoNo), idx, err.Error(), "Could not execute the prepared statement"))
		// 	}

		// 	err = stmt.Close()
		// 	if err != nil {
		// 		dna.Log("Transaction No:" + dna.Sprintf("%v", globalSqlTransactoNo).String() + err.Error() + " Could not close\n")
		// 	}
		// }
	}
	// err = tx.Commit()
	// if err != nil {
	// 	dna.Log("Transaction No:" + dna.Sprintf("%v", globalSqlTransactoNo).String() + err.Error() + " Could not commit transaction\n")
	// }

	if err != nil {
		errQueries := dna.StringArray(queries.Map(func(val dna.String, idx dna.Int) dna.String {
			return "Transaction No:" + dna.Sprintf("%v", globalSqlTransactoNo) + " $$$error$$$" + val + "$$$error$$$"
		}).([]dna.String))
		return errors.New(err.Error() + errQueries.Join("\n").String())
	} else {
		return nil
	}
}

func getGenresFromDesc(desc dna.String) dna.StringArray {
	var ret dna.StringArray
	genres := desc.FindAllString(`(?i)genres?(\s+)?:?.+`, 1)
	// "Released:" found in album id: 836258
	if genres.Length() > 0 {
		ret = dna.StringArray(genres[0].ReplaceWithRegexp(`(?mis)genres?(\s+)?:?`, "").ReplaceWithRegexp(`\.?\s*Released:.+`, "").Trim().Split(",").Map(func(val dna.String, idx dna.Int) dna.String {
			return val.ReplaceWithRegexp(":", "").Trim()
		}).([]dna.String))
		if ret.Length() == 1 {
			arr := dna.StringArray{}
			if ret[0].FindAllString(`(?mis)K-Pop`, 1).Length() > 0 {
				arr.Push("Korean Pop")
				arr.Push(ret[0].ReplaceWithRegexp(`(?mis)\(?K-Pop\)?`, "").Trim())
				ret = arr
			}
		}
	}
	return ret.SplitWithRegexp(` > `).SplitWithRegexp(`/`)
}

func (apiaward *APIAward) ToAward() *Award {
	award := NewAward()
	award.Id = apiaward.Id
	award.Title = apiaward.Title
	award.Section = ""
	award.Year = apiaward.Year
	award.Chart = apiaward.Chart
	award.Peak = apiaward.Peak
	award.Type = apiaward.Type
	award.Prize = apiaward.Award
	winnerids := dna.IntArray{}
	winners := dna.StringArray{}
	for _, winner := range apiaward.Winners {
		winnerids.Push(winner.Id)
		winners.Push(winner.Name)
	}
	award.Winnerids = winnerids
	award.Winners = winners
	return award
}

// SelectMissingKeys accepts a table name as an input and a list of keys as a source.
// It returns a new list of keys that does not exist in the destination table
//
// 	* tblName : a table name
// 	* srcKeys : a source keys
// 	* db : a pointer to connected databased
// 	* Returns a new list of keys which are not from the specified table
//
// Notice: Only applied to a table having a column named "key".
// The column has to be indexed to ensure good performance
//
// The format of sql statement is:
//	with dna (key) as (values ('43f3HhhU6DGV'),('uFfgQhKbwAfN'),('RvFDlckJB5QU'),('uIF7rwd5wo4p'),('Kveukbhre1ry'),('oJ1lzAlKwJX6'),('43f3HhhU6DGV'),('uFfgQhKbwAfN'),('hfhtyMdywMau'),('PpZuccjYqy1b'))
//	select key from dna where key not in
//	(select key from nctalbums where key in ('43f3HhhU6DGV','uFfgQhKbwAfN','RvFDlckJB5QU','uIF7rwd5wo4p','Kveukbhre1ry','oJ1lzAlKwJX6','43f3HhhU6DGV','uFfgQhKbwAfN','hfhtyMdywMau','PpZuccjYqy1b'))
func SelectMissingKeys(tblName dna.String, srcKeys *dna.StringArray, db *sqlpg.DB) (*dna.StringArray, error) {
	if srcKeys.Length() > 0 {
		val := dna.StringArray(srcKeys.Map(func(val dna.String, idx dna.Int) dna.String {
			return `('` + val + `')`
		}).([]dna.String))
		val1 := dna.StringArray(srcKeys.Map(func(val dna.String, idx dna.Int) dna.String {
			return `'` + val + `'`
		}).([]dna.String))
		selectStmt := "with dna (key) as (values " + val.Join(",") + ") \n"
		selectStmt += "select key from dna where key not in \n(select key from " + tblName + " where key in (" + val1.Join(",") + "))"
		keys := &[]dna.String{}
		err := db.Select(keys, selectStmt)
		switch {
		case err != nil:
			return nil, err
		case err == nil && keys != nil:
			slice := dna.StringArray(*keys)
			return &slice, nil
		case err == nil && keys == nil:
			return &dna.StringArray{}, nil
		default:
			panic("Default case triggered. Case is not expected. Cannot select non existed keys")
		}
	} else {
		return nil, errors.New("Empty input array")
	}
}

func (apisong *APISong) ToSong() *Song {
	song := NewSong()
	song.Id = apisong.Id
	song.Title = apisong.Title.DecodeHTML()
	song.Duration = apisong.Duration

	artistids := dna.IntArray{}
	artists := dna.StringArray{}
	for _, artist := range apisong.Artists {
		artistids.Push(artist.Id)
		artists.Push(artist.Name)
	}
	song.Artistids = artistids
	song.Artists = artists

	composerids := dna.IntArray{}
	composers := dna.StringArray{}
	for _, composer := range apisong.Composers {
		composerids.Push(composer.Id)
		composers.Push(composer.Name)
	}
	song.Composerids = composerids
	song.Composers = composers
	song.Checktime = time.Now()

	return song
}