using System;
using System.Data;
using System.Linq;
using System.Data.SQLite;
using System.Windows.Forms;

namespace WilksMergeModule
{
    public struct SettingDbKey
    {
        public int SettingCategoryId { get; }
        public string CategoryName { get; }
        public int SettingId { get; }
        public string SettingName { get; }
        public string SettingValue { get; }

        public SettingDbKey(int settingCategoryId, string categoryName, int settingId, string settingName, string settingValue)
        {
            SettingCategoryId = settingCategoryId;
            CategoryName = categoryName;
            SettingId = settingId;
            SettingName = settingName;
            SettingValue = settingValue;
        }
    }

    public partial class FormSettings : Form
    {
        public DataTable Settings { get; set; }

        public FormSettings()
        {
            InitializeComponent();
        }

        private void formSettings_Load(object sender, EventArgs e)
        {

            LoadSettings();
        }

        private void LoadSettings()
        {
            SQLiteConnection connection = new SQLiteConnection(
                String.Format(
                    "Data Source={0}\\{1};Version=3;",
                    new object[]
                    {
                        AppDomain.CurrentDomain.BaseDirectory,
                        "WilksMergeModule.db"
                    }
                )
            );
            connection.Open();
            DataSet dataSetSettings = new DataSet("Settings");
            SQLiteDataAdapter dataAdapterSettings = new SQLiteDataAdapter("SELECT SC.Id AS [SettingCategoryId], SC.Name AS [CategoryName], S.Id AS [SettingId], S.Name AS [SettingName], S.Value AS [SettingValue] FROM Setting S INNER JOIN SettingCategory SC ON S.SettingCategoryId = SC.Id;", connection);
            dataAdapterSettings.Fill(dataSetSettings);
            dataAdapterSettings.Dispose();
            connection.Close();
            connection.Dispose();
            Settings = dataSetSettings.Tables[0];
            dataSetSettings.Dispose();

            textboxLaserficeSettingsServer.Tag = (from s in Settings.AsEnumerable() where s.Field<String>("CategoryName") == "Laserfiche" && s.Field<String>("SettingName") == "Server" select new SettingDbKey(Int32.Parse(s["SettingCategoryId"].ToString()), s["CategoryName"].ToString(), Int32.Parse(s["SettingId"].ToString()), s["SettingName"].ToString(), s["SettingValue"].ToString())).SingleOrDefault();
            textboxLaserficeSettingsRepository.Tag = (from s in Settings.AsEnumerable() where s.Field<String>("CategoryName") == "Laserfiche" && s.Field<String>("SettingName") == "Repository" select new SettingDbKey(Int32.Parse(s["SettingCategoryId"].ToString()), s["CategoryName"].ToString(), Int32.Parse(s["SettingId"].ToString()), s["SettingName"].ToString(), s["SettingValue"].ToString())).SingleOrDefault();
            textboxLaserficeSettingsUsername.Tag = (from s in Settings.AsEnumerable() where s.Field<String>("CategoryName") == "Laserfiche" && s.Field<String>("SettingName") == "User Name" select new SettingDbKey(Int32.Parse(s["SettingCategoryId"].ToString()), s["CategoryName"].ToString(), Int32.Parse(s["SettingId"].ToString()), s["SettingName"].ToString(), s["SettingValue"].ToString())).SingleOrDefault();
            textboxLaserficeSettingsPassword.Tag = (from s in Settings.AsEnumerable() where s.Field<String>("CategoryName") == "Laserfiche" && s.Field<String>("SettingName") == "Password" select new SettingDbKey(Int32.Parse(s["SettingCategoryId"].ToString()), s["CategoryName"].ToString(), Int32.Parse(s["SettingId"].ToString()), s["SettingName"].ToString(), s["SettingValue"].ToString())).SingleOrDefault();
            textboxLaserficeSettingsSearchCommand.Tag = (from s in Settings.AsEnumerable() where s.Field<String>("CategoryName") == "Laserfiche" && s.Field<String>("SettingName") == "Search Command" select new SettingDbKey(Int32.Parse(s["SettingCategoryId"].ToString()), s["CategoryName"].ToString(), Int32.Parse(s["SettingId"].ToString()), s["SettingName"].ToString(), s["SettingValue"].ToString())).SingleOrDefault();
            textboxLaserficeSettingsInvoiceCommand.Tag = (from s in Settings.AsEnumerable() where s.Field<String>("CategoryName") == "Laserfiche" && s.Field<String>("SettingName") == "Invoice Command" select new SettingDbKey(Int32.Parse(s["SettingCategoryId"].ToString()), s["CategoryName"].ToString(), Int32.Parse(s["SettingId"].ToString()), s["SettingName"].ToString(), s["SettingValue"].ToString())).SingleOrDefault();

            textboxSynergySettingsConnectionString.Tag = (from s in Settings.AsEnumerable() where s.Field<String>("CategoryName") == "Synergy" && s.Field<String>("SettingName") == "Connection String" select new SettingDbKey(Int32.Parse(s["SettingCategoryId"].ToString()), s["CategoryName"].ToString(), Int32.Parse(s["SettingId"].ToString()), s["SettingName"].ToString(), s["SettingValue"].ToString())).SingleOrDefault();
            textboxSynergySettingsConnectionQuery.Tag = (from s in Settings.AsEnumerable() where s.Field<String>("CategoryName") == "Synergy" && s.Field<String>("SettingName") == "Query" select new SettingDbKey(Int32.Parse(s["SettingCategoryId"].ToString()), s["CategoryName"].ToString(), Int32.Parse(s["SettingId"].ToString()), s["SettingName"].ToString(), s["SettingValue"].ToString())).SingleOrDefault();


            textboxLaserficeSettingsServer.Text = ((SettingDbKey)textboxLaserficeSettingsServer.Tag).SettingValue;
            textboxLaserficeSettingsRepository.Text = ((SettingDbKey)textboxLaserficeSettingsRepository.Tag).SettingValue;
            textboxLaserficeSettingsUsername.Text = ((SettingDbKey)textboxLaserficeSettingsUsername.Tag).SettingValue;
            textboxLaserficeSettingsPassword.Text = ((SettingDbKey)textboxLaserficeSettingsPassword.Tag).SettingValue;
            textboxLaserficeSettingsSearchCommand.Text = ((SettingDbKey)textboxLaserficeSettingsSearchCommand.Tag).SettingValue;
            textboxLaserficeSettingsInvoiceCommand.Text = ((SettingDbKey)textboxLaserficeSettingsInvoiceCommand.Tag).SettingValue;

            textboxSynergySettingsConnectionString.Text = ((SettingDbKey)textboxSynergySettingsConnectionString.Tag).SettingValue;
            textboxSynergySettingsConnectionQuery.Text = ((SettingDbKey)textboxSynergySettingsConnectionQuery.Tag).SettingValue;
        }

