package main

import "C"
import (
	"errors"
	"fmt"
	"fyne.io/fyne/v2"
	"fyne.io/fyne/v2/app"
	"fyne.io/fyne/v2/container"
	"fyne.io/fyne/v2/dialog"
	"fyne.io/fyne/v2/layout"
	"fyne.io/fyne/v2/widget"
	"strings"
)

type DBInterface struct {
	application fyne.App
	window      fyne.Window
	db          SQLConnection
}

func genMainView(view fyne.CanvasObject, buttons []fyne.CanvasObject) fyne.CanvasObject {
	return container.NewBorder(
		nil, container.NewGridWithColumns(len(buttons), buttons...), nil, nil, view)
}

func parseFields(box *fyne.Container) ([]string, error) {
	var query []string
	for _, obj := range box.Objects {
		constraint := obj.(*fyne.Container).Objects
		column := constraint[0].(*widget.Select)

		if column.SelectedIndex() == -1 {
			return nil, errors.New("не выбрано одно из полей")
		}

		query = append(query, column.Selected)
	}
	return query, nil
}

func parseQuery(box *fyne.Container) ([]string, error) {
	var query []string
	for _, obj := range box.Objects {
		constraint := obj.(*fyne.Container).Objects
		column := constraint[0].(*widget.Select)
		operator := constraint[1].(*widget.Select)
		value := constraint[2].(*widget.Entry)

		if column.SelectedIndex() == -1 {
			return nil, errors.New("в одном из ограничений не выбрана колонка")
		}
		if operator.SelectedIndex() == -1 {
			return nil, errors.New("в одном из ограничений не выбран оператор")
		}

		query = append(query,
			fmt.Sprintf("%s %s '%s'", column.Selected, operator.Selected, value.Text))
	}
	return query, nil
}

func (app *DBInterface) insertAdminView(table string) {
	app.window.SetTitle(fmt.Sprintf("Вставка в таблицу [%s] (Режим администратора)", table))
	columns, _ := app.db.getColumns(table)
	identityCols, _ := app.db.getIdentityColumns(strings.Split(table, ".")[2])

	form := container.New(layout.NewFormLayout())
	for _, val := range columns {
		form.Add(widget.NewLabel(val))
		entry := widget.NewEntry()
		form.Add(entry)
		for _, identity := range identityCols {
			if identity == val {
				entry.PlaceHolder = "Данная колонка является автозаполняемой"
				entry.Disable()
			}
		}
	}
	form.Refresh()

	insertBtn := widget.NewButton("Вставить", func() {
		var insertColumns, insertValues []string
		for i := 0; i < len(form.Objects); i += 2 {
			column := form.Objects[i].(*widget.Label).Text
			isIdentity := false
			for _, identity := range identityCols {
				if identity == column {
					isIdentity = true
					break
				}
			}
			if isIdentity {
				continue
			}
			insertColumns = append(insertColumns, column)
			insertValues = append(insertValues, form.Objects[i+1].(*widget.Entry).Text)
		}
		err := app.db.insertIntoTable(table, insertColumns, insertValues)
		if err != nil {
			dialog.ShowInformation(
				"Вставка",
				err.Error(),
				app.window)
			return
		}
		dialog.ShowInformation(
			"Вставка",
			"Вставка выполнена успешно",
			app.window)
	})
	backBtn := widget.NewButton("Назад", func() {
		app.showAdminTable(table)
	})
	app.window.SetContent(
		genMainView(
			container.NewVScroll(form),
			[]fyne.CanvasObject{insertBtn, backBtn}))
}

