//	Spludlow Software
//	Copyright © Samuel P. Ludlow 2020 All Rights Reserved
//	Distributed under the terms of the GNU General Public License version 3
//	Distributed WITHOUT ANY WARRANTY; without implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE
//	https://www.spludlow.co.uk/LICENCE.TXT
//	The Spludlow logo is a registered trademark of Samuel P. Ludlow and may not be used without permission
//	v1.14

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Data.Common;
using System.Text;

public partial class DataTableEdit : System.Web.UI.UserControl
{
	[Serializable]
	public delegate void DataLoadedHandler(DataTable table);

	[Serializable]
	private class State
	{
		public string SqlConfigKey;
		public string TableName;
		public string[] PrimaryKeyColumnNames;

		public DataLoadedHandler DataLoadedHandler;

		public DataTable DataTable;
		public DataTable RecordTable;
	}

	protected void Page_Load(object sender, EventArgs e)
	{
		Spludlow.WebControls.DisableOnClick(this.ButtonEdit);
		Spludlow.WebControls.DisableOnClick(this.ButtonDelete);
		Spludlow.WebControls.DisableOnClick(this.ButtonUpdate);
		Spludlow.WebControls.DisableOnClick(this.ButtonCancel);

		if (this.IsPostBack == false)
		{
			this.PanelDisplay.Visible = false;
			this.PanelEdit.Visible = false;
		}
	}

	public void ShowData(string sqlConfigKey, string tableName)
	{
		this.ShowData(sqlConfigKey, tableName, null);
	}

	public void ShowData(string sqlConfigKey, string tableName, DataLoadedHandler dataLoadedHandler)
	{
		this.PanelDisplay.Visible = true;
		this.PanelEdit.Visible = false;

		this.GridViewData.Columns[0].Visible = true;

		Spludlow.Data.IDAL database = Spludlow.Data.DAL.Create(sqlConfigKey);

		DataSet schema = database.Schema(tableName);

		DataTable dataTable = database.Select("SELECT * FROM " + tableName);

		if (dataLoadedHandler != null)
			dataLoadedHandler(dataTable);

		string[] primaryKeys = Spludlow.Data.Schemas.PrimaryKeyColumns(schema, tableName);
		
		this.GridViewData.DataKeyNames = primaryKeys;

		this.GridViewData.DataSource = dataTable;
		this.GridViewData.DataBind();

		this.DataListRecord.DataSource = null;
		this.DataListRecord.DataBind();

		State state = new State();

		state.SqlConfigKey = sqlConfigKey;
		state.TableName = tableName;
		state.PrimaryKeyColumnNames = primaryKeys;

		state.DataLoadedHandler = dataLoadedHandler;

		this.ViewState["State"] = state;
	}

	private object[][] GetChecked()
	{
		List<object[]> keys = new List<object[]>();

		foreach (GridViewRow row in this.GridViewData.Rows)
		{
			CheckBox checkBox = (CheckBox)row.FindControl("CheckBoxRow");

			if (checkBox.Checked == true)
			{
				DataKey dataKey = this.GridViewData.DataKeys[row.RowIndex];

				object[] key = new object[dataKey.Values.Count];

				for (int index = 0; index < dataKey.Values.Count; ++index)
					key[index] = dataKey[index];

				keys.Add(key);
			}
		}

		return keys.ToArray();
	}

