using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Collections.ObjectModel;
using System.IO;

namespace CC.XDocs.Data.Documents
{
    public class clsDDocuments
    {
        #region Private fields

        static readonly string[] SizeSuffixes =
                  { "bytes", "KB", "MB", "GB", "TB", "PB", "EB", "ZB", "YB" };

        private readonly Database database;

        #endregion

        #region Constructor

        /// <summary>
        /// Default constructor
        /// </summary>
        public clsDDocuments()
        {
            this.database = DatabaseFactory.CreateDatabase();
        }

        #endregion

        #region Public Methods

        /// <summary>
        /// A method that loads all the documents from the database
        /// and send them a as datatable (for the GridView).
        /// </summary>
        /// <returns>Returns the DataTable with all documents.</returns>
        public DataTable AllDocuments()
        {
            int queriesCount = 2;
            StringBuilder[] sqlQuery = new StringBuilder[queriesCount];

            sqlQuery[0] = new StringBuilder().AppendLine(" SELECT Documents.D500_DOCUMENTO AS[Document Name],")
                                             .AppendLine(" Files.D500_NOME_FILE AS[Filename],")
                                             .AppendLine(" Categories.DEFI_TIPO_DOCUMENTO AS[Category],")
                                             .AppendLine(" 	   CONVERT(DATE, Documents.DATA_INSERIMENTO, 103) AS[Upload Date],")
                                             .AppendLine("        Documents.UNIV_UTENTE_INS AS[User]")
                                             .AppendLine("   FROM XD_DD01_DOCUMENTI AS Documents")
                                             .AppendLine("   JOIN XD_DD02_FILE AS Files")
                                             .AppendLine("     ON Files.UNIV_FILE = Documents.UNIV_FILE")
                                             .AppendLine("   JOIN XD_DC01_TIPI_DOCUMENTO AS Categories")
                                             .AppendLine("     ON Categories.TIPB_DOCUMENTO = Documents.TIPB_DOCUMENTO");

            DbCommand command = this.database.GetSqlStringCommand(sqlQuery[0].ToString());

            sqlQuery[1] = new StringBuilder()
                .AppendLine("SELECT D500_PATH FROM XD_DD02_FILE AS f JOIN XD_DD01_DOCUMENTI AS d ON d.UNIV_FILE = f.UNIV_FILE");

            DbCommand pathCommand = this.database.GetSqlStringCommand(sqlQuery[1].ToString());

            // Gets the file paths needed for getting the file size
            DataRowCollection filePathsRows = this.database.ExecuteDataSet(pathCommand).Tables[0].Rows;


            DataTable dt = this.database.ExecuteDataSet(command).Tables[0];

            // Adds the column "Dimension"
            DataColumn dc = new DataColumn("Dimension", typeof(string));
            dt.Columns.Add(dc);

            // Fills the column "Dimension" with the corresponding values
            InsertFileSizeRows(filePathsRows, dt);

            return dt;
        }

        /// <summary>
        /// A method that loads all the categories from the database
        /// and send them a as datatable (for the DropDownList).
        /// </summary>
        /// <returns>Returns the DataTable with all categories.</returns>
        public DataTable AllCategories()
        {
            StringBuilder sqlQuery = new StringBuilder();

            sqlQuery.AppendLine("SELECT DEFI_TIPO_DOCUMENTO FROM XD_DC01_TIPI_DOCUMENTO");

            DbCommand command = this.database.GetSqlStringCommand(sqlQuery.ToString());

            DataTable dt = this.database.ExecuteDataSet(command).Tables[0];
            return dt;
        }

        /// <summary>
        /// A method that loads all the types from the database
        /// and send them as a datatable (for the DropDownList).
        /// </summary>
        /// <returns>Returns the DataTable with all types.</returns>
        public DataTable AllTypes()
        {
            StringBuilder sqlQuery = new StringBuilder();

            sqlQuery.AppendLine("SELECT D500_PATH FROM XD_DD02_FILE");

            DbCommand command = this.database.GetSqlStringCommand(sqlQuery.ToString());

            DataTable dt = this.database.ExecuteDataSet(command).Tables[0];
            return dt;
        }

        public DataTable AllUsers()
        {
            StringBuilder sqlQuery = new StringBuilder();

            sqlQuery.AppendLine("SELECT DISTINCT UNIV_UTENTE_INS FROM XD_DD01_DOCUMENTI");

            DbCommand command = this.database.GetSqlStringCommand(sqlQuery.ToString());

            DataTable dt = this.database.ExecuteDataSet(command).Tables[0];
            return dt;
        }