        private void buttonCancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void buttonSave_Click(object sender, EventArgs e)
        {
            SQLiteConnection connection = new SQLiteConnection(
                String.Format(
                    "Data Source={0}\\{1};Version=3;",
                    new object[]
                    {
                        AppDomain.CurrentDomain.BaseDirectory,
                        "WilksMergeModule.db"
                    }
                )
            );
            connection.Open();
            SQLiteCommand command = new SQLiteCommand("Update Setting SET Value = @Value WHERE Id = @Id AND Name = @Name AND SettingCategoryId = @SettingCategoryId", connection);
            command.CommandType = CommandType.Text;
            foreach (Control control in this.Controls)
            {
                if (control.GetType() == typeof(TextBox))
                {
                    if (control.Tag.GetType() == typeof(SettingDbKey))
                    {
                        SettingDbKey settingsDbKey = (SettingDbKey)control.Tag;
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@Value", ((TextBox)control).Text.Trim());
                        command.Parameters.AddWithValue("@Id", settingsDbKey.SettingId);
                        command.Parameters.AddWithValue("@Name", settingsDbKey.SettingName);
                        command.Parameters.AddWithValue("@SettingCategoryId", settingsDbKey.SettingCategoryId);
                        command.ExecuteNonQuery();
                    }
                }
            }
            command.Dispose();
            connection.Close();
            connection.Dispose();
            LoadSettings();
        }
    }
}