func (app *DBInterface) deleteAdminView(table string) {
	app.window.SetTitle(fmt.Sprintf("Удаление из таблицы [%s] (Режим администратора)", table))
	columns, _ := app.db.getColumns(table)

	queryBox := container.NewVBox()
	newConstraintBtn := widget.NewButton("Добавить новое ограничение", func() {
		selectBtn := widget.NewSelect(columns, func(value string) {})
		operatorBtn := widget.NewSelect([]string{"=", "<", ">", "<=", ">=", "LIKE"}, func(value string) {})
		compareField := widget.NewEntry()
		fieldConstraint := container.NewGridWithColumns(3, selectBtn, operatorBtn, compareField)
		queryBox.Add(fieldConstraint)
		queryBox.Refresh()
	})

	deleteBtn := widget.NewButton("Выполнить удаление", func() {
		if len(queryBox.Objects) == 0 {
			dialog.ShowInformation(
				"Удаление",
				"Необходимо указать как минимум одно ограничение",
				app.window)
		} else {
			query, err := parseQuery(queryBox)
			if err != nil {
				dialog.ShowInformation(
					"Удаление",
					err.Error(),
					app.window)
				return
			}
			err = app.db.deleteWhere(table, query)
			if err != nil {
				dialog.ShowInformation(
					"Удаление",
					err.Error(),
					app.window)
				return
			}

			dialog.ShowInformation(
				"Удаление",
				"Удаление выполнено успешно",
				app.window)
		}
	})
	backBtn := widget.NewButton("Назад", func() {
		app.showAdminTable(table)
	})
	app.window.SetContent(
		genMainView(
			container.NewVScroll(
				container.NewVBox(
					widget.NewLabel(fmt.Sprintf("DELETE FROM [%s] WHERE", table)),
					queryBox,
					newConstraintBtn)),
			[]fyne.CanvasObject{deleteBtn, backBtn}))
}

func (app *DBInterface) selectAdminView(table string) {
	app.window.SetTitle(fmt.Sprintf("Выбор из таблицы [%s] (Режим администратора)", table))

	columns, _ := app.db.getColumns(table)

	selectFields := container.NewVBox()
	newField := widget.NewButton("Добавить новое поле", func() {
		selectFields.Add(
			container.NewHBox(
				widget.NewSelect(columns, func(value string) {}),
				widget.NewLabel(",")))
		selectFields.Refresh()
	})

	queryBox := container.NewVBox()
	newConstraintBtn := widget.NewButton("Добавить новое ограничение", func() {
		selectBtn := widget.NewSelect(columns, func(value string) {})
		operatorBtn := widget.NewSelect([]string{"=", "<", ">", "<=", ">=", "LIKE"}, func(value string) {})
		compareField := widget.NewEntry()
		fieldConstraint := container.NewGridWithColumns(3, selectBtn, operatorBtn, compareField)
		queryBox.Add(fieldConstraint)
		queryBox.Refresh()
	})

	backBtn := widget.NewButton("Назад", func() {
		app.showAdminTable(table)
	})

	selectBtn := widget.NewButton("Показать результаты выбора", func() {
		if len(selectFields.Objects) == 0 {
			dialog.ShowInformation(
				"Выбор",
				"Не выбрано ни одного поля для запроса",
				app.window)
		} else {
			w := app.application.NewWindow("")
			w.Resize(fyne.NewSize(100, 100))
			conn := DBInterface{app.application, w, app.db}
			conn.selectedShowAdmin(table, selectFields, queryBox)
			w.Show()
		}
	})

	app.window.SetContent(
		genMainView(
			container.NewVScroll(
				container.NewVBox(
					widget.NewLabel("SELECT"),
					selectFields,
					newField,
					widget.NewLabel(fmt.Sprintf("FROM [%s] WHERE", table)),
					queryBox,
					newConstraintBtn)),
			[]fyne.CanvasObject{selectBtn, backBtn}))
}

