package webutility

import (
	"database/sql"
	"fmt"
)

// ListOptions ...
type ListOptions struct {
	GlobalFilter  bool   `json:"globalFilter"`
	LocalFilters  bool   `json:"localFilters"`
	RemoteFilters bool   `json:"remoteFilters"`
	Pagination    bool   `json:"pagination"`
	PageSize      uint32 `json:"pageSize"`
	Pivot         bool   `json:"pivot"`
	Detail        bool   `json:"detail"`
	Total         bool   `json:"total"`
}

// ListFilter ...
type ListFilter struct {
	Position       uint32   `json:"-"`
	ObjectType     string   `json:"-"`
	FiltersField   string   `json:"filtersField"`
	DefaultValues  string   `json:"defaultValues"`
	FiltersType    string   `json:"filtersType"`
	FiltersLabel   string   `json:"filtersLabel"`
	DropdownConfig Dropdown `json:"dropdownConfig"`
}

// Dropdown ...
type Dropdown struct {
	ObjectType   string `json:"objectType"`
	FiltersField string `json:"filtersField"`
	IDField      string `json:"idField"`
	LabelField   string `json:"labelField"`
}

// ListGraph ...
type ListGraph struct {
	ObjectType string `json:"objectType"`
	X          string `json:"xField"`
	Y          string `json:"yField"`
	GroupField string `json:"groupField"`
	Label      string `json:"label"`
}

// ListActions ...
type ListActions struct {
	Create    bool `json:"create"`
	Update    bool `json:"update"`
	Delete    bool `json:"delete"`
	Export    bool `json:"export"`
	Print     bool `json:"print"`
	Graph     bool `json:"graph"`
	LiveGraph bool `json:"liveGraph"`
	SaveFile  bool `json:"saveFile"`
	ShowFile  bool `json:"showFile"`
}

// ListNavNode ...
type ListNavNode struct {
	ObjectType        string `json:"objectType"`
	LabelField        string `json:"label"`
	Icon              string `json:"icon"`
	ParentObjectType  string `json:"parentObjectType"`
	ParentIDField     string `json:"parentIdField"`
	ParentFilterField string `json:"parentFilterField"`
}

// ListParentNode ...
type ListParentNode struct {
	ObjectType  string `json:"objectType"`
	LabelField  string `json:"labelField"`
	FilterField string `json:"filterField"`
}

// ListPivot ...
type ListPivot struct {
	ObjectType    string `json:"objectType"`
	GroupField    string `json:"groupField"`
	DistinctField string `json:"distinctField"`
	Value         string `json:"valueField"`
}

// ListDetails ...
type ListDetails struct {
	ObjectType        string `json:"objectType"`
	ParentObjectType  string `json:"parentObjectType"`
	ParentFilterField string `json:"parentFilterField"`
	SingleDetail      bool   `json:"singleDetail"`
}

// ListLiveGraph ...
type ListLiveGraph struct {
	ObjectType  string `json:"objectType"`
	ValueFields string `json:"valueFields"`
	LabelFields string `json:"labelFields"`
}

// ListConfig ...
type ListConfig struct {
	ObjectType string           `json:"objectType"`
	Title      string           `json:"title"`
	LazyLoad   bool             `json:"lazyLoad"`
	InlineEdit bool             `json:"inlineEdit"`
	Options    ListOptions      `json:"options"`
	Filters    []ListFilter     `json:"defaultFilters"`
	Graphs     []ListGraph      `json:"graphs"`
	Actions    ListActions      `json:"actions"`
	Parent     []ListParentNode `json:"parent"`
	Navigation []ListNavNode    `json:"navigation"`
	Pivots     []ListPivot      `json:"pivots"`
	Details    ListDetails      `json:"details"`
	LiveGraph  ListLiveGraph    `json:"liveGraphs"`
}

// GetListConfig returns list configuration for the provided object type for the front-end application
// or an error if it fails.
func GetListConfig(db *sql.DB, objType string) (ListConfig, error) {
	list := NewListConfig(objType)

	err := list.setParams(db, objType)
	err = list.SetNavigation(db, objType)
	err = list.SetActions(db, objType)
	err = list.SetFilters(db, objType)
	err = list.SetOptions(db, objType)
	err = list.SetParent(db, objType)
	err = list.SetPivot(db, objType)
	err = list.SetGraph(db, objType)
	err = list.SetDetails(db, objType)
	err = list.SetLiveGraph(db, objType)

	if err != nil {
		return list, err
	}

	return list, nil
}

