using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web.Mvc;
using EnVisage.Code;
using EnVisage.Code.BLL;
using EnVisage.Models;
using System.Net.Http;
using Newtonsoft.Json;
using System.Text;
using System.Data.Entity.Validation;
using EnVisage.Code.ThreadedProcessing;

namespace EnVisage.Controllers
{
  
    public class ImportDataController : BaseController
    {
       
        [HttpGet] 
        public ActionResult LoadProcessDefintions()
        {
            
            ParseRecordManager manager = new ParseRecordManager(DbContext);
            IList<ParseRecordModel> ids = manager.getProcessIDs();
            return Json(ids, JsonRequestBehavior.AllowGet);
           
            
        }
        [HttpGet]
        public ActionResult LoadProcessDefintion(string ProcessID)
        {

            ParseRecordManager manager = new ParseRecordManager(DbContext);
            IList<ParseRecordModel> ids = manager.GetRecordDefinition(ProcessID);
            return Json(ids, JsonRequestBehavior.AllowGet);


        }
        [HttpPost]
        public HttpResponseMessage updateRecordDefinition(string schemaId, string[] lines)
        {
            try
            {
                if (lines == null)
                    lines = new string[0];
                if (schemaId == null)
                    schemaId = "";
                if (lines.Length == 0 || schemaId.Trim().Length == 0)
                    return new HttpResponseMessage(HttpStatusCode.NoContent);
                //based on schema id, determine what type of file it is and how to parse it
				//once parsed store the data in the Prevu format in the import table where it is either used for dropdown data or is
                //avaialbe for user review (per the todo list) for final import (thinking of actuals here)
                ParseRecordManager manager = new ParseRecordManager(DbContext);
                var ParseDefinition = manager.GetRecordDefinition(schemaId);
                if (ParseDefinition.Count == 0)
                    return new HttpResponseMessage(HttpStatusCode.BadRequest);

                ParseRecordModel p = ParseDefinition.FirstOrDefault();
                bool isDelimted = p.isDelimited.ToLower().Equals("y");
                bool ok = false;
                //to do add xml parser
                // today if its not delimited then its fixed length
                string sqlcmd = "";
                int linenbr = 0;
                foreach (string line in lines)
                {
                    linenbr++;
                    if (line.Trim().Length > 0)
                    {
                        if (isDelimted)
                        {
                            string cmd = "";
                            if (p.ToType == "sproc")
                                cmd = parseAndExecSprocDelimtedRecord(line, ParseDefinition, p.Delimitor, p.ToName, linenbr, p.ProcessID);
                            else  
                                cmd = parseAndUpdateDelimtedRecord(line, ParseDefinition, p.Delimitor, p.ToName);
                            if (cmd != null)
                                sqlcmd += cmd;
                        }
                        else
                        {
                            string cmd = "";
                            if (p.ToType == "sproc")
                                cmd = parseAndExecSprocFixedLenghtRecord(line, ParseDefinition, p.ToName, linenbr, p.ProcessID);
                            else
                                cmd = parseAndUpdateFixedLenghtRecord(line, ParseDefinition, p.ToName);
                            if (cmd != null)
                                sqlcmd += cmd;
                        }
                    }
                }
                if (sqlcmd != "")
                {
                    string sqltrans = "Begin transaction " + Environment.NewLine;
                    sqltrans += " " + sqlcmd + " Commit transaction";

                    if (p.ToType == "sproc")
                    {
                        sqltrans = sqlcmd;
                    }
                    ok = this.doSql(sqltrans);
                }
                if (ok)
                    return new HttpResponseMessage(HttpStatusCode.OK);
                else
                    return new HttpResponseMessage(HttpStatusCode.InternalServerError);
            }
            catch (Exception d) { LogException(d); }
            return new HttpResponseMessage(HttpStatusCode.InternalServerError);
        }
        // add data to system
        [HttpPost]
        public HttpResponseMessage Import(string schemaId, string[] lines)
        {
            try
            {
                if (lines == null)
                    lines =new string[0];
                if (schemaId == null)
                    schemaId = "";
                if (lines.Length == 0 || schemaId.Trim().Length == 0)
                    return new HttpResponseMessage(HttpStatusCode.NoContent);
                //based on schema id, determine what type of file it is and how to parse it
				//once parsed store the data in the Prevu format in the import table where it is either used for dropdown data or is
                //avaialbe for user review (per the todo list) for final import (thinking of actuals here)
                ParseRecordManager manager = new ParseRecordManager(DbContext);
                var ParseDefinition = manager.GetRecordDefinition(schemaId);
                if (ParseDefinition.Count == 0)
                    return new HttpResponseMessage(HttpStatusCode.BadRequest);

                ParseRecordModel p = ParseDefinition.FirstOrDefault();
                bool isDelimted = p.isDelimited.ToLower().Equals("y");
                bool ok = false;
                //to do add xml parser
                // today if its not delimited then its fixed length
                bool doDelete = false;
                if (p.Overlay.HasValue)
                    doDelete = p.Overlay.Value;

                string sqlcmd = "";
               
                foreach (string line in lines)
                {
                    if (line.Trim().Length > 0)
                    {
                        if (isDelimted)
                        {
                            string cmd = "";
                            cmd = parseAndInsertDelimitedRecord(line, ParseDefinition, p.Delimitor, p.ToName);
                            if (cmd != null)
                                sqlcmd += cmd;
                        }
                        else
                        {
                            string cmd = "";
                            cmd = parseAndInsertFixedLenghtRecord(line, ParseDefinition, p.ToName);
                            if (cmd != null)
                                sqlcmd += cmd;
                        }
                        //if (!ok)
                        //{
                        //    LogError("Import data failed for:" + schemaId + " data for import:" + line);
                        //    break;
                        //}
                      
                    }
                }
                if (sqlcmd != "")
                {
                    string sqltrans = "Begin transaction " + Environment.NewLine;
                    if (doDelete)
                        sqltrans += Environment.NewLine + " Delete from " + p.ToName + Environment.NewLine + sqlcmd + " Commit transaction" + Environment.NewLine;
                    else
                        sqltrans += " " + sqlcmd + " Commit transaction";
                    ok = this.doSql(sqltrans);
                }
                if (ok)
                    return new HttpResponseMessage(HttpStatusCode.OK);
                else
                    return new HttpResponseMessage(HttpStatusCode.InternalServerError);
            }
            catch (Exception d) { LogException(d); }
            return new HttpResponseMessage(HttpStatusCode.InternalServerError);
        }

