using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;
using System.Collections.Specialized;
using System.Text;
using NVC = System.Collections.Specialized.NameValueCollection;
using HT = System.Collections.Hashtable;
using Library;

namespace Library {
    public class SimpleDB {
        public static string MakeWhere(HT condition) {
            return MakeWhere(ToNVC(condition));
        }

        public static string MakeWhere(NVC condition) {
            StringBuilder sb = new StringBuilder();
            string oper = "";
            string column = "";
            foreach (string col in condition.Keys) {
                column = col;
                if (condition[column].Trim().Length == 0) continue;
                oper = "";
                if (column.Length >= 5) {
                    oper = column.Trim().Substring(column.Trim().Length - 5);
                }
                if (sb.Length > 0) {
                    if (column.Length >= 3 && column.Trim().Substring(0, 3).ToUpper() == "OR ") {
                        sb.Append(" OR ");
                        column = column.Substring(3);
                    } else {
                        sb.Append(" AND ");
                    }
                }
                if (oper == " LIKE") {
                    sb.Append(column.Substring(0, column.Length - 5));
                    sb.Append(" LIKE ");
                    sb.Append("'%" + EscapeString((string)condition[column]) + "%'");
                } else if (oper == " EXPR") {
                    sb.Append(column.Substring(0, column.Length - 5));
                    sb.Append(" = ");
                    sb.Append((string)condition[column]);
                } else if (oper == "eated") {
                    sb.Append(column.Substring(0, column.Length - 7));
                    sb.Append((string)condition[column]);
                } else {
                    if (column.Length >= 3) {
                        oper = column.Trim().Substring(column.Trim().Length - 3);
                    }
                    if (oper == " IN") {
                        sb.Append(column);
                        sb.Append("(" + EscapeString((string)condition[column]) + ")");
                    } else {
                        sb.Append(column);
                        oper = column.Trim().Substring(column.Trim().Length - 1);
                        if (oper != "=" && oper != ">" && oper != "<") {
                            sb.Append(" = ");
                        }
                        sb.Append("'" + EscapeString((string)condition[column]) + "'");
                    }
                }
            }

            return sb.ToString();
        }
        public string MakeSet(NVC columns) {
            StringBuilder sb = new StringBuilder();
            foreach (string column in columns.Keys) {
                if (sb.Length > 0) sb.Append(" , ");
                sb.Append(column);
                sb.Append(" = ");
                sb.Append("'" + EscapeString((string)columns[column]) + "'");
            }
            return sb.ToString();
        }

        public static string MakeFind(HT options) {
            StringBuilder sql = new StringBuilder();
            string fields = "*";
            string table = "";
            string conditions = "";
            string order = "";
            string group = "";
            string having = "";
            string leftjoin = "";
            int limit = 0;
            int page = 0;

            if (options["conditions"] is string) {
                conditions = (string)options["conditions"];
            } else if (options["conditions"] is NVC) {
                conditions = MakeWhere((NVC)options["conditions"]);
            } else if (options["conditions"] is HT) {
                conditions = MakeWhere((HT)options["conditions"]);
            }
            if (options["fields"] is string) {
                fields = (string)options["fields"];
            } else if (options["fields"] is string[]) {
                fields = string.Join(",", (string[])options["fields"]);
            }
            if (options["table"] is string) {
                table = (string)options["table"];
            }
            if (options["limit"] is string) {
                limit = int.Parse((string)options["limit"]);
            } else if (options["limit"] is int) {
                limit = (int)options["limit"];
            }
            if (options["page"] is string) {
                page = int.Parse((string)options["page"]);
            } else if (options["page"] is int) {
                page = (int)options["page"];
            }
            if (options["order"] is string) {
                order = (string)options["order"];
            }
            if (options["group"] is string) {
                group = (string)options["group"];
            }
            if (options["having"] is string) {
                having = (string)options["having"];
            }
            if (options["leftjoin"] is string) {
                leftjoin = (string)options["leftjoin"];
            } else if (options["leftjoin"] is HT) {
                HT leftjoins = (HT)options["leftjoin"];
                StringBuilder sb = new StringBuilder();
                foreach (string key in leftjoins.Keys) {
                    sb.Append(" LEFT JOIN ");
                    sb.Append(key);
                    sb.Append(" ON ");
                    sb.Append(leftjoins[key]);
                }
                leftjoin = sb.ToString();
            } else if (options["leftjoin"] is NVC) {
                NVC leftjoins = (NVC)options["leftjoin"];
                StringBuilder sb = new StringBuilder();
                foreach (string key in leftjoins.AllKeys) {
                    sb.Append(" LEFT JOIN ");
                    sb.Append(key);
                    sb.Append(" ON ");
                    sb.Append(leftjoins[key]);
                }
                leftjoin = sb.ToString();
            }
            if (limit == 0 || (limit > 0 && page <= 1)) {
                sql.Append("SELECT ");
                if (limit > 0) {
                    sql.Append(" TOP ");
                    sql.Append(limit);
                    sql.Append(" ");
                }
                sql.Append(fields);
                sql.Append(" FROM ");
                sql.Append(table);
                sql.Append(leftjoin);
                if (conditions.Length > 0) {
                    sql.Append(" WHERE ");
                    sql.Append(conditions);
                }
                if (group.Length > 0) {
                    sql.Append(" GROUP BY ");
                    sql.Append(group);
                }
                if (having.Length > 0) {
                    sql.Append(" HAVING ");
                    sql.Append(having);
                }
                if (order.Length > 0) {
                    sql.Append(" ORDER BY ");
                    sql.Append(order);
                }
            } else {
                sql.Append("SELECT * FROM (");
                sql.Append("SELECT ");
                sql.AppendFormat(" ROW_NUMBER() OVER (ORDER BY {0}) as _ROWNUM_, ", order);
                sql.Append(fields);
                sql.Append(" FROM ");
                sql.Append(table);
                sql.Append(leftjoin);
                if (conditions.Length > 0) {
                    sql.Append(" WHERE ");
                    sql.Append(conditions);
                }
                if (group.Length > 0) {
                    sql.Append(" GROUP BY ");
                    sql.Append(group);
                }
                if (having.Length > 0) {
                    sql.Append(" HAVING ");
                    sql.Append(having);
                }
                sql.AppendFormat(") AS _ROWNUM_TABLE_ WHERE _ROWNUM_ BETWEEN {0} AND {1}", (page - 1) * limit + 1, (page - 1) * limit + limit);
            }
            return sql.ToString();
        }

