using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;
using EnVisage;
using EnVisage.Code;
using EnVisage.Code.BLL;
using EnVisage.Code.HtmlHelpers;
using EnVisage.Models;
using jQuery.DataTables.Mvc;
using EnVisage.App_Start;
using Microsoft.AspNet.Identity;
using System.Collections.ObjectModel;
using System.IO;
using FileHelpers;
using System.Web.Script.Serialization;
using EnVisage.Code.Cache;
using System.Net.Http;

namespace EnVisage.Controllers
{
    public class ImportDataController : BaseController
    {
        [Authorize]
        [HttpGet]
        
        public ActionResult LoadProcessDefintions()
        {
            
            ParseRecordManager manager = new ParseRecordManager(DbContext);
            IList<ParseRecordModel> ids = manager.getProcessIDs();
            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);
        }
        private bool CheckUser(string userName, string password)
        {

            string user = null; // manager.FindAsync(userName, password);
            if (user != null)
                return true;
            return false;
        }
        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] ="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("%", " ");
            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 (Exception df) { }
            return false;
        }
    }
}