using MySqlConnector;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using static System.Net.Mime.MediaTypeNames;

namespace CapaDeDatos
{
    public class CapaDeDatos
    {
        private DataBaseClase _db { get; set; }
        public bool Test { get; set; }
        public bool Remote { get; set; }
        public string commandText { get; set; }
        public Dictionary<string,object> dataCommand { get; set; }

        public DataTable GetDataTableQuery() {
            DataTable dt = new DataTable();
            MySqlConnection conn = new MySqlConnection();
            try
            {
                if (commandText == "CumplimientoHojaCalidad") {
                    conn = new MySqlConnection("server=38.124.220.170;userid=gruasgmt_RICARDO;database=gruasgmt_system_gmt;password=GruasGMT.2024;port=6303;");
                    conn.Open();
                    var command = new MySqlCommand("select entry_id,not ISNULL(doc_documento) documento from sisadmin_sheetquality ss " +
                        $"WHERE entry_id REGEXP '{dataCommand["parametros"].ToString()}'", conn);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(command);
                    adapter.Fill(dt);
                    conn.Close();
                }
                if (commandText == "DescargarHojaCalidad")
                {
                    conn = new MySqlConnection("server=38.124.220.170;userid=gruasgmt_RICARDO;database=gruasgmt_system_gmt;password=GruasGMT.2024;port=6303;");
                    conn.Open();
                    var command = new MySqlCommand("select doc_documento from sisadmin_sheetquality ss " +
                        $"WHERE entry_id = '{dataCommand["iHC"].ToString()}'", conn);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(command);
                    adapter.Fill(dt);
                    conn.Close();
                }
            }
            catch (MySqlException ex)
            {
                var errorMySQL = ex.Message;
                dt.Columns.Add(new DataColumn("result"));
                dt.Rows.Add(errorMySQL.ToString());               

            }
            finally {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return dt;
        }

        public DataTable GetDataTableCommands() {
            DataTable dt = new DataTable();
            MySqlConnection conn = new MySqlConnection();
            _db = new DataBaseClase();

            try
            {
                var test1 = Test;
                var remoto1 = Remote;
                conn = (Test && Remote) ? _db.RemoteTestConn() : (!Test && Remote) ? _db.RemoteProdConn() : (Test && !Remote) ? _db.LocalTestConn() : _db.LocalProdConn();
                try
                {
                    conn.Open();
                }
                catch
                {
                    if (commandText == "allowLogin")
                    {
                        conn = _db.RemoteProdConn();
                        conn.Open();
                    }
                }

                MySqlCommand command = new MySqlCommand(commandText, conn);
                foreach (var clave in dataCommand)
                {
                    command.Parameters.Add(new MySqlParameter(clave.Key, clave.Value));
                }
                command.CommandType = CommandType.StoredProcedure;
                MySqlDataAdapter adapter = new MySqlDataAdapter(command);
                adapter.Fill(dt);
                conn.Close();

            }
            catch (MySqlException ex)
            {
                var errorMySQL = ex.Message;
                dt.Columns.Add(new DataColumn("result"));
                dt.Rows.Add(errorMySQL);
            }
            finally {
                if (conn.State == ConnectionState.Open)
                    conn.Close();

                _db = null;
            }

            return dt;
        }
    }
}