        public string Delete(string table, string id) { return Delete(table, id, "Id"); }
        public string Delete(string table, string id, string primaryKey) {
            string sql = string.Format("DELETE FROM [{0}] WHERE {1} = {2}", table, id, primaryKey);
            return sql;
        }
        public string Delete(string table, NVC conditions) {
            string where = MakeWhere(conditions);
            string sql = string.Format("DELETE FROM [{0}] WHERE {1}", table, where);

            return sql;
        }

        public string Insert(string table, NVC fields) { return Insert(table, ToHT(fields)); }
        public string Insert(string table, HT fields) {
            string insertSql = InsertQuery(table, fields);
            return insertSql;
        }

        public string InsertQuery(string table, NVC fields) { return InsertQuery(table, ToHT(fields)); }
        public string InsertQuery(string table, HT fields) {
            StringBuilder sbVal = new StringBuilder();
            StringBuilder sbCol = new StringBuilder();
            foreach (string key in fields.Keys) {
                if (fields[key] == null) continue;
                if (sbCol.Length > 0) sbCol.Append(",");
                sbCol.Append(key);
                if (sbVal.Length > 0) sbVal.Append(",");
                string val = EscapeString((string)fields[key]);
                if (val.ToLower() == "getdate()") {
                    sbVal.Append(val);
                } else {
                    sbVal.Append("'");
                    sbVal.Append(val);
                    sbVal.Append("'");
                }
            }
            string columns = sbCol.ToString();
            string values = sbVal.ToString();
            string insertSql = $"INSERT INTO [{table}]({columns}) VALUES({values})";
            return insertSql;
        }

        public string Update(string table, NVC fields, string where) { return Update(table, ToHT(fields), where); }
        public string Update(string table, HT fields, string where) {
            string set = MakeSet(ToNVC(fields));

            string sql = $"UPDATE [{table}] SET {set} WHERE {where}";
            return sql;
        }
        public string Update(string table, NVC fields, NVC conditions) {
            string where = MakeWhere(conditions);
            string set = MakeSet(fields);

            string sql = $"UPDATE [{table}] SET {set} WHERE {where}";
            return sql;
        }

        public static string EscapeString(string str) {
            return str.Replace("'", "''");
        }

        public static NVC ToNVC(HT ht) {
            NVC nvc = new NVC();
            foreach (object col in ht.Keys) {
                if (col == null) continue;
                nvc.Add(col.ToString(), (ht[col] ?? "").ToString());
            }
            return nvc;
        }
        public static HT ToHT(NVC nvc) {
            HT ht = new HT();
            foreach (string key in nvc.Keys) {
                if (key == null) continue;
                ht.Add(key, nvc[key]);
            }
            return ht;
        }

    }
}