	protected void ButtonEdit_Click(object sender, EventArgs e)
	{
		State state = (State)this.ViewState["State"];

		if (state == null)
			throw new ApplicationException("View State missing.");

		Spludlow.Data.IDAL database = Spludlow.Data.DAL.Create(state.SqlConfigKey);

		object[][] checkedKeys = this.GetChecked();

		if (checkedKeys.Length > 1)
		{
			this.LabelError.Text = "Please select only one row to edit or none to create a new row.";
			return;
		}

		this.PanelDisplay.Visible = false;
		this.PanelEdit.Visible = true;

		StringBuilder text;

		string whereText = "0 = 1";

		if (checkedKeys.Length == 1)
		{
			object[] checkedKey = checkedKeys[0];

			if (state.PrimaryKeyColumnNames.Length != checkedKey.Length)
				throw new ApplicationException("Primary Key Length missmatch.");

			text = new StringBuilder();

			foreach (string primaryKeyColumnName in state.PrimaryKeyColumnNames)
			{
				if (text.Length > 0)
					text.Append(" AND ");

				text.Append(primaryKeyColumnName);
				text.Append(" = @");
				text.Append(primaryKeyColumnName);
			}

			whereText = text.ToString();
		}

		text = new StringBuilder("SELECT * FROM @TableName WHERE (@Where)");
		text.Replace("@TableName", state.TableName);
		text.Replace("@Where", whereText);

		DbCommand command = database.MakeCommand(text.ToString());

		if (checkedKeys.Length == 1)
		{
			object[] checkedKey = checkedKeys[0];

			for (int index = 0; index < state.PrimaryKeyColumnNames.Length; ++index)
			{
				string columnName = state.PrimaryKeyColumnNames[index];
				object data = checkedKey[index];
				Spludlow.Data.DAL.AddParameter(command, "@" + columnName, data);
			}
		}

		DataTable table = database.Select(command);

		Spludlow.Log.Warning("table", new object[] { table });

		List<DataColumn> primaryKeys = new List<DataColumn>();
		foreach (string primaryKeyColumnName in state.PrimaryKeyColumnNames)
			primaryKeys.Add(table.Columns[primaryKeyColumnName]);
		table.PrimaryKey = primaryKeys.ToArray();

		table.TableName = state.TableName;

		DataRow row;

		if (table.Rows.Count > 0)
			row = table.Rows[0];
		else
			row = table.NewRow();

		DataTable schemaTable = database.Schema(state.TableName).Tables[0];

		DataTable recordTable = new DataTable();

		recordTable.Columns.Add("ColumnName", typeof(string));
		recordTable.Columns.Add("ColumnSize", typeof(int));
		recordTable.Columns.Add("IsReadOnly", typeof(bool));
		recordTable.Columns.Add("DataTypeName", typeof(string));
		recordTable.Columns.Add("DataDisplay", typeof(string));
		recordTable.Columns.Add("TextBoxWidth", typeof(Unit));
		recordTable.Columns.Add("TextBoxHeight", typeof(Unit));
		recordTable.Columns.Add("TextBoxMode", typeof(TextBoxMode));
		recordTable.Columns.Add("ColumnInfo", typeof(string));

		recordTable.PrimaryKey = new DataColumn[] { recordTable.Columns[0] };

		foreach (DataColumn column in table.Columns)
		{
			object data = row[column];
			string dataTypeName = column.DataType.ToString();

			DataRow schemaRow = schemaTable.Rows.Find(column.ColumnName);
			if (schemaRow == null)
				throw new ApplicationException("Can not find schema row: " + column.ColumnName);

			int columnSize = (int)schemaRow["MaxLength"];

			DataRow recordRow = recordTable.NewRow();

			recordRow["ColumnName"] = (string)schemaRow["ColumnName"];
			recordRow["ColumnSize"] = columnSize;
			recordRow["IsReadOnly"] = (bool)schemaRow["AutoIncrement"];		//	Fix name in recordRow
			recordRow["DataTypeName"] = dataTypeName;

			int width = columnSize * 8;
			int height = 20;
			TextBoxMode mode = TextBoxMode.SingleLine;

			if (dataTypeName == "System.Boolean")
				width = 4 * 8;

			if (dataTypeName == "System.String")
			{
				if (columnSize == 0)
					columnSize = 4096;

				if (columnSize > 1024)
				{
					width = 128 * 8;
					mode = TextBoxMode.MultiLine;

					int lines = columnSize / 64;
					if (lines > 16)
						lines = 16;
					height = lines * 22;
				}
				else
				{
					if (columnSize < 2)
						width = 2 * 8;

					if (columnSize > 128)
						width = 128 * 8;
				}
			}

			Unit unit;

			unit = new Unit((double)width, UnitType.Pixel);
			recordRow["TextBoxWidth"] = unit;

			unit = new Unit((double)height, UnitType.Pixel);
			recordRow["TextBoxHeight"] = unit;

			recordRow["TextBoxMode"] = mode;

			recordRow["ColumnInfo"] = dataTypeName + "(" + columnSize.ToString() + ")";

			string display = "";

			switch (dataTypeName)
			{
				case "System.String":
					if (data.GetType() != typeof(System.DBNull))
					{
						display = (string)data;

						if (display.Length == 0)
							display = "\"\"";
					}
					break;

				case "System.Boolean":
					if (data.GetType() != typeof(System.DBNull))
					{
						display = ((bool)data).ToString();
					}
					break;
				case "System.UInt64":
					if (data.GetType() != typeof(System.DBNull))
						display = ((UInt64)data).ToString();
					break;
				default:
					throw new ApplicationException("Data Type not supported: " + dataTypeName);
			}

			recordRow["DataDisplay"] = display;

			recordTable.Rows.Add(recordRow);
		}

		this.DataListRecord.DataSource = recordTable;
		this.DataListRecord.DataBind();

		this.GridViewData.Columns[0].Visible = false;


		this.GridViewData.DataSource = table;
		this.GridViewData.DataBind();

		state.DataTable = table;
		state.RecordTable = recordTable;

		this.ViewState["State"] = state;

	}
	protected void ButtonDelete_Click(object sender, EventArgs e)
	{
		State state = (State)this.ViewState["State"];

		if (state == null)
			throw new ApplicationException("View State missing.");

		object[][] checkedKeys = this.GetChecked();

		if (checkedKeys.Length == 0)
		{
			this.LabelError.Text = "Please select the rows you want to delete.";
			return;
		}

		foreach (object[] checkedKey in checkedKeys)
		{
			try
			{
				this.Delete(checkedKey, state);
			}
			catch (Exception ee)
			{
				this.LabelError.Text = ee.Message;
				return;
			}
		}

		this.ShowData(state.SqlConfigKey, state.TableName, state.DataLoadedHandler);
	}

