// This is Free Software under GNU Affero General Public License v >= 3.0
// without warranty, see README.md and license for details.
//
// SPDX-License-Identifier: AGPL-3.0-or-later
// License-Filename: LICENSES/AGPL-3.0.txt
//
// Copyright (C) 2021 by via donau
//   – Österreichische Wasserstraßen-Gesellschaft mbH
// Software engineering by Intevation GmbH
//
// Author(s):
//  * Sascha L. Teichmann <sascha.teichmann@intevation.de>

package xlsx

import (
	"bufio"
	"context"
	"database/sql"
	"errors"
	"fmt"
	"io"
	"os"
	"strconv"
	"strings"

	"github.com/xuri/excelize/v2"
	"gopkg.in/yaml.v2"

	"github.com/PaesslerAG/gval"

	"gemma.intevation.de/gemma/pkg/log"
)

type Action struct {
	Type        string    `yaml:"type"`
	Actions     []*Action `yaml:"actions"`
	Location    []string  `yaml:"location"`
	Source      string    `yaml:"source"`
	Destination string    `yaml:"destination"`
	Statement   string    `yaml:"statement"`
	Vars        []string  `yaml:"vars"`
	Name        string    `yaml:"name"`
	Expr        string    `yaml:"expr"`
}

type frame struct {
	res   *sqlResult
	index int
}

type sheetAxis struct {
	sheet string
	axis  string
}

type cellValue struct {
	value string
	err   error
}

type executor struct {
	ctx              context.Context
	tx               *sql.Tx
	template         *excelize.File
	keep             map[string]bool
	expressions      map[string]gval.Evaluable
	sourceSheet      string
	destinationSheet string
	frames           []frame
	// Fetching formulas out of cells is very expensive so we cache them.
	formulaCache map[sheetAxis]cellValue
}

type area struct {
	x1 int
	y1 int
	x2 int
	y2 int
	mc excelize.MergeCell
}

func mergeCellToArea(mc excelize.MergeCell) (area, error) {
	x1, y1, err := excelize.CellNameToCoordinates(mc.GetStartAxis())
	if err != nil {
		return area{}, err
	}
	x2, y2, err := excelize.CellNameToCoordinates(mc.GetEndAxis())
	if err != nil {
		return area{}, err
	}
	return area{
		x1: x1,
		y1: y1,
		x2: x2,
		y2: y2,
		mc: mc,
	}, nil
}

func (a *area) contains(x, y int) bool {
	return a.x1 <= x && x <= a.x2 && a.y1 <= y && y <= a.y2
}

func ActionFromFile(filename string) (*Action, error) {
	f, err := os.Open(filename)
	if err != nil {
		return nil, err
	}
	defer f.Close()
	return ActionFromReader(f)
}

func ActionFromReader(r io.Reader) (*Action, error) {
	action := new(Action)
	err := yaml.NewDecoder(bufio.NewReader(r)).Decode(action)
	return action, err
}

func (a *Action) Execute(
	ctx context.Context,
	tx *sql.Tx,
	template *excelize.File,
) error {

	//if f, err := os.Create("cpu.prof"); err == nil {
	//	pprof.StartCPUProfile(f)
	//	defer pprof.StopCPUProfile()
	//}

	e := executor{
		ctx:          ctx,
		tx:           tx,
		template:     template,
		keep:         map[string]bool{},
		expressions:  map[string]gval.Evaluable{},
		formulaCache: map[sheetAxis]cellValue{},
	}

	oldSheets := template.GetSheetList()

	if err := e.dispatch(a); err != nil {
		return err
	}

	for _, sheet := range oldSheets {
		if !e.keep[sheet] {
			template.DeleteSheet(sheet)
		}
	}
	return nil
}

var reused int

func (e *executor) getCellFormula(sheet, axis string) (string, error) {
	var (
		k  = sheetAxis{sheet: sheet, axis: axis}
		v  cellValue
		ok bool
	)
	if v, ok = e.formulaCache[k]; !ok {
		v.value, v.err = e.template.GetCellFormula(sheet, axis)
		e.formulaCache[k] = v
	}
	return v.value, v.err
}

