using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net;
using System.Xml.Linq;
using Neo.Afx.ComponentModel;
using Neo.Afx.Services.Documents.Entities;
using Neo.Afx.Services.Audits;

namespace Neo.Afx.Services.Documents
{
    // TODO: TWR - Fix KVP results and remove try..catch's

    /// <summary>
    /// The documents database
    /// </summary>
    public partial class DocumentsDatabase : Database<DocumentContext>
    {
        const string DefaultMimeType = "application/octet-stream";
        const int ItemNumberWidth = 7;  // used to pad ItemID

        /// <summary>
        /// Initializes a new instance of the <see cref="DocumentsDatabase"/> class.
        /// </summary>
        public DocumentsDatabase()
            : base(new DocumentContext())
        {
        }

        #region UploadFile

        /// <summary>
        /// Uploads the file.
        /// </summary>
        /// <param name="uploadedByID">The uploaded by ID.</param>
        /// <param name="uploadedByUserName">The NT account name of the user.</param>
        /// <param name="documentID">The document ID.</param>
        /// <param name="entityID">The entity ID.</param>
        /// <param name="itemID">The item ID.</param>
        /// <param name="documentTypeID">The document type ID.</param>
        /// <param name="documentStoreLocationID">The document store location ID.</param>
        /// <param name="fileName">Name of the file.</param>
        /// <param name="contentType">Type of the content.</param>
        /// <param name="content">The content.</param>
        /// <returns></returns>
        public Document UploadFile(long uploadedByID, string uploadedByUserName, long documentID, int entityID, long itemID, short documentTypeID, long documentStoreLocationID, string fileName, string contentType, byte[] content)
        {
            var location = GetDocumentStoreLocation(documentStoreLocationID);
            if(location == null)
            {
                throw new ArgumentOutOfRangeException("documentStoreLocationID", "Invalid document store location ID");
            }

            var mimeType = GetDocumentMimeType(contentType);
            if(mimeType == null)
            {
                throw new ArgumentOutOfRangeException("contentType", "The file mime type is not supported by the file store.");
            }

            Document document;

            if(documentID > 0)
            {
                document = GetDocuments(entityID, itemID).FirstOrDefault(d => d.DocumentID == documentID);
            }
            else
            {
                document = GetDocuments(entityID, itemID).FirstOrDefault(d => d.Description.ToLower() == fileName.ToLower()
                                                                              && d.DocumentTypeID == documentTypeID
                                                                              && d.DocumentStoreLocationID == documentStoreLocationID);    
            }
            
            if(document != null)
            {
                document.UpdatedByID = uploadedByID;
                document.UpdatedDate = DateTime.Now;
                document.Description = fileName;
                document.DocumentMimeTypeID = mimeType.DocumentMimeTypeID;
                document.DocumentVerificationStatusID = 1; /*change status to awaiting verifition as a new document is being added*/
                Update(document);

                Save();
                Upload(document, uploadedByUserName, content);
            }
            else
            {
                if(documentID == -1)
                {
                    var documentType = GetDocumentType(documentTypeID);

                    document = new Document
                    {
                        IsCancelled = false,
                        CreatedByID = uploadedByID,
                        CreatedDate = DateTime.Now,
                        EntityID = entityID,
                        ItemID = itemID,
                        DocumentTypeID = documentTypeID,
                        DocumentStoreLocationID = location.DocumentStoreLocationID,
                        DocumentMimeTypeID = mimeType.DocumentMimeTypeID,
                        Title = documentType.Description,
                        Description = fileName,
                        UpdatedByID = uploadedByID,
                        UpdatedDate = DateTime.Now,
                        DocumentVerificationStatusID = 1 /*default status to awaiting verification*/
                    };

                    Insert(document);

                    Save();
                    Upload(document, uploadedByUserName, content);
                }
            }

            var auditDb = new AuditsDatabase();
            auditDb.WriteEntry(uploadedByID, "Application", "Updated", "Uploaded document " + fileName, false, false, entityID, itemID);

            return document;
        }

        #endregion

        #region GetDocument