func (app *DBInterface) updateRowAdminView(table string, rowData []string) {
	app.window.SetTitle(fmt.Sprintf("Обновшение ряда таблицы [%s] (Режим администратора)", table))

	columns, _ := app.db.getColumns(table)
	identityCols, _ := app.db.getIdentityColumns(strings.Split(table, ".")[2])

	identityIdx := -1
	form := container.New(layout.NewFormLayout())
	for i, val := range columns {
		form.Add(widget.NewLabel(val))
		entry := widget.NewEntry()
		entry.SetText(rowData[i])
		form.Add(entry)
		for j, identity := range identityCols {
			if identity == val {
				identityIdx = j
				entry.PlaceHolder = "Данная колонка является автозаполняемой"
				entry.Disable()
			}
		}
	}
	form.Refresh()

	insertBtn := widget.NewButton("Изменить", func() {
		var updateColumns, updateValues []string
		for i := 0; i < len(form.Objects); i += 2 {
			column := form.Objects[i].(*widget.Label).Text
			isIdentity := false
			for _, identity := range identityCols {
				if identity == column {
					isIdentity = true
					break
				}
			}
			if isIdentity {
				continue
			}
			updateColumns = append(updateColumns, column)
			updateValues = append(updateValues, form.Objects[i+1].(*widget.Entry).Text)
		}
		err := app.db.updateRow(table, updateColumns, updateValues, rowData[identityIdx])
		if err != nil {
			dialog.ShowInformation(
				"Изменение записи",
				err.Error(),
				app.window)
			return
		}
		dialog.ShowInformation(
			"Изменение записи",
			"Изменение записи выполнено успешно",
			app.window)
	})
	backBtn := widget.NewButton("Назад", func() {
		app.showAdminTable(table)
	})
	app.window.SetContent(
		genMainView(
			container.NewVScroll(form),
			[]fyne.CanvasObject{insertBtn, backBtn}))

}

func (app *DBInterface) selectedShowAdmin(table string, fields *fyne.Container, constraints *fyne.Container) {
	app.window.SetTitle("Результаты выбора (Режим администратора)")

	fieldsStr, _ := parseFields(fields)
	query, _ := parseQuery(constraints)

	data, columns, err := app.db.selectWhere(table, fieldsStr, query)
	if err != nil {
		fmt.Println(err)
		return
	}

	tableWidget := widget.NewTable(
		func() (int, int) {
			return len(data) + 1, len(columns)
		},
		func() fyne.CanvasObject {
			return widget.NewLabel("some very wide content")
		},
		func(i widget.TableCellID, o fyne.CanvasObject) {
			if i.Row == 0 {
				o.(*widget.Label).Alignment = fyne.TextAlignCenter
				o.(*widget.Label).TextStyle = fyne.TextStyle{Bold: true}
				o.(*widget.Label).SetText(fmt.Sprintf("%v", columns[i.Col]))
			} else {
				o.(*widget.Label).Alignment = fyne.TextAlignLeading
				o.(*widget.Label).TextStyle = fyne.TextStyle{Bold: false}
				o.(*widget.Label).SetText(fmt.Sprintf("%v", data[i.Row-1][i.Col]))
			}
		})

	app.window.SetContent(container.NewMax(tableWidget))
}

func (app *DBInterface) oneToManyShowAdmin(manyTable string, manyColumn string, oneValue string) {
	app.window.SetTitle("Отношение один ко многим (Режим администратора)")
	data, err := app.db.getOneToManyTable(manyTable, manyColumn, oneValue)
	if err != nil {
		fmt.Println(err)
		return
	}
	columns, err := app.db.getColumns(manyTable)

	tableWidget := widget.NewTable(
		func() (int, int) {
			return len(data) + 1, len(columns)
		},
		func() fyne.CanvasObject {
			return widget.NewLabel("some very wide content")
		},
		func(i widget.TableCellID, o fyne.CanvasObject) {
			if i.Row == 0 {
				o.(*widget.Label).Alignment = fyne.TextAlignCenter
				o.(*widget.Label).TextStyle = fyne.TextStyle{Bold: true}
				o.(*widget.Label).SetText(fmt.Sprintf("%v", columns[i.Col]))
			} else {
				o.(*widget.Label).Alignment = fyne.TextAlignLeading
				o.(*widget.Label).TextStyle = fyne.TextStyle{Bold: false}
				o.(*widget.Label).SetText(fmt.Sprintf("%v", data[i.Row-1][i.Col]))
			}
		})

	app.window.SetContent(container.NewMax(tableWidget))
}