func (e *executor) setCellFormula(sheet, axis, formula string) {
	e.formulaCache[sheetAxis{sheet: sheet, axis: axis}] = cellValue{value: formula}
	e.template.SetCellFormula(sheet, axis, formula)
}

func (e *executor) dispatch(action *Action) error {
	if len(action.Vars) > 0 {
		e.pushVars(action.Vars)
		defer e.popFrame()
	}
	switch action.Type {
	case "sheet":
		return e.sheet(action)
	case "copy":
		return e.copy(action)
	case "select":
		return e.sel(action)
	case "assign":
		return e.assign(action)
	case "":
		return e.actions(action)
	}
	return fmt.Errorf("unknown type '%s'", action.Type)
}

func (e *executor) pushVars(vars []string) {
	e.frames = append(e.frames, frame{
		res: &sqlResult{
			columns: vars,
			rows:    [][]interface{}{make([]interface{}, len(vars))},
		},
	})
}

func (e *executor) popFrame() {
	n := len(e.frames)
	e.frames[n-1].res = nil
	e.frames = e.frames[:n-1]
}

func (e *executor) assign(action *Action) error {
	if action.Name == "" {
		return errors.New("missing name in assign")
	}
	if action.Expr == "" {
		return errors.New("missing expr in assign")
	}

	for i := len(e.frames) - 1; i >= 0; i-- {
		fr := &e.frames[i]
		if idx := fr.res.find(action.Name); idx >= 0 {
			f, err := e.expr(action.Expr)
			if err != nil {
				return err
			}
			value, err := f(e.ctx, e.vars())
			if err != nil {
				return err
			}
			fr.res.rows[fr.index][idx] = value
			break
		}
	}
	return e.actions(action)
}

func order(a, b int) (int, int) {
	if a < b {
		return a, b
	}
	return b, a
}

func (e *executor) copy(action *Action) error {
	if n := len(action.Location); !(n == 1 || n == 2) {
		return fmt.Errorf("length location = %d (expect 1 or 2)",
			len(action.Location))
	}

	vars := e.vars()

	var err error
	expand := func(s string) string {
		if err == nil {
			s, err = e.expand(s, vars)
		}
		return s
	}
	split := func(s string) (int, int) {
		var x, y int
		if err == nil {
			x, y, err = excelize.CellNameToCoordinates(s)
		}
		return x, y
	}

	var location []string
	if len(action.Location) == 1 {
		location = []string{action.Location[0], action.Location[0]}
	} else {
		location = action.Location
	}

	var destination string
	if action.Destination == "" {
		destination = location[0]
	} else {
		destination = action.Destination
	}

	var (
		s1       = expand(location[0])
		s2       = expand(location[1])
		d1       = expand(destination)
		sx1, sy1 = split(s1)
		sx2, sy2 = split(s2)
		dx1, dy1 = split(d1)
	)
	if err != nil {
		return err
	}
	sx1, sx2 = order(sx1, sx2)
	sy1, sy2 = order(sy1, sy2)

	var areas []area

	//log.Debugln("merged cells")
	if mcs, err := e.template.GetMergeCells(e.sourceSheet); err == nil {
		areas = make([]area, 0, len(mcs))
		for _, mc := range mcs {
			if a, err := mergeCellToArea(mc); err == nil {
				areas = append(areas, a)
			}
		}
	}

	for y, i := sy1, 0; y <= sy2; y, i = y+1, i+1 {
	nextX:
		for x, j := sx1, 0; x <= sx2; x, j = x+1, j+1 {

			// check if cell is part of a merged cell
			for k := range areas {
				area := &areas[k]

				if area.contains(x, y) {
					ofsX := x - area.x1
					ofsY := y - area.y1

					sx := dx1 + j - ofsX
					sy := dy1 + i - ofsY
					ex := sx + (area.x2 - area.x1)
					ey := sy + (area.y2 - area.y1)

					// Copy over attributes
					for l := 0; l <= area.x2-area.x1; l++ {
						for m := 0; m <= area.y2-area.y1; m++ {
							src, err1 := excelize.CoordinatesToCellName(area.x1+l, area.y1+m)
							dst, err2 := excelize.CoordinatesToCellName(sx+l, sy+m)
							if err1 != nil || err2 != nil {
								continue
							}
							if s, err := e.template.GetCellStyle(e.sourceSheet, src); err == nil {
								e.template.SetCellStyle(e.destinationSheet, dst, dst, s)
							}
							if s, err := e.getCellFormula(e.sourceSheet, src); err == nil {
								e.setCellFormula(e.destinationSheet, dst, s)
							}
						}
					}

					dst, err := excelize.CoordinatesToCellName(sx, sy)
					if err != nil {
						continue nextX
					}

					// Copy over expanded text
					if v, err := e.typedExpand(area.mc.GetCellValue(), vars); err == nil {
						e.template.SetCellValue(e.destinationSheet, dst, v)
					}

					// Finally merge the cells
					if end, err := excelize.CoordinatesToCellName(ex, ey); err == nil {
						e.template.MergeCell(e.destinationSheet, dst, end)
					}

					continue nextX
				}
			}

			// Regular cell

			src, err := excelize.CoordinatesToCellName(x, y)
			if err != nil {
				continue
			}
			dst, err := excelize.CoordinatesToCellName(dx1+j, dy1+i)
			if err != nil {
				continue
			}

			cn, err := excelize.ColumnNumberToName(x)
			if err != nil {
				continue
			}

			cw, err := e.template.GetColWidth(e.sourceSheet, cn)
			if err != nil {
				continue
			}

			rh, err := e.template.GetRowHeight(e.sourceSheet, y)
			if err != nil {
				continue
			}

			dc, err := excelize.ColumnNumberToName(dx1 + j)
			if err != nil {
				continue
			}

			if e.template.SetColWidth(e.destinationSheet, dc, dc, cw) != nil {
				continue
			}

			if e.template.SetRowHeight(e.destinationSheet, dy1+i, rh) != nil {
				continue
			}

			if s, err := e.template.GetCellStyle(e.sourceSheet, src); err == nil {
				e.template.SetCellStyle(e.destinationSheet, dst, dst, s)
			}
			if s, err := e.getCellFormula(e.sourceSheet, src); err == nil {
				e.setCellFormula(e.destinationSheet, dst, s)
			}
			if s, err := e.template.GetCellValue(e.sourceSheet, src); err == nil {
				if v, err := e.typedExpand(s, vars); err == nil {
					e.template.SetCellValue(e.destinationSheet, dst, v)
				}
			}
		}
	}

	return nil
}