        /// <summary>
        /// Gets the document.
        /// </summary>
        /// <param name="documentID">The document ID.</param>
        /// <returns></returns>
        public Document GetDocument(long documentID)
        {
            return GetByID<Document>(documentID);
        }

        #endregion

        #region GetDocumentStores

        /// <summary>
        /// Gets the document stores.
        /// </summary>
        /// <param name="documentID">The document ID.</param>
        /// <returns></returns>
        public IEnumerable<DocumentStore> GetDocumentStores(long documentID)
        {
            return from q in Context.DocumentStores.Include("Document")
                   where q.DocumentID == documentID
                   select q;
        }

        #endregion

        #region UpdateDocumentItemID

        /// <summary>
        /// Updates the document item ID.
        /// </summary>
        /// <param name="updatedByID">The updated by ID.</param>
        /// <param name="entityID">The entity ID.</param>
        /// <param name="oldItemID">The old item ID.</param>
        /// <param name="newItemID">The new item ID.</param>
        /// <returns></returns>
        public void UpdateDocumentItemID(long updatedByID, long entityID, long oldItemID, long newItemID)
        {
            var document = Get<Document>(d => d.EntityID == entityID && d.ItemID == oldItemID, null, "DocumentStoreLocation").FirstOrDefault();
            if(document == null)
            {
                return;
            }

            Context.Database.ExecuteSqlCommand("UPDATE Doc.Documents SET ItemID=@p0, UpdatedByID=@p1, UpdatedDate=GETDATE() WHERE EntityID=@p2 AND ItemID=@p3",
                newItemID, updatedByID, entityID, oldItemID);
        }

        #endregion

        #region Delete

        /// <summary>
        /// Deletes the document with the specified ID.
        /// </summary>
        /// <param name="deletedByID">The user ID.</param>
        /// <param name="documentID">The document ID.</param>
        /// <returns>An error message or null if successful.</returns>
        public string Delete(long deletedByID, long documentID)
        {
            var document = GetDocument(documentID);
            if(document == null)
            {
                return "Document does not exist";
            }

            document.IsCancelled = true;
            document.UpdatedByID = deletedByID;
            document.UpdatedDate = DateTime.Now;
            Update(document);
            Save();

            return null;
        }

        #endregion

        #region GetDocuments

        /// <summary>
        /// Gets the documents.
        /// </summary>
        /// <param name="entityID">The entity ID.</param>
        /// <param name="itemID">The item ID.</param>
        /// <returns></returns>
        public IEnumerable<Document> GetDocuments(int entityID, long itemID)
        {
            return from q in Context.Documents.Include("UserCreatedBy").Include("UserUpdatedBy").Include("DocumentMimeType")
                   where q.EntityID == entityID && (itemID != -1 ? q.ItemID == itemID : true) && !q.IsCancelled
                   select q;
        }

        #endregion

        #region CopyDocuments

        /// <summary>
        /// Copies the documents.
        /// </summary>
        /// <param name="userID">The user ID.</param>
        /// <param name="sourceDocumentIDs">The source document I ds.</param>
        /// <param name="targetEntityID">The target entity ID.</param>
        /// <param name="targetItemID">The target item ID.</param>
        /// <returns></returns>
        public KeyValuePair<long, string> CopyDocuments(long userID, string sourceDocumentIDs, int targetEntityID, long targetItemID)
        {
            KeyValuePair<long, string> returnResult;

            try
            {
                var result = Context.Pr_DocumentCopyMany(sourceDocumentIDs, targetItemID, targetEntityID, userID).First();

                returnResult = result != "1" ? new KeyValuePair<long, string>(1, "Error: " + result) : new KeyValuePair<long, string>(1, "Success");
            }
            catch(Exception ex)
            {
                returnResult = new KeyValuePair<long, string>(0, ex.InnerException.Message);
            }

            return returnResult;

        }

        #endregion

        #region GetDocumentObject

        /// <summary>
        /// Gets the document object.
        /// </summary>
        /// <param name="documentID">The document ID.</param>
        /// <returns></returns>
        public FileObject GetDocumentObject(long documentID)
        {
            var doc = Context.Documents.Where(d => d.DocumentID == documentID).Include("DocumentStoreLocation").Include("DocumentMimeType").FirstOrDefault();
            return doc == null ? null : Download(doc);
        }