// GetListConfigObjectIDField takes in database connection and an object type and it returns the
// ID field name for the provided object type.
func GetListConfigObjectIDField(db *sql.DB, otype string) string {
	var resp string

	rows, err := db.Query(`SELECT
		ID_FIELD
		FROM LIST_CONFIG_ID_FIELD
		WHERE OBJECT_TYPE = ` + fmt.Sprintf("'%s'", otype))
	if err != nil {
		return ""
	}
	defer rows.Close()

	if rows.Next() {
		rows.Scan(&resp)
	}

	if rows.Err() != nil {
		return ""
	}

	return resp
}

// NewListConfig returns default configuration for the provided object type.
func NewListConfig(objType string) ListConfig {
	list := ListConfig{
		ObjectType: objType,
		Title:      objType,
		LazyLoad:   false,
		Options: ListOptions{
			GlobalFilter:  true,
			LocalFilters:  true,
			RemoteFilters: false,
			Pagination:    true,
			PageSize:      20,
		},
		Filters: nil,
		Actions: ListActions{
			Create:    false,
			Update:    false,
			Delete:    false,
			Export:    false,
			Print:     false,
			Graph:     false,
			LiveGraph: false,
		},
		Parent:     nil,
		Navigation: nil,
	}

	return list
}

// setParams sets the default parameters of the provided configuration list for the provided object type.
func (list *ListConfig) setParams(db *sql.DB, objType string) error {
	rows, err := db.Query(`SELECT
		OBJECT_TYPE,
		TITLE,
		LAZY_LOAD,
		INLINE_EDIT
		FROM LIST_CONFIG
		WHERE OBJECT_TYPE = ` + fmt.Sprintf("'%s'", objType))
	if err != nil {
		return err
	}
	defer rows.Close()
	if rows.Next() {
		otype, title := "", ""
		lazyLoad, inlineEdit := 0, 0
		rows.Scan(&otype, &title, &lazyLoad, &inlineEdit)

		if otype != "" {
			list.ObjectType = otype
		}
		if title != "" {
			list.Title = title
		}
		list.LazyLoad = lazyLoad != 0
		list.InlineEdit = inlineEdit != 0
	}
	if rows.Err() != nil {
		return rows.Err()
	}
	return nil
}

// SetNavigation returns set's navigation nodes for listObjType object type.
func (list *ListConfig) SetNavigation(db *sql.DB, listObjType string) error {
	list.Navigation = make([]ListNavNode, 0)
	rows, err := db.Query(`SELECT
		a.OBJECT_TYPE,
		a.PARENT_OBJECT_TYPE,
		a.LABEL,
		a.ICON,
		a.PARENT_FILTER_FIELD,
		b.PARENT_ID_FIELD
		FROM LIST_CONFIG_NAVIGATION b
		JOIN LIST_CONFIG_CHILD a ON b.PARENT_CHILD_ID = a.PARENT_CHILD_ID
		WHERE b.LIST_OBJECT_TYPE = ` + fmt.Sprintf("'%s'", listObjType) +
		` ORDER BY b.RB ASC`)
	if err != nil {
		return err
	}
	defer rows.Close()

	var node ListNavNode
	for rows.Next() {
		rows.Scan(&node.ObjectType, &node.ParentObjectType, &node.LabelField, &node.Icon,
			&node.ParentFilterField, &node.ParentIDField)
		list.Navigation = append(list.Navigation, node)
	}
	if rows.Err() != nil {
		return rows.Err()
	}

	return nil
}

// SetActions sets list's actions based for objType object type.
func (list *ListConfig) SetActions(db *sql.DB, objType string) error {
	rows, err := db.Query(`SELECT
		ACTION_CREATE,
		ACTION_UPDATE,
		ACTION_DELETE,
		ACTION_EXPORT,
		ACTION_PRINT,
		ACTION_GRAPH,
		ACTION_LIVE_GRAPH,
		ACTION_SAVE_FILE,
		ACTION_SHOW_FILE
		FROM LIST_CONFIG
		WHERE OBJECT_TYPE = ` + fmt.Sprintf("'%s'", objType))
	if err != nil {
		return err
	}
	defer rows.Close()

	var create, update, delete, export, print, graph, liveGraph, saveFile, showFile uint32
	if rows.Next() {
		rows.Scan(&create, &update, &delete, &export, &print, &graph, &liveGraph, &saveFile, &showFile)
		list.Actions.Create = create != 0
		list.Actions.Update = update != 0
		list.Actions.Delete = delete != 0
		list.Actions.Export = export != 0
		list.Actions.Print = print != 0
		list.Actions.Graph = graph != 0
		list.Actions.LiveGraph = liveGraph != 0
		list.Actions.SaveFile = saveFile != 0
		list.Actions.ShowFile = showFile != 0
	}
	if rows.Err() != nil {
		return rows.Err()
	}

	return nil
}