func (e *executor) sel(action *Action) error {
	vars := e.vars()

	eval := func(x string) (interface{}, error) {
		f, err := e.expr(x)
		if err != nil {
			return nil, err
		}
		return f(e.ctx, vars)
	}

	res, err := query(e.ctx, e.tx, action.Statement, eval)
	if err != nil {
		return err
	}

	e.frames = append(e.frames, frame{res: res})
	defer e.popFrame()

	for i := range res.rows {
		e.frames[len(e.frames)-1].index = i
		if err := e.actions(action); err != nil {
			return err
		}
	}

	return nil
}

func (e *executor) actions(action *Action) error {
	for _, a := range action.Actions {
		if err := e.dispatch(a); err != nil {
			return err
		}
	}
	return nil
}

func (e *executor) sheet(action *Action) error {

	vars := e.vars()
	source, err := e.expand(action.Source, vars)
	if err != nil {
		return err
	}

	srcIdx := e.template.GetSheetIndex(source)
	if srcIdx == -1 {
		return fmt.Errorf("sheet '%s' not found", source)
	}

	destination := action.Destination
	if destination == "" { // same as source
		e.keep[source] = true
		destination = source
	} else { // new sheet
		destination, err = e.expand(destination, vars)
		if err != nil {
			return err
		}
		dstIdx := e.template.NewSheet(destination)
		if len(action.Actions) == 0 {
			// Only copy if there are no explicit instructions.
			if err := e.template.CopySheet(srcIdx, dstIdx); err != nil {
				return err
			}
		}
	}

	if len(action.Actions) > 0 {
		pSrc, pDst := e.sourceSheet, e.destinationSheet
		defer func() {
			e.sourceSheet, e.destinationSheet = pSrc, pDst
		}()
		e.sourceSheet, e.destinationSheet = source, destination
		return e.actions(action)
	}

	// Simple filling

	// "{{" only as a quick filter
	result, err := e.template.SearchSheet(destination, "{{", true)
	if err != nil {
		return err
	}
	for _, axis := range result {
		value, err := e.template.GetCellValue(destination, axis)
		if err != nil {
			return err
		}
		nvalue, err := e.typedExpand(value, vars)
		if err != nil {
			return err
		}
		if err := e.template.SetCellValue(destination, axis, nvalue); err != nil {
			return err
		}
	}

	return nil
}

