// 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"
	"strings"

	"github.com/360EntSecGroup-Skylar/excelize/v2"
	"gopkg.in/yaml.v2"

	"github.com/PaesslerAG/gval"
)

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 executor struct {
	ctx              context.Context
	db               *sql.Conn
	template         *excelize.File
	keep             map[string]bool
	expressions      map[string]gval.Evaluable
	sourceSheet      string
	destinationSheet string
	frames           []frame
}

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,
	db *sql.Conn,
	template *excelize.File,
) error {

	e := executor{
		ctx:         ctx,
		db:          db,
		template:    template,
		keep:        map[string]bool{},
		expressions: map[string]gval.Evaluable{},
	}

	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
}

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 (e *executor) copy(action *Action) error {
	if len(action.Location) != 2 {
		return fmt.Errorf("length location = %d (expect 2)",
			len(action.Source))
	}

	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 {
			var cell string
			if cell, y, err = excelize.SplitCellName(s); err == nil {
				x, err = excelize.ColumnNameToNumber(cell)
			}
		}
		return x, y
	}
	order := func(a, b int) (int, int) {
		if a > b {
			return b, a
		}
		return a, b
	}

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

	//log.Printf("%s/%s -> %s\n", sFrom, sTo, dTo)

	for y, i := sy1, 0; y <= sy2; y, i = y+1, i+1 {
		for x, j := sx1, 0; x <= sx2; x, j = x+1, j+1 {
			src, err1 := excelize.CoordinatesToCellName(x, y)
			dst, err2 := excelize.CoordinatesToCellName(dx1+j, dy1+i)
			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.template.GetCellFormula(e.sourceSheet, src); err == nil {
				e.template.SetCellFormula(e.destinationSheet, dst, s)
			}
			if s, err := e.template.GetCellValue(e.sourceSheet, src); err == nil {
				if s, err = e.expand(s, vars); err == nil {
					e.template.SetCellStr(e.destinationSheet, dst, s)
				}
			}
		}
	}

	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.db, 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
		}
		value, err = e.expand(value, vars)
		if err != nil {
			return err
		}
		if err := e.template.SetCellStr(destination, axis, value); err != nil {
			return err
		}
	}

	return nil
}

func (e *executor) expr(x string) (gval.Evaluable, error) {
	if f := e.expressions[x]; f != nil {
		return f, nil
	}
	f, err := gval.Full().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)
		return s
	}

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