using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;

namespace SCAN.lib
{
    public class DbTable
    {
        protected LogWriter logger = LogWriter.getInstance();
        private string TABLE;
        private Dictionary<string, string> COLUMNS;

        public virtual string __TABLE { get { return TABLE; } set { TABLE = value; } }
        public virtual Dictionary<string, string> __COLUMNS { get { return COLUMNS; } set { COLUMNS = value; } }


        public DbTable()
        {
        }

        public DbTable(string[] row)
        {
            string[] keyArray = new string[__COLUMNS.Keys.Count];
            __COLUMNS.Keys.CopyTo(keyArray, 0);
            for (int i=0; i<keyArray.Length; i++)
            {
                __COLUMNS[keyArray[i]] = row[i];
            }
        }

        public string toCsvFormat(List<string[]> rows)
        {
            StringBuilder stringBuilder = new StringBuilder();
            foreach (Object[] row in rows)
            {
                int i = 0;
                foreach (Object obj in row)
                {
                    string val = "\"" + obj.ToString().Trim() + "\"";
                    if (obj.GetType().ToString().Equals("System.DateTime"))
                    {
                        val = String.Format(@"""{0}""", ((DateTime)obj).ToString("yyMMdd HHmmss"));
                    }
                    stringBuilder.Append((i++ > 0 ? "," : "") + val);
                }
                stringBuilder.AppendLine();
            }

            return stringBuilder.ToString();
        }

        public Dictionary<string, string> bindCsvData(List<string> csv, Dictionary<string, string> cols)
        {
            int i = 0;
            if (cols == null)
            {
                cols = new Dictionary<string, string>(__COLUMNS);
            }


            try
            {
                string[] keyArray = new string[cols.Keys.Count];
                cols.Keys.CopyTo(keyArray, 0);
                foreach (var s in keyArray)
                {
                    if (i >= csv.Count)
                        break;
                    cols[s] = csv[i++];
                }
            }
            catch (Exception e)
            {
                throw;
            }
            return cols;
        }

        protected string toQsParams(Dictionary<string, string> cols)
        {
            string qstr = "";
            foreach (KeyValuePair<string, string> col in cols)
            {
                qstr += "?,";
            }
            return qstr.Length > 0 ? qstr.Substring(0, qstr.Length - 1) : "";
        }

        protected string getColumns(Dictionary<string, string> cols)
        {
            string qstr = "";
            foreach (KeyValuePair<string, string> col in cols)
            {
                qstr += col.Key + ",";
            }
            return qstr.Length > 0 ? qstr.Substring(0, qstr.Length - 1) : "";
        }

        protected string getValues(Dictionary<string, string> cols, string delimiter)
        {
            int i = 0;
            string qstr = "";
            foreach (KeyValuePair<string, string> col in cols)
            {
                qstr += (i++ > 0 ? delimiter : "") + "\"" + col.Value.Trim() + "\"";
            }
            return qstr;
        }

        protected string toQueryString(Dictionary<string, string> cols, string delimiter)
        {
            int i = 0;
            string qstr = "";
            foreach (KeyValuePair<string, string> col in cols)
            {
                qstr += (i++>0 ? delimiter : "") + col.Key + "=\"" + col.Value.Trim() + "\"";
            }
            return qstr;
        }

        /// <summary>This method inserts row to db file.
        /// <example>For example:
        /// <code>
        ///    ret = add(Dictionary<string, string> cols);
        /// </code>
        /// return 1 if OK, 0 or -1 if ERROR
        /// </example>
        /// </summary>
        public int add(Dictionary<string, string> cols)
        {
            int id = -1;
            try
            {
                string sql = @"INSERT INTO {0}
                    ({1}) VALUES ({2});";
                sql = String.Format(sql, __TABLE, getColumns(cols), getValues(cols, ","));

			    logger.Log("SQL: " + sql);

                id = SqliteDb.execute(sql) == Bt.LibDef.BT_OK ? 1 : 0;
                
            }
            catch (Exception e) {
                logger.Log(e.Message);
                throw;
            }

            return id;
        }

        public List<string[]> executeQuery(string sql)
        {
            List<string[]> rows = new List<string[]>();

            logger.Log("SQL: " + sql);

            SqliteDb.query(sql, ref rows);

            return rows;
        }


        /**
         * Inquiry data with conditions
         *
         * @return inquire rows
         */
        public List<string[]> get(Dictionary<string, string> cols, 
            string where, string limit, string orderby, string ordertype)
        {
            if (ordertype == null) ordertype = "Asc";

            List<string[]> rows = new List<string[]>();
		    string sql = "SELECT " +
            (limit != null ? " TOP " + limit + " " : "") +
    		  getColumns(cols) +
                " FROM " + __TABLE +
               (where != null ? " WHERE " + where : "") +
               (orderby != null ? " ORDER BY " + orderby + " " + ordertype : "");


		    logger.Log("SQL: " + sql);

            SqliteDb.query(sql, ref rows);

            return rows;
        }

        public int set(Dictionary<string, string> cols, string where)
        {
            if (where == null) where = "1=1";

            // fix remove comma from numeric value
            string[] keyArray = new string[cols.Count];
            cols.Keys.CopyTo(keyArray, 0);
            foreach (string key in keyArray)
            {
                string digitPattern = @"^[\d,\.]+$";
                Match m = Regex.Match(cols[key], digitPattern, RegexOptions.IgnoreCase);
                if (m.Success)
                    cols[key] = cols[key].Replace(",", "");
            }

            string sql = "UPDATE " + __TABLE +
                " SET " + toQueryString(cols, ",") +
            " WHERE " + where;

            logger.Log("SQL: " + sql);

            return SqliteDb.execute(sql);
        }

        public int remove(string where)
        {

            if (where == null)
            {
                Console.WriteLine("Removing without where condition is not allowed!");
                return -1;
            }

            string sql = "DELETE " +
                " FROM " + __TABLE +
               " WHERE " + where;

            logger.Log("SQL: " + sql);

            return SqliteDb.execute(sql);
        }

        /**
         * Inquiry all data
         *
         * @return fetched rows
         */
        public List<string[]> getAll()
        {
            return get(__COLUMNS, null, null, null, null);
        }

        public virtual string getAllCsv() { return _getAllCsv();  }

        public string _getAllCsv()
        {
            List<string[]> rows = get(__COLUMNS, null, null, null, null);
            return toCsvFormat(rows);
        }
    }
}