// SetFilters ...
func (list *ListConfig) SetFilters(db *sql.DB, objType string) error {
	list.Filters = make([]ListFilter, 0)
	filtersFields, err := list.GetFilterFieldsAndPosition(db, objType)
	if err != nil {
		return err
	}
	for field, pos := range filtersFields {
		filters, _ := list.getFiltersByFilterField(db, field)
		for _, filter := range filters {
			var f ListFilter
			f.Position = pos
			f.ObjectType = objType
			f.FiltersField = field
			f.DefaultValues = filter.DefaultValues
			f.FiltersLabel = filter.Label
			f.FiltersType = filter.Type
			if filter.Type == "dropdown" {
				err := f.SetDropdownConfig(db, field)
				if err != nil {
					return err
				}
			}
			list.Filters = append(list.Filters, f)
		}
	}

	list.sortFilters()

	return nil
}

// GetFilterFieldsAndPosition returns a map of filter fields and their respective position in the menu.
func (list *ListConfig) GetFilterFieldsAndPosition(db *sql.DB, objType string) (map[string]uint32, error) {
	filtersField := make(map[string]uint32, 0)
	rows, err := db.Query(`SELECT
		FILTERS_FIELD,
		RB
		FROM LIST_CONFIG_FILTERS
		WHERE OBJECT_TYPE = ` + fmt.Sprintf("'%s'", objType))
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		var field string
		var rb uint32
		rows.Scan(&field, &rb)
		filtersField[field] = rb
	}
	if rows.Err() != nil {
		return nil, rows.Err()
	}
	return filtersField, nil
}

type _filter struct {
	DefaultValues string
	Label         string
	Type          string
}

// getFiltersByFilterField ...
func (list *ListConfig) getFiltersByFilterField(db *sql.DB, filtersField string) ([]_filter, error) {
	resp := make([]_filter, 0)
	rows, err := db.Query(`SELECT
		FILTERS_TYPE,
		FILTERS_LABEL,
		DEFAULT_VALUES
		FROM LIST_FILTERS_FIELD
		WHERE FILTERS_FIELD = ` + fmt.Sprintf("'%s'", filtersField))
	if err != nil {
		return resp, err
	}
	defer rows.Close()

	var f _filter
	for rows.Next() {
		rows.Scan(&f.Type, &f.Label, &f.DefaultValues)
		resp = append(resp, f)
	}
	if rows.Err() != nil {
		return resp, rows.Err()
	}
	return resp, nil
}

// SetDropdownConfig ...
func (f *ListFilter) SetDropdownConfig(db *sql.DB, filtersField string) error {
	var resp Dropdown
	rows, err := db.Query(`SELECT
		FILTERS_FIELD,
		OBJECT_TYPE,
		ID_FIELD,
		LABEL_FIELD
		FROM LIST_DROPDOWN_FILTER
		WHERE FILTERS_FIELD = ` + fmt.Sprintf("'%s'", filtersField))
	if err != nil {
		return err
	}
	defer rows.Close()
	if rows.Next() {
		rows.Scan(&resp.FiltersField, &resp.ObjectType, &resp.IDField, &resp.LabelField)
	}
	if rows.Err() != nil {
		return rows.Err()
	}

	f.DropdownConfig = resp

	return nil
}

// sortFilters bubble sorts provided filters slice by position field.
func (list *ListConfig) sortFilters() {
	done := false
	var temp ListFilter
	for !done {
		done = true
		for i := 0; i < len(list.Filters)-1; i++ {
			if list.Filters[i].Position > list.Filters[i+1].Position {
				done = false
				temp = list.Filters[i]
				list.Filters[i] = list.Filters[i+1]
				list.Filters[i+1] = temp
			}
		}
	}
}

// SetGraph ...
func (list *ListConfig) SetGraph(db *sql.DB, objType string) error {
	list.Graphs = make([]ListGraph, 0)
	rows, err := db.Query(`SELECT
		OBJECT_TYPE,
		X_FIELD,
		Y_FIELD,
		GROUP_FIELD,
		LABEL
		FROM LIST_GRAPHS
		WHERE OBJECT_TYPE = ` + fmt.Sprintf("'%s'", objType))
	if err != nil {
		return err
	}
	defer rows.Close()

	var lg ListGraph
	for rows.Next() {
		rows.Scan(&lg.ObjectType, &lg.X, &lg.Y, &lg.GroupField, &lg.Label)
		list.Graphs = append(list.Graphs, lg)
	}
	if rows.Err() != nil {
		return rows.Err()
	}

	return nil
}

