package sql_job

import (
	"context"
	"errors"
	"fmt"
	"log"
	"strconv"
	"strings"
	"time"

	"github.com/spf13/viper"
	"gorm.io/gorm"

	"github.com/pingcap/crm-bot/model"
	"github.com/pingcap/crm-bot/pkg/config"
	"github.com/pingcap/crm-bot/pkg/db"
	"github.com/pingcap/crm-bot/pkg/job"
)

var ErrDatabaseNotConfig = errors.New("database not config")

type SQLJob struct {
	SourceTables  []string               `json:"SourceTables"`
	TargetTable   string                 `json:"TargetTable"`
	InitSQL       []string               `json:"InitSQL"`
	CalSQL        string                 `json:"CalSQL"`
	CalParams     map[string]interface{} `json:"CalParams"`
	QueryDatabase string                 `json:"QueryDatabase"`
	// if need overwrite table rows
	DontOverwrite bool `json:"DontOverwrite"`
}

// generate insert SQL
func (job *SQLJob) genInsertSQL(rows []map[string]interface{}) string {
	if len(rows) == 0 {
		return ""
	}

	keys := []string{}
	for k := range rows[0] {
		keys = append(keys, k)
	}

	ss := strings.Builder{}
	ss.WriteString(fmt.Sprintf("insert into %s ", job.TargetTable))
	for i, key := range keys {
		if i == 0 {
			ss.WriteByte('(')
		}
		ss.WriteString(fmt.Sprintf("`%s`", key))

		if i == len(keys)-1 {
			ss.WriteString(") values")
		} else {
			ss.WriteByte(',')
		}
	}

	for i, row := range rows {
		ss.WriteByte('(')
		for j, key := range keys {
			v := row[key]
			switch vv := v.(type) {
			case string:
				ss.WriteString(strconv.Quote(vv))
			case int64:
				ss.WriteString(strconv.FormatInt(vv, 10))
			case time.Time:
				ss.WriteString(strconv.Quote(vv.Format("2006-01-02 15:04:05")))
			case nil:
				ss.WriteString("0")
			default:
				log.Printf("[error] column type not support %T\n", vv)
				return ""
			}
			if j != len(keys)-1 {
				ss.WriteByte(',')
			}
		}
		ss.WriteByte(')')

		if i != len(rows)-1 {
			ss.WriteByte(',')
		}
	}

	return ss.String()
}

func (job *SQLJob) Run(gdb *gorm.DB) error {
	log.Println("Run ", job.TargetTable)
	dbCfgs := []model.DatabaseCfg{}
	_ = viper.UnmarshalKey("sql-job-databases", &dbCfgs)

	var err error
	// run init sql
	for _, sql := range job.InitSQL {
		err = gdb.Exec(sql).Error
		if err != nil {
			log.Println("[error] ", err)
			return err
		}
	}

	// todo cjl: flatten CalParams
	args := []interface{}{}
	rows := []map[string]interface{}{}
	var qdb *gorm.DB
	// check if the qurey database is the same of default-database
	// if not we need new the query database
	if job.QueryDatabase != config.GetString("dafault-database") {
		for _, cfg := range dbCfgs {
			if job.QueryDatabase == cfg.Database {
				qdb, err = db.NewDb(cfg.URL)
				if err != nil {
					log.Println("[error] ", err)
					return err
				}
				break
			}
		}

		if qdb == nil {
			log.Println("[error] QueryDatabase not config", job.QueryDatabase)
			return ErrDatabaseNotConfig
		} else {
			closeDB, _ := qdb.DB()
			defer closeDB.Close()
		}
	} else {
		qdb = gdb
	}

	// do query
	ctx, cancle := context.WithTimeout(context.Background(), 5*time.Minute)
	defer cancle()
	err = qdb.WithContext(ctx).Raw(job.CalSQL, args...).Scan(&rows).Error
	if err != nil {
		log.Println(err)
		return err
	}

	// truncate the table if need overwrite
	if !job.DontOverwrite {
		err = gdb.Exec(fmt.Sprintf("truncate table %s", job.TargetTable)).Error
		if err != nil {
			return err
		}
	}

	insertSQL := job.genInsertSQL(rows)
	log.Println("insert sql ", insertSQL)
	err = gdb.Exec(insertSQL).Error
	if err != nil {
		log.Println("[error] ", err)
		return err
	}

	return nil
}

type SQLJobArr struct {
	db   *gorm.DB `json:"-"`
	Cron string   `json:"Cron"`
	Jobs []SQLJob `json:"Jobs"`
}

func (arr SQLJobArr) Run() {
	gdb, err := db.NewDb(config.GetString("db"))
	if err != nil {
		log.Println("[error] create db ", err)
	}

	defer func() {
		innerDb, err := gdb.DB()
		if err != nil {
			log.Println("[error] gorm get DB() ", err)
			return
		}
		innerDb.Close()
	}()

	arr.db = gdb
	for _, job := range arr.Jobs {
		// run sequencial
		err := job.Run(arr.db)
		if err != nil {
			log.Println("[error] run sql job err", err)
			break
		}
	}
}

type SQLJobMap map[string]SQLJobArr

func Sched(m SQLJobMap) {
	for _, jobs := range m {
		_, _ = job.AddJob(jobs.Cron, jobs)
	}
}

// Run at once
func Run(m SQLJobMap) {
	for _, jobs := range m {
		jobs.Run()
	}
}