func (app *DBInterface) oneToManyParamsAdmin(table string, row int) {
	app.window.SetTitle("Выбор параметров отношения один ко многим (Режим администратора)")
	app.window.Resize(fyne.NewSize(700, 480))
	oneColumns, _ := app.db.getColumns(table)
	tables, _ := app.db.getTables()
	data, _ := app.db.getTable(table)

	oneColumn := ""
	manyTable := ""
	manyColumn := ""
	oneValue := ""

	showTable := widget.NewButton("Показать таблицу", func() {
		app.oneToManyShowAdmin(manyTable, manyColumn, oneValue)
	})
	showTable.Disable()

	manyFieldSelect := widget.NewSelect([]string{}, func(manyColumnVal string) {
		manyColumn = manyColumnVal
		showTable.Enable()
	})
	manyFieldSelect.Disable()

	manyTablesSelect := widget.NewSelect(tables, func(manyTableVal string) {
		manyTable = manyTableVal
		manyColumns, _ := app.db.getColumns(manyTable)
		manyFieldSelect.Options = manyColumns
		manyFieldSelect.Refresh()
		manyFieldSelect.Enable()
	})
	manyTablesSelect.Disable()

	selectedLabel := widget.NewLabel("")

	oneFieldSelect := widget.NewSelect(oneColumns, func(oneColumnVal string) {
		oneColumn = oneColumnVal
		columnIdx := 0
		for i, val := range oneColumns {
			if val == oneColumn {
				columnIdx = i
			}
		}
		oneValue = fmt.Sprintf("%v", data[row][columnIdx])
		selectedLabel.Text = fmt.Sprintf(
			"Значение [%s] выбранной строки = %v",
			oneColumn, oneValue)
		selectedLabel.Refresh()
		manyTablesSelect.Enable()
	})

	app.window.SetContent(
		container.NewVBox(
			widget.NewLabel(fmt.Sprintf("Выбранная таблица: [%s]", table)),
			container.NewHBox(widget.NewLabel("Представитель связи \"один\":"), oneFieldSelect),
			selectedLabel,
			container.NewHBox(widget.NewLabel("Таблица связи \"многие\":"), manyTablesSelect),
			container.NewHBox(widget.NewLabel("Представитель связи \"многие\":"), manyFieldSelect),
			showTable))
}

func (app *DBInterface) showAdminTable(table string) {
	app.window.SetTitle(fmt.Sprintf("Таблица [%s] (Режим администратора)", table))
	data, err := app.db.getTable(table)
	if err != nil {
		fmt.Println(err)
		return
	}
	columns, err := app.db.getColumns(table)

	tableWidget := widget.NewTable(
		func() (int, int) {
			return len(data) + 1, len(columns)
		},
		func() fyne.CanvasObject {
			return widget.NewLabel("some very wide content")
		},
		func(i widget.TableCellID, o fyne.CanvasObject) {
			if i.Row == 0 {
				o.(*widget.Label).Alignment = fyne.TextAlignCenter
				o.(*widget.Label).TextStyle = fyne.TextStyle{Bold: true}
				o.(*widget.Label).SetText(fmt.Sprintf("%v", columns[i.Col]))
			} else {
				o.(*widget.Label).Alignment = fyne.TextAlignLeading
				o.(*widget.Label).TextStyle = fyne.TextStyle{Bold: false}
				o.(*widget.Label).SetText(fmt.Sprintf("%v", data[i.Row-1][i.Col]))
			}
		})

	selectedRow := -1
	tableWidget.OnSelected = func(id widget.TableCellID) {
		selectedRow = id.Row - 1
	}

	selectBtn := widget.NewButton("Выбрать", func() {
		app.selectAdminView(table)
	})

	updateBtn := widget.NewButton("Изменить", func() {
		if selectedRow == -1 {
			dialog.ShowInformation(
				"Изменение записи",
				"Не выбрана ни одна строка в таблице",
				app.window)
		} else {
			app.updateRowAdminView(table, data[selectedRow])
		}
	})

	insertBtn := widget.NewButton("Вставить", func() {
		app.insertAdminView(table)
	})

	deleteBtn := widget.NewButton("Удалить", func() {
		app.deleteAdminView(table)
	})

	oneToMany := widget.NewButton("Один ко многим", func() {
		if selectedRow == -1 {
			dialog.ShowInformation(
				"Один ко многим",
				"Не выбрана ни одна строка в таблице",
				app.window)
		} else {
			w := app.application.NewWindow("")
			w.Resize(fyne.NewSize(100, 100))
			conn := DBInterface{app.application, w, app.db}
			conn.oneToManyParamsAdmin(table, selectedRow)
			w.Show()
		}
	})

	back := widget.NewButton("Назад", func() {
		app.adminView()
	})

	app.window.SetContent(
		genMainView(
			tableWidget,
			[]fyne.CanvasObject{selectBtn, updateBtn, insertBtn, deleteBtn, oneToMany, back}))
}

