// Copyright (c) 2017 schick Informatik
// Description: Alle Lookup-Tabellen für das Display statisch aus der SQLite Datenbank initialisieren
//

using System.Data;
using System.Data.SQLite;
using System.Collections.Generic;

using bsmd.database;
using ENI2.Locode;

namespace ENI2
{
    internal static class LocalizedLookup
    {
        private static readonly SQLiteConnection _con;
        private const string _locode_DB_NAME = "db.sqlite";
        private static Dictionary<string, string> _nationalities = null;

        static LocalizedLookup()
        {
            _con = new SQLiteConnection(string.Format("data source={0}; Version=3;", _locode_DB_NAME));
            _con.Open();
        }

        public static Dictionary<int, string> getLADGCargoHandlingStrings(string languageCode)
        {
            Dictionary<int, string> result = new Dictionary<int, string>();
            string query = string.Format("SELECT key, text FROM LADG_CargoHandlingCodes WHERE langKey = '{0}'", languageCode);
                
            SQLiteCommand cmd = new SQLiteCommand(query, _con);
            IDataReader reader = cmd.ExecuteReader();
            int key;
            string text;
            while (reader.Read())
            {
                key = reader.GetInt32(0);
                text = reader.GetString(1);
                result[key] = text;
            }
            reader.Close();
            return result;
        }

        public static Dictionary<string, string> getPortAreasForLocode(string locode)
        {
            Dictionary<string, string> result = new Dictionary<string, string>();
            string query = string.Format("SELECT Code, PortArea FROM INFO_PortArea WHERE Locode = '{0}' ORDER BY PortArea", locode);
            SQLiteCommand cmd = new SQLiteCommand(query, _con);
            IDataReader reader = cmd.ExecuteReader();            
            while(reader.Read())
            {
                string code = null;
                string portarea = null;
                if (!reader.IsDBNull(0)) code = reader.GetString(0);
                if (!reader.IsDBNull(1)) portarea = reader.GetString(1);
                if((code != null) && (portarea != null))
                {
                    result[code] = string.Format("{0}-{1}", code, portarea);
                }
            }
            reader.Close();
            return result;               
        }

        public static bool PortAreaExists(string locode, string portArea)
        {
            if (locode.IsNullOrEmpty() || portArea.IsNullOrEmpty()) return false;
            string query = string.Format("SELECT COUNT(*) FROM INFO_PortArea WHERE Locode = '{0}' AND Code = '{1}'", locode, portArea);
            SQLiteCommand cmd = new SQLiteCommand(query, _con);
            long numResults = (long) cmd.ExecuteScalar();
            return numResults > 0;
        }

        public static bool CheckNationality(string nationality)
        {
            if (nationality.IsNullOrEmpty()) return false;
            if (_nationalities == null) _nationalities = getNationalities();
            return _nationalities.ContainsKey(nationality);
        }

        public static Dictionary<string, string> getNationalities()
        {
            Dictionary<string, string> result = new Dictionary<string, string>();
            string query = string.Format("SELECT Code, Name FROM Nationality ORDER BY Code");
            SQLiteCommand cmd = new SQLiteCommand(query, _con);
            IDataReader reader = cmd.ExecuteReader();
            while(reader.Read())
            {
                string code = null;
                string name = null;
                if (!reader.IsDBNull(0)) code = reader.GetString(0);
                if (!reader.IsDBNull(1)) name = reader.GetString(1);
                if ((code != null) && (name != null))
                    result[code] = string.Format("{0} {1}", code, name);
            }
            reader.Close();
            return result;            
        }

        public static Dictionary<string, string> getVesselTypes()
        {
            Dictionary<string, string> result = new Dictionary<string, string>();
            string query = string.Format("SELECT Code, Name FROM VesselType ORDER BY Code");
            SQLiteCommand cmd = new SQLiteCommand(query, _con);
            IDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                string code = null;
                string name = null;
                if (!reader.IsDBNull(0)) code = reader.GetString(0);
                if (!reader.IsDBNull(1)) name = reader.GetString(1);
                if ((code != null) && (name != null))
                    result[code] = string.Format("{0} {1}", code, name);
            }
            reader.Close();
            return result;
        }