        #endregion

        #region GetDocumentObjectWithoutBlob

        /// <summary>
        /// Gets the document object.
        /// </summary>
        /// <param name="documentID">The document ID.</param>
        /// <returns></returns>
        public Document GetDocumentObjectWithoutBlob(long documentID)
        {
            var doc = Context.Documents.Where(d => d.DocumentID == documentID).Include("DocumentType").Include("DocumentMimeType").FirstOrDefault();
            return doc;
        }

        #endregion

        #region GetDocumentType

        /// <summary>
        /// Gets the document type object.
        /// </summary>
        /// <param name="documentTypeID">The document type ID.</param>
        /// <returns></returns>
        public DocumentType GetDocumentType(long documentTypeID)
        {
            return Context.DocumentTypes.Where(d => d.DocumentTypeID == documentTypeID).FirstOrDefault();            
        }

        #endregion

        #region GetDocumentTemplateData

        /// <summary>
        /// Gets the document template data.
        /// </summary>
        /// <param name="entityID">The entity ID.</param>
        /// <param name="itemID">The item ID.</param>
        /// <returns></returns>
        public XElement GetDocumentTemplateData(int entityID, long itemID)
        {
            return XElement.Parse(Context.Pr_DocumentTemplateDataGet(entityID, itemID).FirstOrDefault());
        }

        #endregion

        #region GetDocumentStoreLocation

        DocumentStoreLocation GetDocumentStoreLocation(long documentStoreLocationID)
        {
            return Context.DocumentStoreLocations.Where(l => l.DocumentStoreLocationID == documentStoreLocationID).FirstOrDefault();
        }

        #endregion

        #region GetMimeType

        /// <summary>
        /// Gets the <see cref="DocumentMimeType"/> of the <paramref name="fileMimeType"/>;
        /// returns the <see cref="DefaultMimeType"/> if not found.
        /// </summary>
        /// <param name="fileMimeType">Type of the file MIME.</param>
        /// <returns>
        /// The <see cref="DocumentMimeType"/> of the <paramref name="fileMimeType"/> or the <see cref="DefaultMimeType"/> if not found.
        /// </returns>
        DocumentMimeType GetDocumentMimeType(string fileMimeType)
        {
            DocumentMimeType result = null;

            var mimeTypes = Context.DocumentMimeTypes.Where(m => m.MimeType == fileMimeType || m.MimeType == DefaultMimeType);

            foreach(var mimeType in mimeTypes)
            {
                if(mimeType.MimeType == fileMimeType)
                {
                    result = mimeType;
                    break;
                }
                result = mimeType;
            }
            return result;
        }

        #endregion

        #region Upload

        /// <summary>
        /// Uploads the specified document.
        /// </summary>
        /// <param name="doc">The document.</param>
        /// <param name="uploadedByUserName">Name of the uploaded by user.</param>
        /// <param name="content">The content.</param>
        void Upload(Document doc, string uploadedByUserName, byte[] content)
        {
            var fileName = doc.Description;
            var extension = Path.GetExtension(fileName);
            var mimeType = doc.DocumentMimeType.MimeType;
            var location = doc.DocumentStoreLocation;

            try
            {
                switch((DocumentStoreTypeEnum)location.DocumentStoreTypeID)
                {
                    case DocumentStoreTypeEnum.SQL:
                        using(var conn = new SqlConnection(location.ConnectionString))
                        {
                            conn.Open();
                            var cmd = new SqlCommand("Doc.Pr_DocumentStoreCreate", conn)
                            {
                                CommandType = CommandType.StoredProcedure
                            };

                            cmd.Parameters.Add(new SqlParameter("@DocumentID", doc.DocumentID));
                            cmd.Parameters.Add(new SqlParameter("@ContentBLOB", content));
                            cmd.Parameters.Add(new SqlParameter("@MimeType", mimeType));
                            cmd.Parameters.Add(new SqlParameter("@Size", content.Length));
                            cmd.Parameters.Add(new SqlParameter("@Name", fileName));
                            cmd.Parameters.Add(new SqlParameter("@Ext", extension));

                            cmd.ExecuteNonQuery();
                        }
                        break;
                }
            }
            catch(Exception ex)
            {
                Delete(doc.CreatedByID, doc.DocumentID);

                throw ex;
            }
        }