        public void EditPeopleResource(string json)
        {
            Guid savedId = Guid.NewGuid();
            Guid userid = Guid.Parse(HttpContext.User.Identity.GetID());
            List<PeopleResourceAPIModel> apiModels = JsonConvert.DeserializeObject<List<PeopleResourceAPIModel>>(json);
            BackgroundProcessManager bpm = new BackgroundProcessManager(savedId, userid);
            bpm.BulkPeopleResourceRecordEditAsync(apiModels, savedId);
        }
        public  static void LogImportException(string processid, Exception ex, int linenbr, Guid groupId)
        {
            var sb = new StringBuilder();
            sb.AppendLine(string.Format("{0}: {1}", ex.GetType(), ex.Message));
            sb.AppendLine(ex.StackTrace);

            var innerCount = 0;
            var innerEx = ex;
            while (innerEx.InnerException != null && innerCount++ < EnVisage.Code.Constants.MAX_INNER_EXCEPTION_LOG_LEVEL)
            {
                if (innerEx.Message != innerEx.InnerException.Message)
                    sb.AppendLine("Inner Exception Message: " + innerEx.InnerException.Message);
                innerEx = innerEx.InnerException;
            }
            var dbEntityValidationException = ex as DbEntityValidationException;
            if (dbEntityValidationException != null)
            {
                foreach (var validationErrors in dbEntityValidationException.EntityValidationErrors)
                {
                    foreach (var validationError in validationErrors.ValidationErrors)
                    {
                        sb.AppendFormat("Property: {0} Error: {1}", validationError.PropertyName,
                            validationError.ErrorMessage);

                    }
                }
                sb.AppendLine(dbEntityValidationException.StackTrace);
            }
            if (System.Web.HttpContext.Current != null)
            {
                sb.AppendLine();
                sb.AppendLine(string.Format("URL: {0}", System.Web.HttpContext.Current.Request.Url));
                sb.AppendLine(string.Format("Referrer: {0}", System.Web.HttpContext.Current.Request.UrlReferrer));
                sb.AppendLine(string.Format("QueryString: {0}", System.Web.HttpContext.Current.Request.QueryString));
                sb.AppendLine(string.Format("UserHostAddress: {0}", System.Web.HttpContext.Current.Request.UserHostAddress));
                sb.AppendLine(string.Format("UserAgent: {0}", System.Web.HttpContext.Current.Request.UserAgent));
                if (System.Web.HttpContext.Current.Request.Form.Count > 0)
                {
                    sb.AppendLine();
                    sb.AppendLine("Form:");
                    foreach (string key in System.Web.HttpContext.Current.Request.Form.Keys)
                    {
                        sb.AppendLine(string.Format("{0}: {1}", key, System.Web.HttpContext.Current.Request.Form[key]));
                    }
                }
            }

            EnVisageEntities _dbContext = new EnVisageEntities();
            var importMessage = new supt_ImportMessages()
            {
                Id = Guid.NewGuid(),
                ProcessID = processid,
                MessageType = (int)ImportDataMessageType.Exception,
                Message = sb.ToString(),
                RecordNbr = linenbr,
                DateTimeProcessed = DateTime.Now,
                GroupID = groupId
            };
            _dbContext.supt_ImportMessages.Add(importMessage);
            _dbContext.Entry(importMessage).State = EntityState.Added;
            _dbContext.SaveChanges();
        }
        public static void LogImportMessage(string processid, string message, int linenbr, Guid groupId)
        {
            EnVisageEntities _dbContext = new EnVisageEntities();
            var importMessage = new supt_ImportMessages()
            {
                Id = Guid.NewGuid(),
                ProcessID = processid,
                MessageType = (int)ImportDataMessageType.Information,
                Message = message,
                RecordNbr = linenbr,
                DateTimeProcessed = DateTime.Now,
                GroupID = groupId
            };
            _dbContext.supt_ImportMessages.Add(importMessage);
            _dbContext.Entry(importMessage).State = EntityState.Added;
            _dbContext.SaveChanges();
        }
        public static void LogHeaderMessage(string processid, string message,Guid groupId)
        {
            ClearOutLogMessages(processid);
            EnVisageEntities _dbContext = new EnVisageEntities();
            
            var importMessage = new supt_ImportMessages()
            {
                Id = Guid.NewGuid(),
                ProcessID = processid,
                MessageType = (int)ImportDataMessageType.Header,
                Message = message,
                RecordNbr = -1,
                DateTimeProcessed = DateTime.Now,
                GroupID = groupId
            };
            _dbContext.supt_ImportMessages.Add(importMessage);
            _dbContext.Entry(importMessage).State = EntityState.Added;
            _dbContext.SaveChanges();
        }
        public static void LogTrailerMessage(string processid, string message, Guid groupId)
        {
            EnVisageEntities _dbContext = new EnVisageEntities();
            var importMessage = new supt_ImportMessages()
            {
                Id = Guid.NewGuid(),
                ProcessID = processid,
                MessageType = (int)ImportDataMessageType.Trailer,
                Message = message,
                RecordNbr = -1,
                DateTimeProcessed = DateTime.Now,
                GroupID=groupId
            };
            _dbContext.supt_ImportMessages.Add(importMessage);
            _dbContext.Entry(importMessage).State = EntityState.Added;
            _dbContext.SaveChanges();
        }
        public static void ClearOutLogMessages(string processid)
        {
            EnVisageEntities _dbContext = new EnVisageEntities();
            int totalHeaders = _dbContext.supt_ImportMessages.Where(x => x.ProcessID == processid && x.MessageType == (int)ImportDataMessageType.Header).Distinct().OrderBy(x => x.DateTimeProcessed).Count();
            if (totalHeaders > 2)
            {
                supt_ImportMessages msgHeader = _dbContext.supt_ImportMessages.Where(x => x.ProcessID == processid && x.MessageType == (int)ImportDataMessageType.Header).Distinct().OrderBy(x => x.DateTimeProcessed).First();
                var msgs = _dbContext.supt_ImportMessages.Where(x => x.GroupID == msgHeader.GroupID);
                _dbContext.supt_ImportMessages.RemoveRange(msgs);
                _dbContext.SaveChanges();
            }
        }
        private string parseAndExecSprocDelimtedRecord(string data, IList<ParseRecordModel> definitions, string delim, string sprocName, int recnbr, string processID)
        {
            try
            {
                string execcmd = "exec " + sprocName + " @ProcessID='" + processID + "', @RecordNumber=" + recnbr.ToString();

                string sepvalue = ",";
                string vals = "";

                string[] splprm = { delim };
                string[] values = data.Split(splprm, StringSplitOptions.None);
                foreach (ParseRecordModel p in definitions.Select(x => x).OrderBy(x => x.index))
                {

                    if (values.Length < p.index)
                    {
                        return null;
                    }
                    string q = "";
                    if (p.ToType != "int")
                        q = "'";
                    if (p.AdditionalFormat != null && p.AdditionalFormat.ToLower().Equals("allownull"))
                    {
                        if (values[p.index.Value].Trim().Equals("") || values[p.index.Value].Trim().Equals("NULL"))
                        {
                            values[p.index.Value] ="NULL";
                            q = "";
                        }
                    }
                     vals += sepvalue + p.ToField + "=" + q + scrubData(values[p.index.Value].Trim(), p.AdditionalFormat, p.ToType) + q;
                     sepvalue = ",";
                }
                execcmd += " " + vals + ";" + Environment.NewLine;
                return execcmd;
            }
            catch (Exception parsEx) { LogException(parsEx); }
            return null;

        }
        private string parseAndUpdateDelimtedRecord(string data, IList<ParseRecordModel> definitions, string delim, string toTable)
        {
            string insertstatement="if isnull((select count([key]) from [table] where [key] = [keyvalue]),0) = 0  begin Insert into [table] ([cols]) values ([vals]) end ";
           
            try
            {

                string cols = "";
                string vals = "";
                string sepcols = "";
                string sepvalues = "";
                string key = "";
                string keyvalue = "";
                bool keyPairSet=false;
                string[] splprm = { delim };
                string[] values = data.Split(splprm, StringSplitOptions.None);
                foreach (ParseRecordModel p in definitions.Select(x => x).OrderBy(x => x.index))
                {
                   
                    if (values.Length < p.index)
                    {
                        return null;
                    }
                    string q = "";
                    if (p.ToType != "int")
                        q = "'";
                    if (p.AdditionalFormat != null && p.AdditionalFormat.ToLower().Equals("allownull"))
                    {
                        if (values[p.index.Value].Trim().Equals(""))
                        {
                            values[p.index.Value] ="NULL";
                            q = "";
                        }
                    }
                    cols += sepcols + p.ToField;
                    sepcols = ",";
                    string v = scrubData(values[p.index.Value].Trim(), p.AdditionalFormat, p.ToType);
                    vals += sepvalues + q + v + q;
                    sepvalues = ",";
                    if (p.ToType == "key"){
                        key = p.ToField;
                        keyvalue = q + v + q;
                        keyPairSet = true;
                    }
                }
                if (keyPairSet){
                    insertstatement = insertstatement.Replace("[cols]", cols).Replace("[vals]", vals).Replace("[key]", key).Replace("[keyvalue]", keyvalue).Replace("[table]",toTable);
                 
                return insertstatement;
                }else{
                throw new Exception("Keypair not set for :"+definitions.FirstOrDefault().ProcessID+".  Record not inserted!");
                }
            }
            catch (Exception parsEx) { LogException(parsEx); }
            return null;
        }
        private string parseAndInsertDelimitedRecord(string data, IList<ParseRecordModel> definitions, string delim, string toTable)
        {
            try
            {
                string insert = "Insert into " + toTable;
                string cols = "";
                string vals = "";
                string sepcols = "";
                string sepvalues = "";

              //  string del = "delete from " + toTable;
                string[] splprm = { delim };
                string[] values = data.Split(splprm, StringSplitOptions.None);
                foreach (ParseRecordModel p in definitions.Select(x => x).OrderBy(x => x.index))
                {

                    if (values.Length < p.index)
                    {
                        return null;
                    }
                    string q = "";
                    if (p.ToType != "int")
                        q = "'";
               
                    if (p.AdditionalFormat != null && p.AdditionalFormat.ToLower().Equals("allownull"))
                    {
                        if (values[p.index.Value].Trim().Equals(""))
                        {
                            values[p.index.Value] ="NULL";
                            q = "";
                        }
                    }
                    cols += sepcols + p.ToField;
                    sepcols = ",";
                    vals += sepvalues + q + scrubData(values[p.index.Value].Trim(), p.AdditionalFormat, p.ToType) + q;
                    sepvalues = ",";
                }
                insert += " (" + cols + ") values (" + vals + ")";
                insert += Environment.NewLine + "IF @@error <> 0 " + Environment.NewLine + "ROLLBACK transaction;" + Environment.NewLine;
                return insert;
            }
            catch (Exception parsEx) { LogException(parsEx); }
            return null;
        }
        private string parseAndInsertFixedLenghtRecord(string data, IList<ParseRecordModel> definitions, string toTable)
        {
            string insert = "Insert into " + toTable;
            string cols = "";
            string vals = "";
            string sepcols = "";
            string sepvalues = "";
            try
            {
                foreach (ParseRecordModel p in definitions.Select(x => x).OrderBy(x => x.StartPos))
                {

                    if (data.Length < (p.StartPos + p.Length))
                    {
                        return null;
                    }

                    string q = "";
                    if (p.ToType != "int")
                        q = "'";
                    string v = data.Substring(p.StartPos.Value, p.Length.Value).Trim();
                    if (p.AdditionalFormat != null && p.AdditionalFormat.ToLower().Equals("allownull"))
                    {
                        if (v.Trim().Equals(""))
                        {
                            v ="NULL";
                            q = "";
                        }
                    }

                    cols += sepcols + p.ToField;
                    sepcols = ",";
                    vals = sepvalues + q + scrubData(v, p.AdditionalFormat, p.ToType) + q;
                    sepvalues = ",";
                }
                insert += " (" + cols + ") values (" + vals + ")";
                insert += Environment.NewLine + "IF @@error <> 0 " + Environment.NewLine + "ROLLBACK transaction;" +Environment.NewLine;
                return insert;
            }
            catch (Exception parsEx) { LogException(parsEx); }
            return null;
        }
        private string parseAndUpdateFixedLenghtRecord(string data, IList<ParseRecordModel> definitions, string toTable)
        {
            string insertstatement="if isnull((select count([key]) from [table] where [key] = [keyvalue]),0) = 0  begin Insert into [table] ([cols]) values ([vals]) end else update [table] set [colequval] where [key] = [keyvalue] ";

            try
            {

                string cols = "";
                string vals = "";
                string sepcols = "";
                string sepvalues = "";
                string key = "";
                string keyvalue = "";
                string updateSet = "";
                bool keyPairSet=false;
                foreach (ParseRecordModel p in definitions.Select(x => x).OrderBy(x => x.index))
                {

                    if (data.Length < (p.StartPos + p.Length))
                    {
                        return null;
                    }

                    string q = "";
                    if (p.ToType != "int")
                        q = "'";
                    string v = data.Substring(p.StartPos.Value, p.Length.Value).Trim();
                    if (p.AdditionalFormat != null && p.AdditionalFormat.ToLower().Equals("allownull"))
                    {
                        if (v.Trim().Equals(""))
                        {
                            v ="NULL";
                            q = "";
                        }
                    }
                    cols += sepcols + p.ToField;
                    sepcols = ",";
                    
                    vals = sepvalues + q + v + q;
                    updateSet += sepvalues + p.ToField + "=" + q + v + q;
                    
                    sepvalues = ",";
                    if (p.ToField == "key"){
                        key = p.ToField;
                        keyvalue = q + v + q;
                        keyPairSet = true;
                    }
                }
                if (keyPairSet){
                    insertstatement = insertstatement.Replace("[cols]", cols).Replace("[vals]", vals).Replace("[key]", key).Replace("[keyvalue]", keyvalue).Replace("[colequval]", updateSet).Replace("[table]", toTable);
                    return insertstatement;
                }else{
                throw new Exception("Keypair not set for :"+definitions.FirstOrDefault().ProcessID+".  Record not inserted!");
                }
            }
            catch (Exception parsEx) { LogException(parsEx); }
            return null;
        }
        private string parseAndExecSprocFixedLenghtRecord(string data, IList<ParseRecordModel> definitions, string sprocName, int recnbr, string processID)
        {
            try
            {
                string execcmd = "exec " + sprocName + " @ProcessID='" + processID + "', @RecordNumber=" + recnbr.ToString();
                string sepvalue = ",";
                string vals = "";

                foreach (ParseRecordModel p in definitions.Select(x => x).OrderBy(x => x.index))
                {

                   if (data.Length < (p.StartPos + p.Length))
                    {
                        return null;
                    }
                    string q = "";
                    if (p.ToType != "int")
                        q = "'";
                    string v = data.Substring(p.StartPos.Value, p.Length.Value).Trim();
                    if (p.AdditionalFormat != null && p.AdditionalFormat.ToLower().Equals("allownull"))
                    {
                        if (v.Trim().Equals(""))
                        {
                            v ="NULL";
                            q = "";
                        }
                    }
                    vals += sepvalue + p.ToField + "=" + q + scrubData(v, p.AdditionalFormat, p.ToType) + q;
                    sepvalue = ",";
                }
                execcmd += " " + vals + ";" + Environment.NewLine;
                return execcmd;
            }
            catch (Exception parsEx) { LogException(parsEx); }
            return null;

        }
        //to do add data validation and formatting
        private string scrubData(string val, string format, string type )
        {

            val = val.Replace("'", "''").Replace(",", " ").Replace("%", " ").Replace("'NULL'", "NULL");
            return val;
        }
        //todo add xml here
        private bool parseAndInsertXMLRecord(string data, IList<ParseRecordModel> definitions)
        {
            return true;
        }
        private bool doSql(string statement)
        {
            try
            {
                DbContext.Database.ExecuteSqlCommand(statement,null,null);
                return true;
            }
            catch
			{
			}
            return false;
        }
    }
}