        //TODO: Expand the search button functionality with better query
        /// <summary>
        /// A method that loads the new DataTable from
        /// the user input after a search button
        /// </summary>
        /// <returns>Returns the DataTable with the categories input</returns>
        public DataTable SelectedCategory(string category)
        {
            int queriesCount = 2;
            StringBuilder[] sqlQuery = new StringBuilder[queriesCount];

            sqlQuery[0] = new StringBuilder()
                 .AppendLine(" SELECT Documents.D500_DOCUMENTO AS[Document Name],")
                 .AppendLine(" Files.D500_NOME_FILE AS[Filename],")
                 .AppendLine(" Categories.DEFI_TIPO_DOCUMENTO AS [Category],")
                 .AppendLine(" 	   CONVERT(DATE, Documents.DATA_INSERIMENTO, 103) AS[Upload Date],")
                 .AppendLine("        Documents.UNIV_UTENTE_INS AS[User]")
                 .AppendLine("   FROM XD_DD01_DOCUMENTI AS Documents")
                 .AppendLine("   JOIN XD_DD02_FILE AS Files")
                 .AppendLine("     ON Files.UNIV_FILE = Documents.UNIV_FILE")
                 .AppendLine("   JOIN XD_DC01_TIPI_DOCUMENTO AS Categories")
                 .AppendLine("     ON Categories.TIPB_DOCUMENTO = Documents.TIPB_DOCUMENTO")
                 .AppendLine("   WHERE Categories.DEFI_TIPO_DOCUMENTO = @Category");

            DbCommand command = this.database.GetSqlStringCommand(sqlQuery[0].ToString());

            database.AddInParameter(command, "@Category", DbType.String, category);

            sqlQuery[1] = new StringBuilder()
                .AppendLine("SELECT f.D500_PATH FROM XD_DD02_FILE AS f JOIN XD_DD01_DOCUMENTI AS d " +
                "ON d.UNIV_FILE = f.UNIV_FILE JOIN XD_DC01_TIPI_DOCUMENTO AS c ON d.TIPB_DOCUMENTO = c.TIPB_DOCUMENTO WHERE c.DEFI_TIPO_DOCUMENTO = @Category");

            DbCommand pathCommand = this.database.GetSqlStringCommand(sqlQuery[1].ToString());

            this.database.AddInParameter(pathCommand, "@Category", DbType.String, category);

            // Gets the file paths needed for getting the file size
            DataRowCollection filePathsRows = this.database.ExecuteDataSet(pathCommand).Tables[0].Rows;

            DataTable dt = this.database.ExecuteDataSet(command).Tables[0];

            // Adds the column "Dimension"
            DataColumn dc = new DataColumn("Dimension", typeof(string));
            dt.Columns.Add(dc);

            // Fills the column "Dimension" with the corresponding values
            InsertFileSizeRows(filePathsRows, dt);

            return dt;
        }

        /// <summary>
        /// A method that loads all the statuses from the database
        /// and send them as a datatable (for the PopupMenu DropDownList).
        /// </summary>
        /// <returns>Returns the DataTable with all statuses.</returns>
        public DataTable AllStatuses()
        {
            StringBuilder sqlQuery = new StringBuilder();

            sqlQuery.AppendLine("SELECT DEFI_STATO_DOC FROM XD_DC02_STATO_DOCUMENTO");

            DbCommand command = this.database.GetSqlStringCommand(sqlQuery.ToString());

            DataTable dt = this.database.ExecuteDataSet(command).Tables[0];
            return dt;
        }

        #endregion

        #region Private Methods

        /// <summary>
        /// A helper method for calculating the file size.
        /// </summary>
        /// <param name="value">The size of the file in bytes.</param>
        /// <returns>Returns the calculated size from bytes to KB/MB/GM as a string.</returns>
        private string SizeSuffix(Int64 value)
        {
            if (value < 0) { return "-" + SizeSuffix(-value); }

            int i = 0;
            decimal dValue = (decimal)value;
            while (Math.Round(dValue / 1024) >= 1)
            {
                dValue /= 1024;
                i++;
            }

            return string.Format("{0:n1} {1}", dValue, SizeSuffixes[i]);
        }

        /// <summary>
        /// A method that inserts the file sizes for each document
        /// into the table.
        /// </summary>
        /// <param name="filePathsRows">The rows to be inserted</param>
        /// <param name="dt">The DataTable</param>
        private void InsertFileSizeRows(DataRowCollection filePathsRows, DataTable dt)
        {
            //foreach (DataRow row in filePathsRows)
            //{
            //    string filePath = row.ItemArray[0].ToString();
            //    long fileInfoSize = new FileInfo(filePath).Length;
            //    string size = SizeSuffix(fileInfoSize);

            //    dt.Rows[index++]["Dimension"] = size;
            //}
            int index = 0;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string filePath = filePathsRows[i].ItemArray[0].ToString();
                long fileInfoSize = new FileInfo(filePath).Length;
                string size = SizeSuffix(fileInfoSize);

                dt.Rows[index++]["Dimension"] = size;
            }
        }
        #endregion
    }
}