        #endregion

        #region Download

        // doc must include DocumentStoreLocation & DocumentMimeType
        FileObject Download(Document doc)
        {
            var result = new FileObject
            {
                ID = doc.DocumentID,
                Name = doc.Description,
                Extension = Path.GetExtension(doc.Description),
                MimeType = doc.DocumentMimeType.MimeType,
                Content = new byte[0],
                Length = 0
            };
            var location = doc.DocumentStoreLocation;

            switch((DocumentStoreTypeEnum)location.DocumentStoreTypeID)
            {
                case DocumentStoreTypeEnum.SQL:
                    using(var conn = new SqlConnection(location.ConnectionString))
                    {
                        conn.Open();
                        var cmd = new SqlCommand("Doc.Pr_DocumentStoreGet", conn)
                        {
                            CommandType = CommandType.StoredProcedure
                        };

                        cmd.Parameters.Add(new SqlParameter("@DocumentID", doc.DocumentID));
                        using(var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            if(reader.Read())
                            {
                                result.Content = (byte[])reader["ContentBLOB"];
                            }
                        }
                    }
                    break;
            }

            result.Length = result.Content.Length;
            return result;
        }

        #endregion

        ////#region GetFolderName

        ////string GetFolderName(long entityID, long itemID)
        ////{
        ////    var entity = GetDocumentEntity(entityID);
        ////    return GetFolderName(entity, itemID);
        ////}

        ////static string GetFolderName(DocumentEntity entity, long itemID)
        ////{
        ////    var idStr = itemID.ToString();
        ////    if(itemID > 0)
        ////    {
        ////        idStr = idStr.PadLeft(ItemNumberWidth, '0');
        ////    }
        ////    return entity.FolderPrefix + idStr;
        ////}

        ////#endregion

        #region GetSysSetting

        /// <summary>
        /// Gets the system setting.
        /// </summary>
        /// <param name="settingName">Name of the setting.</param>
        /// <returns></returns>
        public string GetSysSetting(string settingName)
        {
            return Context.Database.SqlQuery<string>("SELECT System.Fn_GetSettingValue(@SettingName)",
                new SqlParameter("@SettingName", settingName)).FirstOrDefault();
        }

        #endregion

        #region UpdateDocumentTitle
        /// <summary>
        /// Updates a document title
        /// </summary>        
        /// <returns></returns>
        public void UpdateDocumentTitle(long documentId, string documentTitle, long updatedById)
        {
            Context.Database.ExecuteSqlCommand("UPDATE Doc.Documents SET Title=@p1, UpdatedByID=@p2, UpdatedDate=GETDATE() WHERE DocumentID=@p0",
                documentId, documentTitle, updatedById);
        }
        #endregion

        #region GetUserNameById

        /// <summary>
        /// Gets a UserName Derived from the supplied User ID
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public String GetUserNameByUserID(int userID)
        {
            var userObject = Context.DocumentsUsers.FirstOrDefault(a => a.UserID == userID);
            return userObject == null ? "" : userObject.FullName;
        }

        #endregion

        #region GetEntityDocuments
        /// <summary>
        /// Gets a list of all documents uplaoded against an entity
        /// </summary>
        /// <returns></returns>
        public List<Pr_DocumentsGet_Result> GetEntityDocuments(int entityID, long itemID)
        {
            return Context.Pr_DocumentsGet(entityID, itemID).ToList();
        }

        #endregion

        #region UpdateDocumentIDOnEntity
        /// <summary>
        /// Updated the document id on the related entity
        /// </summary>
        /// <returns></returns>
        public void UpdateDocumentIDOnEntity(long documentID, int entityID, long itemID, string relatedFieldKey, long userID)
        {
            Context.Pr_DocumentIDUpdateEntity(documentID, entityID, itemID, relatedFieldKey, userID);
        }

        #endregion

    }
}