func (app *DBInterface) adminView() {
	app.window.SetTitle("Режим администратора")
	tables, _ := app.db.getTables()

	var tableButtons []fyne.CanvasObject
	tableButtons = append(tableButtons, container.NewCenter(widget.NewLabel("Таблицы")))
	for _, val := range tables {
		e := val
		tableButtons = append(
			tableButtons, widget.NewButton(
				fmt.Sprintf("Показать %s", val),
				func() {
					app.showAdminTable(e)
				}))
	}

	views, _ := app.db.getViews()
	var viewsButtons []fyne.CanvasObject
	viewsButtons = append(viewsButtons, container.NewCenter(widget.NewLabel("Представления")))
	for _, val := range views {
		e := val
		viewsButtons = append(
			viewsButtons, widget.NewButton(
				fmt.Sprintf("Показать %s", val),
				func() {
					app.showAdminTable(e)
				}))
	}

	tablesScroll := container.NewVScroll(container.NewVBox(tableButtons...))
	viewsScroll := container.NewVScroll(container.NewVBox(viewsButtons...))

	dataBox := container.NewGridWithColumns(2, tablesScroll, viewsScroll)
	menuBtn := widget.NewButton("В меню", func() {
		app.menu()
	})

	app.window.SetContent(genMainView(dataBox, []fyne.CanvasObject{menuBtn}))
}

func (app *DBInterface) showUserTable(table string) {
	data, err := app.db.getTable(table)
	if err != nil {
		fmt.Println(err)
		return
	}
	columns, err := app.db.getColumns(table)

	list := widget.NewTable(
		func() (int, int) {
			return len(data), len(columns)
		},
		func() fyne.CanvasObject {
			return widget.NewLabel("wide content")
		},
		func(i widget.TableCellID, o fyne.CanvasObject) {
			o.(*widget.Label).SetText(fmt.Sprintf("%v", data[i.Row][i.Col]))
		})

	back := widget.NewButton("Назад", func() {
		app.userView()
	})

	app.window.SetContent(genMainView(list, []fyne.CanvasObject{back}))
}

func (app *DBInterface) showDataTable(data [][]string, columns []string) {
	app.window.SetTitle("Таблица")
	tableWidget := widget.NewTable(
		func() (int, int) {
			return len(data) + 1, len(columns)
		},
		func() fyne.CanvasObject {
			return widget.NewLabel("some very wide content")
		},
		func(i widget.TableCellID, o fyne.CanvasObject) {
			if i.Row == 0 {
				o.(*widget.Label).Alignment = fyne.TextAlignCenter
				o.(*widget.Label).TextStyle = fyne.TextStyle{Bold: true}
				o.(*widget.Label).SetText(fmt.Sprintf("%v", columns[i.Col]))
			} else {
				o.(*widget.Label).Alignment = fyne.TextAlignLeading
				o.(*widget.Label).TextStyle = fyne.TextStyle{Bold: false}
				o.(*widget.Label).SetText(fmt.Sprintf("%v", data[i.Row-1][i.Col]))
			}
		})

	app.window.SetContent(container.NewMax(tableWidget))
}

