package utils

import (
	"database/sql"
	"errors"
	"sync"

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

type SqliteUniData struct {
	ServerlessClusterID string
	Query               string  // statements: normalized_sql, slowlog: query
	Count               int     // statements: exec_count,     slowlog: count
	Value               float64 // statements: ru,             slowlog: query_time_p99
}

type SqlMeta struct {
	SqlTemplate string
	FilePath    string
	DB          *sql.DB
}

const (
	STATEMENT_PREFIX = "statements_"
	SLOWLOG_PREFIX   = "slow-stats_"
)

var (
	SqliteQuerysLock sync.RWMutex
	SqliteQuerys     = make(map[string]*SqlMeta)
)

func InitSQLiteDB(region, statementDBPath, slowlogDBPath string) (err error) {
	SqliteQuerysLock.Lock()
	defer SqliteQuerysLock.Unlock()
	k, ok := SqliteQuerys[STATEMENT_PREFIX+region]
	if !ok || k.DB == nil || k.FilePath != statementDBPath {
		SqliteQuerys[STATEMENT_PREFIX+region] = &SqlMeta{
			SqlTemplate: "SELECT serverless_cluster_id, SUBSTR(normalized_sql, 1, 500) || case when length(normalized_sql) > 500 then ' [...more...]' else '' end, sum(exec_count), sum(ru) " +
				"FROM statements WHERE is_internal=0 and ru>exec_count and normalized_sql not like 'commit%%' and serverless_cluster_id='%s' " +
				"GROUP BY digest ORDER BY ru DESC LIMIT 5;",
			FilePath: statementDBPath,
		}
		SqliteQuerys[STATEMENT_PREFIX+region].DB, err = sql.Open("sqlite3", statementDBPath)
		if err != nil {
			delete(SqliteQuerys, STATEMENT_PREFIX+region)
			return err
		}
	}

	k, ok = SqliteQuerys[SLOWLOG_PREFIX+region]
	if !ok || k.DB == nil || k.FilePath != slowlogDBPath {
		SqliteQuerys[SLOWLOG_PREFIX+region] = &SqlMeta{
			/* DML exclude with system table */
			/* TODO: ignore TiFlash/TiDB-Worker querys */
			SqlTemplate: "SELECT serverless_cluster_id, SUBSTR(query, 1, 500) || case when length(query) > 500 then ' [...more...]' else '' end, count, ROUND(query_time_p99, 3) " +
				"FROM slow_stats WHERE is_internal=0 and (is_tiflash is null or is_tiflash=0) and serverless_cluster_id='%s' and " +
				"(query like 'select %%' or query like 'delete %%' or query like 'update %%' or query like 'insert %%' or query like 'with %%') and " +
				"(query not like '%%`mysql`%%' and query not like '%%`INFORMATION_SCHEMA`%%' and query not like '%%`PERFORMANCE_SCHEMA`%%') " +
				"ORDER BY query_time_p99 DESC LIMIT 5;",
			FilePath: slowlogDBPath,
		}

		SqliteQuerys[SLOWLOG_PREFIX+region].DB, err = sql.Open("sqlite3", slowlogDBPath)
		if err != nil {
			_ = SqliteQuerys[STATEMENT_PREFIX+region].DB.Close()
			delete(SqliteQuerys, STATEMENT_PREFIX+region)
			delete(SqliteQuerys, SLOWLOG_PREFIX+region)
			return err
		}
	}
	return nil
}

func CloseSQLiteDB(region string) {
	SqliteQuerysLock.Lock()
	defer SqliteQuerysLock.Unlock()
	k, ok := SqliteQuerys[STATEMENT_PREFIX+region]
	if ok && k.DB != nil {
		k.DB.Close()
	}
	k, ok = SqliteQuerys[SLOWLOG_PREFIX+region]
	if ok && k.DB != nil {
		k.DB.Close()
	}
}

func DoSqliteDBQuery(db *sql.DB, query string) (results []SqliteUniData, _ error) {
	if db == nil {
		return nil, errors.New("invalid db addr")
	}
	rows, err := db.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		var data SqliteUniData
		if err := rows.Scan(
			&data.ServerlessClusterID,
			&data.Query,
			&data.Count,
			&data.Value,
		); err != nil {
			return nil, err
		}
		results = append(results, data)
	}

	return results, rows.Err()
}