using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using CMS.TreeEngine;
using CMS.CMSHelper;
using CMS.DataEngine;
using CMS.SettingsProvider;
using System.Data;
using CMS.GlobalHelper;
using CMS.SiteProvider;
using System.Text.RegularExpressions;
using CMS.EmailEngine;
using CMS.DocumentEngine;
using CMS.Helpers;
using CMS.Localization;
using CMS.Membership;
using CMS.MacroEngine;

/// <summary>
/// Summary description for ERPDataHelper
/// </summary>
public class ERPDataHelper
{
    /// <summary>
    /// Loads categories from database
    /// </summary>
    /// <param name="values"></param>
    /// <returns></returns>
    public static LinkedList<KeyValuePair<string, string>> GetOptionsFromContentTree(string type, string key, string value)
    {
        LinkedList<KeyValuePair<string, string>> values = null;

        using (CachedSection<LinkedList<KeyValuePair<string, string>>> cs = new CachedSection<LinkedList<KeyValuePair<string, string>>>(ref values, ERPConfig.CACHE_TIMEOUT, true, null, "ERP_DataHelper_OptionsContentTree", type))
        {
            if (cs.LoadData)
            {
                // init values list
                values = new LinkedList<KeyValuePair<string, string>>();

                String documentTypes = type;
                bool combineWithDefaultCulture = true;
                String whereCondition = "DocumentMenuItemHideInNavigation = 0";
                String orderByCondition = "NodeLevel, NodeOrder";
                int maxRelativeLevel = 4;
                bool onlyPublished = true;
                int limit = 100;

                // select nodes
                TreeProvider provider = new TreeProvider();
                DataSet dsCategories = provider.SelectNodes(SiteContext.CurrentSiteName, "/%", LocalizationContext.PreferredCultureCode, combineWithDefaultCulture, documentTypes, whereCondition, orderByCondition, maxRelativeLevel, onlyPublished, limit);

                if (!DataHelper.DataSourceIsEmpty(dsCategories))
                {
                    foreach (DataRow row in dsCategories.Tables[0].Rows)
                    {
                        //values.AddLast(new KeyValuePair<string, string>(ValidationHelper.GetString(row[key], ""), ValidationHelper.GetString(row[value], "") + " (" + row[key] + ")"));
                        values.AddLast(new KeyValuePair<string, string>(ValidationHelper.GetString(row[key], ""), ValidationHelper.GetString(row[value], "")));
                    }
                }

                // store in cache
                cs.Data = values;
            }
        }

        return new LinkedList<KeyValuePair<string, string>>(values);
    }

    /// <summary>
    /// Returns price for ad for selected location, solution type and period.
    /// The price is loaded pro custom table and NULL is returned in case the price can not be found.
    /// </summary>
    /// <param name="locationId"></param>
    /// <param name="solutionTypeId"></param>
    /// <param name="periodId"></param>
    /// <returns></returns>
    public static double? GetAdPrice(int locationId, int solutionTypeId, int periodId)
    {
        GeneralConnection gc = ConnectionHelper.GetConnection();
        QueryDataParameters parameters = new QueryDataParameters();
        parameters.Add("@Location", locationId);
        parameters.Add("@SolutionType", solutionTypeId);
        parameters.Add("@Period", periodId);
        var queryInfo = QueryInfoProvider.GetQueryInfo("ERP.AdsPricelist.selectprice");
        object data = gc.ExecuteScalar(new QueryParameters(queryInfo, parameters, null));
        double price = ValidationHelper.GetDouble(data, -1);

        if (price < 0)
        {
            return null;
        }

        return price;
    }

    public static double? GetAppContactPrice(int solutionTypeId, int periodId)
    {
        GeneralConnection gc = ConnectionHelper.GetConnection();
        QueryDataParameters parameters = new QueryDataParameters();
        parameters.Add("@SolutionType", solutionTypeId);
        parameters.Add("@Period", periodId);
        var queryInfo = QueryInfoProvider.GetQueryInfo("ERP.AppContactPricelist.selectprice");
        object data = gc.ExecuteScalar(new QueryParameters(queryInfo, parameters,  null));
        double price = ValidationHelper.GetDouble(data, -1);

        if (price < 0)
        {
            return null;
        }

        return price;
    }