func (app *DBInterface) reportUserBoxes() {
	entry := widget.NewEntry()
	app.window.SetContent(
		container.NewVBox(
			widget.NewLabel("E-Mail Клиента"),
			entry,
			widget.NewButton("Построить таблицу", func() {
				query := fmt.Sprintf(
					`select B.id, B.order_id, B.warehouse_id, B.wrapping_id, B.volume, B.weight
					FROM shipping.dbo.Boxes B
					JOIN shipping.dbo.Orders O on O.id = B.order_id
					JOIN shipping.dbo.Customers C on C.id = O.customer_id
					WHERE email = N'%s'`, entry.Text)
				data, cols, _ := app.db.executeSelect(query)
				app.showDataTable(data, cols)
			})))
}

func (app *DBInterface) reportUserAmount() {
	entryEmail := widget.NewEntry()
	entryStart := widget.NewEntry()
	entryEnd := widget.NewEntry()
	app.window.SetContent(
		container.NewVBox(
			widget.NewLabel("E-Mail Клиента"),
			entryEmail,
			widget.NewLabel("Начало периода"),
			entryStart,
			widget.NewLabel("Конец периода"),
			entryEnd,
			widget.NewButton("Построить таблицу", func() {
				query := fmt.Sprintf(
					`SELECT SUM(amount) as [Размер выплат]
					FROM shipping.dbo.Payments P
					JOIN shipping.dbo.Orders O on O.id = P.order_id
					JOIN shipping.dbo.Customers C on C.id = O.customer_id
					WHERE O.date >= '%s' AND O.date <= '%s' AND email = N'%s'`,
					entryStart.Text, entryEnd.Text, entryEmail.Text)
				data, cols, _ := app.db.executeSelect(query)
				app.showDataTable(data, cols)
			})))
}

func (app *DBInterface) userView() {
	views, _ := app.db.getViews()
	var viewsButtons []fyne.CanvasObject
	viewsButtons = append(viewsButtons, container.NewCenter(widget.NewLabel("Представления")))
	for _, val := range views {
		e := val
		viewsButtons = append(
			viewsButtons, widget.NewButton(
				fmt.Sprintf("Показать %s", val),
				func() {
					app.showUserTable(e)
				}))
	}

	reportButtons := container.NewVBox(
		container.NewCenter(widget.NewLabel("Отчёты")),
		widget.NewButton("Все коробки пользователя", func() {
			w := app.application.NewWindow("")
			w.Resize(fyne.NewSize(700, 480))
			conn := DBInterface{app.application, w, app.db}
			conn.reportUserBoxes()
			w.Show()
		}),
		widget.NewButton("Размер выплат клиента за период", func() {
			w := app.application.NewWindow("")
			w.Resize(fyne.NewSize(700, 480))
			conn := DBInterface{app.application, w, app.db}
			conn.reportUserAmount()
			w.Show()
		}),
	)

	viewsScroll := container.NewVScroll(container.NewVBox(viewsButtons...))
	reportsScroll := container.NewVScroll(reportButtons)

	menuBtn := widget.NewButton("В меню", func() {
		app.menu()
	})

	dataBox := container.NewGridWithColumns(2, viewsScroll, reportsScroll)
	app.window.SetContent(genMainView(dataBox, []fyne.CanvasObject{menuBtn}))
}

func (app *DBInterface) menu() {
	app.window.SetTitle("Главное меню")
	app.window.SetContent(container.NewCenter(
		container.NewVBox(
			widget.NewButton("Режим Администратора", func() {
				app.adminView()
			}),
			widget.NewButton("Режим Пользователя", func() {
				app.userView()
			}),
		),
	))
}

func main() {
	myApp := app.New()
	window := myApp.NewWindow("Table Widget")
	window.Resize(fyne.NewSize(700, 480))

	db := SQLConnection{}
	err := db.init()
	if err != nil {
		fmt.Println(err)
		return
	}

	dbApp := DBInterface{myApp, window, db}
	dbApp.menu()
	window.ShowAndRun()
}