// SetOptions ...
func (list *ListConfig) SetOptions(db *sql.DB, objType string) error {
	rows, err := db.Query(`SELECT
		GLOBAL_FILTER,
		LOCAL_FILTER,
		REMOTE_FILTER,
		PAGINATION,
		PAGE_SIZE,
		PIVOT,
		DETAIL,
		TOTAL
		FROM LIST_CONFIG
		WHERE OBJECT_TYPE = ` + fmt.Sprintf("'%s'", objType))
	if err != nil {
		return err
	}
	defer rows.Close()

	if rows.Next() {
		var gfilter, lfilters, rfilters, pagination, pageSize, pivot, detail, total uint32
		rows.Scan(&gfilter, &lfilters, &rfilters, &pagination, &pageSize, &pivot, &detail, &total)
		list.Options.GlobalFilter = gfilter != 0
		list.Options.LocalFilters = lfilters != 0
		list.Options.RemoteFilters = rfilters != 0
		list.Options.Pagination = pagination != 0
		list.Options.PageSize = pageSize
		list.Options.Pivot = pivot != 0
		list.Options.Detail = detail != 0
		list.Options.Total = total != 0
	}
	if rows.Err() != nil {
		return rows.Err()
	}

	return nil
}

// SetParent ...
func (list *ListConfig) SetParent(db *sql.DB, objType string) error {
	list.Parent = make([]ListParentNode, 0)
	rows, err := db.Query(`SELECT
		PARENT_OBJECT_TYPE,
		PARENT_LABEL_FIELD,
		PARENT_FILTER_FIELD
		FROM LIST_CONFIG_CHILD
		WHERE OBJECT_TYPE = ` + fmt.Sprintf("'%s'", objType))
	if err != nil {
		return err
	}
	defer rows.Close()

	var pnode ListParentNode
	for rows.Next() {
		rows.Scan(&pnode.ObjectType, &pnode.LabelField, &pnode.FilterField)
		list.Parent = append(list.Parent, pnode)
	}
	if rows.Err() != nil {
		return rows.Err()
	}

	return nil
}

// SetPivot ...
func (list *ListConfig) SetPivot(db *sql.DB, objType string) error {
	list.Pivots = make([]ListPivot, 0)
	rows, err := db.Query(`SELECT
		OBJECT_TYPE,
		GROUP_FIELD,
		DISTINCT_FIELD,
		VALUE_FIELD
		FROM LIST_PIVOTS
		WHERE OBJECT_TYPE = ` + fmt.Sprintf("'%s'", objType))
	if err != nil {
		return err
	}
	defer rows.Close()

	var p ListPivot
	for rows.Next() {
		rows.Scan(&p.ObjectType, &p.GroupField, &p.DistinctField, &p.Value)
		list.Pivots = append(list.Pivots, p)
	}
	if rows.Err() != nil {
		return rows.Err()
	}

	return nil
}

// SetDetails ...
func (list *ListConfig) SetDetails(db *sql.DB, objType string) error {
	var resp ListDetails
	rows, err := db.Query(`SELECT
		OBJECT_TYPE,
		PARENT_OBJECT_TYPE,
		PARENT_FILTER_FIELD,
		SINGLE_DETAIL
		FROM LIST_CONFIG_DETAIL
		WHERE PARENT_OBJECT_TYPE = ` + fmt.Sprintf("'%s'", objType))
	if err != nil {
		return err
	}
	defer rows.Close()
	if rows.Next() {
		var singleDetail uint32
		rows.Scan(&resp.ObjectType, &resp.ParentObjectType, &resp.ParentFilterField, &singleDetail)
		resp.SingleDetail = singleDetail != 0
	}
	if rows.Err() != nil {
		return rows.Err()
	}

	list.Details = resp

	return nil
}

// SetLiveGraph ...
func (list *ListConfig) SetLiveGraph(db *sql.DB, objType string) error {
	var resp ListLiveGraph
	rows, err := db.Query(`SELECT
		OBJECT_TYPE,
		VALUE_FIELDS,
		LABEL_FIELDS
		FROM LIST_LIVE_GRAPH
		WHERE OBJECT_TYPE = ` + fmt.Sprintf("'%s'", objType))
	if err != nil {
		return err
	}
	defer rows.Close()
	if rows.Next() {
		rows.Scan(&resp.ObjectType, &resp.ValueFields, &resp.LabelFields)
	}
	if rows.Err() != nil {
		return rows.Err()
	}

	list.LiveGraph = resp

	return nil
}