    /// <summary>
    /// For selected period ID returns length in months
    /// </summary>
    /// <param name="periodID"></param>
    /// <returns></returns>
    public static int GetAdPeriodLength(int periodID)
    {
        GeneralConnection gc = ConnectionHelper.GetConnection();
        QueryDataParameters parameters = new QueryDataParameters();
        parameters.Add("@ID", periodID);
        var queryInfo = QueryInfoProvider.GetQueryInfo(ERPConfig.CUSTOM_TABLE_ADS_PERIOD + ".select");
        DataSet ds = gc.ExecuteQuery(new QueryParameters(queryInfo, parameters, null));
        if (ds != null && !DataHelper.DataSourceIsEmpty(ds))
        {
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                return ValidationHelper.GetInteger(item["ItemLength"], 0);
            }
        }

        return 0;
    }

    /// <summary>
    /// Creates review node alias path for selected nodeID application.
    /// Node with ID NodeID must be of ERP.Application document type
    /// </summary>
    /// <param name="nodeID"></param>
    /// <returns></returns>
    public static string GetReviewsDirectoryPath(int nodeID)
    {
        TreeNode node = TreeHelper.SelectSingleNode(nodeID);

        if (node == null || !node.NodeClassName.ToLower().Equals(ERPConfig.DOCTYPE_APPLICATION.ToLower()))
        {
            // node not found or it is not an application
            return null;
        }

        // has vendor access rights to the node?
        if (ValidationHelper.GetInteger(node.GetValue("AppVendorID"), 0) != ValidationHelper.GetInteger(MembershipContext.AuthenticatedUser.GetValue("UserVendorID"), -1))
        {
            // insufficient permissions - current user is not owner of the application
            return null;
        }

        // ok, vendor owns the app, show reviews
        return String.Format("{0}/{1}/%", node.NodeAliasPath, ERPConfig.APP_REVIEWS_DIR_NAME);
    }

    /// <summary>
    /// Creates reviews folder for selected application Node ID.
    /// </summary>
    /// <param name="parentNodeID"></param>
    /// <returns></returns>
    public static TreeNode CreateReviewsDirectory(int parentNodeID)
    {
        TreeProvider tp = new TreeProvider(MembershipContext.AuthenticatedUser);
        var parentNode=  tp.SelectSingleNode(parentNodeID);
        if (parentNode == null)
        {
            return null;
        }
        TreeNode node = TreeNode.New(ERPConfig.APP_REVIEWS_DIR_DOCTYPE);
        node.NodeName = ERPConfig.APP_REVIEWS_DIR_NAME;
        node.DocumentName = ERPConfig.APP_REVIEWS_DIR_NAME;
        node.IsSecuredNode = true; // secure the node
        node.DocumentCulture = DocumentContext.CurrentDocumentCulture.CultureCode;
        node.Insert(parentNode);

        return node;
    }

    /// <summary>
    /// Redirects with code 404.
    /// </summary>
    /// <param name="passCurrentURL">If requested URL should be passed as an argument to 404 page (404?aspxerrorpath=/original/path).</param>
    public static void Redirect404(bool passCurrentURL)
    {
        // application is not published and user is not its owner -> display 404 Not found
        string notFoundUrl = ValidationHelper.GetString(SettingsKeyInfoProvider.GetValue(SiteContext.CurrentSiteName + ".CMSPageNotFoundUrl"), "");
        if (!DataHelper.IsEmpty(notFoundUrl))
        {
            notFoundUrl = URLHelper.ResolveUrl(notFoundUrl);

            if (passCurrentURL)
            {
                // Escape special characters
                string paramValue = ScriptHelper.GetString(RequestContext.CurrentURL, false);

                // Add parameter about what page was originaly not found
                notFoundUrl = URLHelper.AddParameterToUrl(notFoundUrl, "aspxerrorpath", paramValue);
            }

            URLHelper.Redirect(notFoundUrl);
        }

        RequestHelper.Respond404();
    }

    /// <summary>
    /// Checks input text for occurences of predefined regular expressions.
    /// If any match is found, KeyValuePair<string,string> is returned, where key is description of the contact
    /// (such as Phone or Email) and value is the match (e.g. "test@example.com").
    /// Only first match is returned.
    /// </summary>
    /// <param name="text"></param>
    /// <returns></returns>
    public static KeyValuePair<string, string> ContainsContacts(string text)
    {
        LinkedList<KeyValuePair<string, string>> rules = new LinkedList<KeyValuePair<string, string>>();
        rules.AddLast(new KeyValuePair<string, string>("Phone", @"[ -\.\(\)\+0-9]{9,16}")); // phone number 
        rules.AddLast(new KeyValuePair<string, string>("Email", @"\b[a-zA-Z0-9\._%\+\-]+@[a-zA-Z0-9\.\-]+\.[a-zA-Z]{2,6}\b")); // email
        rules.AddLast(new KeyValuePair<string, string>("URL", @"((https?|ftp|file)\://)?([A-Za-z0-9\-]{5,100}\.)+[a-zA-Z]{2,6}(/[A-Za-z0-9\?\&\=;\+!'\(\)\*\-\._~%]*)*")); // URL

        foreach (KeyValuePair<string, string> rule in rules)
        {
            Match match = Regex.Match(text, rule.Value);
            if (match.Success)
            {
                return new KeyValuePair<string, string>(rule.Key, match.Value);
            }
        }

        return new KeyValuePair<string, string>();
    }

    /// <summary>
    /// For vendor ID returns set of CMS users connected to this vendor.
    /// </summary>
    /// <param name="vendorID"></param>
    /// <returns></returns>
    public static DataSet GetVendorUserAccounts(int vendorID)
    {
        // find vendors users accounts for the application
        return UserInfoProvider.GetUsers().WhereEquals("UserVendorID", vendorID);
    }

    /// <summary>
    /// Find vendor TreeNode by its vendor ID.
    /// </summary>
    /// <param name="vendorID"></param>
    /// <returns></returns>
    public static TreeNode GetVendorTreeNode(int vendorID)
    {
        var nodes = TreeHelper.SelectNodes("/%", true, ERPConfig.DOCTYPE_VENDOR, string.Format("VendorID = {0}", vendorID));
        if (nodes != null && nodes.Items.Count == 1)
        {
            return nodes.Items[0];
        }

        return null;
    }

    /// <summary>
    /// From application ID returns Node ID.
    /// </summary>
    /// <param name="appID"></param>
    /// <returns></returns>
    public static TreeNode GetApplicationNode(int appID)
    {
        return GetNode(ERPConfig.DOCTYPE_APPLICATION, appID);
    }

    /// <summary>
    /// From ad ID returns Node ID.
    /// </summary>
    /// <param name="adID"></param>
    /// <returns></returns>
    public static TreeNode GetAdNode(int adID)
    {
        return GetNode(ERPConfig.DOCTYPE_AD, adID);
    }

    /// <summary>
    /// Selected record of document type "type" from database and returns its Node.
    /// </summary>
    /// <param name="type">Document type to be selected</param>
    /// <param name="id">ID argument of the document (not Node ID)</param>
    /// <returns></returns>
    public static TreeNode GetNode(string type, int id)
    {
        QueryDataParameters parameters = new QueryDataParameters();
        parameters.Add("@ID", id);

        GeneralConnection con = ConnectionHelper.GetConnection();
        var queryInfo = QueryInfoProvider.GetQueryInfo(type + ".select");
        DataSet ds = con.ExecuteQuery(new QueryParameters(queryInfo, parameters,  null));
        if (DataHelper.DataSourceIsEmpty(ds))
        {
            return null;
        }

        return TreeNode.New(type, ds.Tables[0].Rows[0]);
    }

    /// <summary>
    /// From given dataset selects only one value and that value is returned in LinkedList.
    /// Example: GetListByColumnName(ds, "name") will return all "names" from the DataSet.
    /// </summary>
    /// <param name="ds"></param>
    /// <param name="columnName"></param>
    /// <returns></returns>
    public static LinkedList<string> GetListByColumnName(DataSet ds, string columnName)
    {
        LinkedList<string> result = new LinkedList<string>();

        if (DataHelper.DataSourceIsEmpty(ds))
        {
            return result;
        }

        foreach (DataRow item in ds.Tables[0].Rows)
        {
            // get value from row and add to result linked list
            string value = ValidationHelper.GetString(item[columnName], null);

            result.AddLast(value);
        }

        return result;
    }

    /// <summary>
    /// Sends email to given list of mailboxes.
    /// The email is based on given template with macro resolving.
    /// </summary>
    /// <param name="eti"></param>
    /// <param name="mcr"></param>
    /// <param name="emails"></param>
    public static void SendEmailToVendor(EmailTemplateInfo eti, MacroResolver mcr, LinkedList<string> emails)
    {
        // compose message
        EmailMessage msg = new EmailMessage();
        msg.EmailFormat = EmailFormatEnum.Both;
        msg.From = eti.TemplateFrom;
        msg.Subject = eti.TemplateSubject;
        msg.BccRecipients = eti.TemplateBcc;
        msg.CcRecipients = eti.TemplateCc;

        foreach (var recipient in emails)
        {
            if (DataHelper.IsEmpty(recipient))
            {
                continue;
            }

            msg.Recipients = recipient;

            // send
            EmailSender.SendEmailWithTemplateText(SiteContext.CurrentSiteName, msg, eti, mcr, false);
        }

    }
}