        public static Dictionary<string, string> getPackageTypes()
        {
            Dictionary<string, string> result = new Dictionary<string, string>();
            string query = string.Format("SELECT Code, Text FROM PackageTypes ORDER BY Code");
            SQLiteCommand cmd = new SQLiteCommand(query, _con);
            IDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                string code = null;
                string name = null;
                if (!reader.IsDBNull(0)) code = reader.GetString(0);
                if (!reader.IsDBNull(1)) name = reader.GetString(1);
                if ((code != null) && (name != null))
                    result[code] = string.Format("{0} {1}", code, name);
            }
            reader.Close();
            return result;
        }

        public static string GetPortAreaFromCode(string code)
        {
            string result = null;
            string query = string.Format("SELECT PortArea from INFO_PortArea WHERE Code = '{0}'", code);
            SQLiteCommand cmd = new SQLiteCommand(query, _con);
            IDataReader reader = cmd.ExecuteReader();
            if(reader.Read())
            {
                if (!reader.IsDBNull(0)) result = reader.GetString(0);
            }
            reader.Close();
            return result;
        }

        public static Dictionary<string, string> getTransportModes()
        {
            Dictionary<string, string> result = new Dictionary<string, string>();
            string query = string.Format("SELECT Code, Name FROM TransportMode ORDER BY Code");
            SQLiteCommand cmd = new SQLiteCommand(query, _con);
            IDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                string code = null;
                string name = null;
                if (!reader.IsDBNull(0)) code = reader.GetString(0);
                if (!reader.IsDBNull(1)) name = reader.GetString(1);
                if ((code != null) && (name != null))
                    result[code] = string.Format("{0} {1}", code, name);
            }
            reader.Close();
            return result;
        }

        public static Dictionary<int, string> getLACodes()
        {
            Dictionary<int, string> result = new Dictionary<int, string>();
            string query = string.Format("SELECT Code, Description FROM LATypes ORDER BY Code");
            SQLiteCommand cmd = new SQLiteCommand(query, _con);
            IDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                int? code = null;
                string name = null;
                if (!reader.IsDBNull(0)) code = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) name = reader.GetString(1);
                if ((code != null) && (name != null))
                    result[(int) code] = string.Format("{0} {1}", code, name);
            }
            reader.Close();
            return result;
        }

        public static Dictionary<string, string> getCargoCodesNST()
        {
            Dictionary<string, string> result = new Dictionary<string, string>();
            string query = string.Format("SELECT Code, Description FROM CargoTypesNST ORDER BY Code");
            SQLiteCommand cmd = new SQLiteCommand(query, _con);
            IDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                string code = null;
                string description = null;
                if (!reader.IsDBNull(0)) code = reader.GetString(0);
                if (!reader.IsDBNull(1)) description = reader.GetString(1);

                if (code?.Length == 1)
                    code = string.Format("0{0}", code); // bei einstelligen Werten 0 voranstellen

                if ((code != null) && (description != null))
                    result[code] = string.Format("{0} {1}", code, description);
            }
            reader.Close();
            return result;
        }

        public static List<KeyValuePair<string, string>> getCargoCodesNST3()
        {
            List<KeyValuePair<string, string>> result = new List<KeyValuePair<string, string>>();

            string query = string.Format("SELECT Code, Description, codeGrp3 FROM CargoTypesNST3 ORDER BY Code");
            SQLiteCommand cmd = new SQLiteCommand(query, _con);
            IDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                string code = null;
                string description = null;
                string codeGrp3 = null;

                if (!reader.IsDBNull(0)) code = reader.GetString(0);
                if (!reader.IsDBNull(1)) description = reader.GetString(1);
                if (!reader.IsDBNull(2)) codeGrp3 = reader.GetString(2);

                if ((code != null) && (description != null) && (codeGrp3 != null))
                {
                    KeyValuePair<string, string> kvp = new KeyValuePair<string, string>(codeGrp3, string.Format("{0} {1} {2}", code, description, codeGrp3));
                    result.Add(kvp);
                }
            }
            reader.Close();
            return result;            
        }        

        public static List<LocodeDB.LocodeEntry> SSNAllLocodesForCityNameAsEntries(string city)
        {
            List<LocodeDB.LocodeEntry> results = new List<LocodeDB.LocodeEntry>();

            const string query = "SELECT LocationCode, LocationName FROM SSN_LOCODES WHERE LocationName like $PAR";
            SQLiteCommand cmd = new SQLiteCommand(query, _con);
            cmd.Parameters.AddWithValue("$PAR", city);
            IDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                if (!reader.IsDBNull(0) && !reader.IsDBNull(1))
                {
                    LocodeDB.LocodeEntry entry = new LocodeDB.LocodeEntry();                    
                    entry.Locode = reader.GetString(0);                    
                    entry.Name = reader.GetString(1);
                    results.Add(entry);
                }
            }
            reader.Close();
            return results;
        }

    }
}