func columnToNum(col interface{}) interface{} {
	var name string
	switch v := col.(type) {
	case string:
		name = v
	default:
		name = fmt.Sprintf("%v", col)
	}
	num, err := excelize.ColumnNameToNumber(name)
	if err != nil {
		log.Errorf("invalid column name '%v'\n", col)
		return 1
	}
	return num
}

func asInt(i interface{}) (int, error) {
	switch v := i.(type) {
	case int:
		return v, nil
	case int8:
		return int(v), nil
	case int16:
		return int(v), nil
	case int32:
		return int(v), nil
	case int64:
		return int(v), nil
	case float32:
		return int(v), nil
	case float64:
		return int(v), nil
	case string:
		return strconv.Atoi(v)
	default:
		return 0, fmt.Errorf("invalid int '%v'", i)
	}
}

func coord2cell(ix, iy interface{}) interface{} {
	x, err := asInt(ix)
	if err != nil {
		log.Errorf("invalid x value: %v\n", err)
		return "A1"
	}
	y, err := asInt(iy)
	if err != nil {
		log.Errorf("invalid y value: %v\n", err)
		return "A1"
	}

	cell, err := excelize.CoordinatesToCellName(x, y)
	if err != nil {
		log.Errorf("invalid cell coord (%d, %d)\n", x, y)
		return "A1"
	}
	return cell
}

var templateLang = gval.Full(
	gval.Function("column2num", columnToNum),
	gval.Function("coord2cell", coord2cell),
)

func (e *executor) expr(x string) (gval.Evaluable, error) {
	if f := e.expressions[x]; f != nil {
		return f, nil
	}
	f, err := templateLang.NewEvaluable(x)
	if err != nil {
		return nil, err
	}
	e.expressions[x] = f
	return f, nil
}

func (e *executor) vars() map[string]interface{} {
	vars := map[string]interface{}{}
	if len(e.frames) > 0 {
		vars["row_number"] = e.frames[len(e.frames)-1].index
	}
	for i := len(e.frames) - 1; i >= 0; i-- {
		fr := &e.frames[i]
		for j, n := range fr.res.columns {
			if _, found := vars[n]; !found {
				vars[n] = fr.res.rows[fr.index][j]
			}
		}
	}
	return vars
}

func (e *executor) expand(
	str string,
	vars map[string]interface{},
) (string, error) {

	var err error

	replace := func(s string) string {
		if err != nil {
			return ""
		}
		var eval gval.Evaluable
		if eval, err = e.expr(strings.TrimSpace(s)); err != nil {
			return ""
		}
		s, err = eval.EvalString(e.ctx, vars)
		if err != nil {
			log.Errorf("'%s' '%s' %v\n", str, s, err)
		}
		return s
	}

	str = handlebars(str, replace)
	return str, err
}

func (e *executor) typedExpand(
	str string,
	vars map[string]interface{},
) (interface{}, error) {

	var (
		err      error
		repCount int
		last     interface{}
	)

	replace := func(s string) string {
		if err != nil {
			return ""
		}
		var eval gval.Evaluable
		if eval, err = e.expr(strings.TrimSpace(s)); err != nil {
			return ""
		}
		repCount++
		last, err = eval(e.ctx, vars)
		if err != nil {
			log.Errorf("'%s' '%s' %v\n", str, s, err)
		}
		return fmt.Sprintf("%v", last)
	}

	nstr := handlebars(str, replace)

	if err != nil {
		return nil, err
	}

	if repCount == 1 &&
		strings.HasPrefix(str, "{{") &&
		strings.HasSuffix(str, "}}") {
		return last, nil
	}
	return nstr, nil
}