	private void Delete(object[] keyData, State state)
	{
		if (state.PrimaryKeyColumnNames.Length != keyData.Length)
			throw new ApplicationException("Primary Key Length missmatch.");

		StringBuilder text;

		text = new StringBuilder();

		foreach (string primaryKeyColumnName in state.PrimaryKeyColumnNames)
		{
			if (text.Length > 0)
				text.Append(" AND ");

			text.Append(primaryKeyColumnName);
			text.Append(" = @");
			text.Append(primaryKeyColumnName);
		}

		string whereText = text.ToString();

		text = new StringBuilder();

		text = new StringBuilder("DELETE FROM @TableName WHERE (@Where)");
		text.Replace("@TableName", state.TableName);
		text.Replace("@Where", whereText);

		Spludlow.Data.IDAL database = Spludlow.Data.DAL.Create(state.SqlConfigKey);

		DbCommand command = database.MakeCommand(text.ToString());
		
		for (int index = 0; index < state.PrimaryKeyColumnNames.Length; ++index)
		{
			string columnName = state.PrimaryKeyColumnNames[index];
			object data = keyData[index];
			Spludlow.Data.DAL.AddParameter(command, "@" + columnName, data);
		}

		database.ExecuteNonQuery(command);
	}

	protected void ButtonUpdate_Click(object sender, EventArgs e)
	{
		State state = (State)this.ViewState["State"];

		if (state == null)
			throw new ApplicationException("View State missing.");

		Spludlow.Data.IDAL database = Spludlow.Data.DAL.Create(state.SqlConfigKey);	

		DataRow row;

		if (state.DataTable.Rows.Count == 0)
			row = state.DataTable.NewRow();
		else
			row = state.DataTable.Rows[0];

		foreach (DataListItem item in this.DataListRecord.Items)
		{
			string columnName = (string)this.DataListRecord.DataKeys[item.ItemIndex];
			DataRow recordRow = state.RecordTable.Rows.Find(columnName);

			if (recordRow == null)
				throw new ApplicationException("Record Row not found: " + columnName);

			string dataTypeName = (string)recordRow["DataTypeName"];

			TextBox textBox = (TextBox)item.FindControl("TextBoxDataDisplay");

			if (textBox == null)
				throw new ApplicationException("TextBox not found.");

			string text = textBox.Text.Trim();

			object data;

			try
			{
				switch (dataTypeName)
				{
					case "System.String":
						if (text.Length == 0)
						{
							data = System.DBNull.Value;
						}
						else
						{
							if (text == "\"\"")
								data = "";
							else
								data = text;
						}
						break;

					case "System.Boolean":
						if (text.Length == 0)
							data = System.DBNull.Value;
						else
							data = Boolean.Parse(text);
						break;

					default:
						throw new ApplicationException("Data Type not supported: " + dataTypeName);
				}
			}
			catch (Exception ee)
			{
				this.LabelError.Text = "Parse\t" + columnName + ":\t" + ee.Message;
				return;
			}

			row[columnName] = data;
		}

		try
		{
			database.InsertOrUpdate(row);
		}
		catch (Exception ee)
		{
			this.LabelError.Text = "Database Error: " + ee.Message;
			return;
		}

		this.ShowData(state.SqlConfigKey, state.TableName, state.DataLoadedHandler);

	}
	protected void ButtonCancel_Click(object sender, EventArgs e)
	{
		State state = (State)this.ViewState["State"];

		if (state == null)
			throw new ApplicationException("View State missing.");

		this.ShowData(state.SqlConfigKey, state.TableName, state.DataLoadedHandler);
	}
}