using System;
using System.Collections.Generic;
using System.Linq;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate;
using NHibernate.Cfg;
using log4net;
using SharedDatabase.Entities;

namespace SharedDatabase
{
    public static class TracingDB
    {
        static readonly ILog log = LogManager.GetLogger(typeof(TracingDB));

        /// <summary>
		/// Current session factory for the last used connection string or null
		/// </summary>
		public static ISessionFactory SessionFactory;

        public static ISession Session;


        /// <summary>
        /// Connection string for all sessions
        /// </summary>
        private static string connectionString;
        public static string ConnectionString
        {
            get { return connectionString; }
            set
            {
                if (value != connectionString)
                {
                    connectionString = value;
                    SessionFactory = null;          /// Clear SessionFactory on connection string change
                }
            }
        }


        /// <summary>
        /// NHibernate session factory (to create the database session 'SessionFactory')
        /// </summary>
        /// <returns>A database session</returns>
		static ISessionFactory CreateSessionFactory()
        {
			return CreateSessionFactory(false);
		}

        /// <summary>
        /// NHibernate session factory (to create the database session 'SessionFactory')
        /// </summary>
        /// <returns>A database session</returns>
		public static ISessionFactory CreateSessionFactory(bool createDB)
        {
			FluentConfiguration cfg = Fluently
				.Configure()
				.Database(MySQLConfiguration.Standard.ConnectionString(connectionString))
				.Mappings(m => m.FluentMappings.AddFromAssemblyOf<Process>());

			if (createDB)
            {
                return cfg.ExposeConfiguration(BuildSchemaCreate).BuildSessionFactory();
            }
            else
            {
                return cfg.ExposeConfiguration(BuildSchema).BuildSessionFactory();
            }
        }

        public delegate void BuildSchemaDlgt(Configuration config);

        public static void BuildSchema(Configuration config)
        {
            /// This NHibernate tool takes a configuration (with mapping info in)
            /// and exports a database schema from it
            new NHibernate.Tool.hbm2ddl.SchemaExport(config).SetOutputFile("db_schema");
        }

        static void BuildSchemaCreate(Configuration config)
        {
            /// This NHibernate tool takes a configuration (with mapping info in)
            /// and exports a database schema from it
            new NHibernate.Tool.hbm2ddl.SchemaExport(config).Create(true, true);
        }

		/// Create a NHibernate session for the given database
		public static ISession CreateSession(string connectionString)
		{
            ConnectionString = connectionString;    /// Clears session factory on connction string change

			if (SessionFactory == null)
                SessionFactory = CreateSessionFactory();

			return SessionFactory.OpenSession();
		}


        /// <summary>
        /// Create an empty database.
		/// Database contains only the user 'admin' and the control board component 'CB'.
        /// </summary>
        /// <param name="dbType">DBType.SQLite or DBType.MySql</param>
        /// <param name="connectionString">Connection string</param>
        /// <returns>true=success, false=error</returns>
        public static bool CreateEmptyDB(string connectionString)
        {
            ConnectionString = connectionString;

			ISessionFactory sessionFactory = CreateSessionFactory(true);
            if (sessionFactory == null) return false;

            /// Populate the database
            using (var session = sessionFactory.OpenSession())
            {
				//using (var transaction = session.BeginTransaction())
				//{
				//    var part1 = new Part("flow tube", CodeType.UniqueNr, CodeForm.QRCode, 0);
				//    session.SaveOrUpdate(part1);
				//    transaction.Commit();
				//}
            }

            return true;
        }


        /// <summary>
        /// Save a reference record and related records into the database.
        /// </summary>
        /// <param name="session">DB session</param>
        /// <param name="refRecord">Referece record</param>
        public static void SaveRecords(ISession session, ReferenceRecord refRecord)
        {
            log.Info("Saving data to MySQL database");

            session.SaveOrUpdate(refRecord);
            log.InfoFormat("   ReferenceRecord : {0}", refRecord);

            foreach (var r in refRecord.Records)
            {
                session.SaveOrUpdate(r);
                log.InfoFormat("      Record : {0}", r);
            }
        }


        /// <summary>
        /// Delete a reference record and related records from the database.
        /// </summary>
        /// <param name="session">DB session</param>
        /// <param name="refRecord">Reference record</param>
        public static void DeleteRecords(ISession session, ReferenceRecord refRecord)
        {
            log.Info("Deleting data from MySQL database");

            foreach (var r in refRecord.Records)
            {
                session.Delete(r);
                log.InfoFormat("      Record : {0}", r);
            }

            session.Delete(refRecord);
            log.InfoFormat("   ReferenceRecord : {0}", refRecord);
        }


        /// <summary>
        /// Find a reference record of product specified by one of unique S/N-s (code)
        /// and get it's workflow and the current order.
        /// </summa
        /// <param name="session">DB session</param>
        /// <param name="code">One of unique serial numbers identifying the product</param>
        /// <param name="codeIx">1,2,3 or 4</param>
        /// <returns>Workflow of the specified part</returns>
        public static ReferenceRecord FindReferenceRecord(ISession session, string code, int codeIx)
		{
			if (string.IsNullOrEmpty(code)) return null;

			try
			{
                /// Read all already existing reference records with Code# == code, referenceRecords[0] will be the most recent one                
                IQueryOver<ReferenceRecord,ReferenceRecord> query = session.QueryOver<ReferenceRecord>();                
                switch (codeIx)
                {
                    default:
                    case 1: query = query.Where(rr => rr.Code1 == code); break;
                    case 2: query = query.Where(rr => rr.Code2 == code); break;
                    case 3: query = query.Where(rr => rr.Code3 == code); break;
                    case 4: query = query.Where(rr => rr.Code4 == code); break;
                }
                //var referenceRecords = query.OrderBy(rr => rr.Timestamp).Desc.List();
                var referenceRecords = query.List();

				if (referenceRecords.Count == 1) return referenceRecords[0];

                return null;
			}
			catch
			{
                return null;
			}
		}

        /// <summary>
        /// Updates processes, processDictionary and workstepsDictionary
        /// </summary>
        /// <param name="session">DB session</param>
        public static IList<WorkflowSummary> ReadWorkflowSummariesFromDB(ISession session,
                                                                         string workstepPattern,
                                                                         bool readDeactivatedWorkflows = false)
        {
            IList<Process> workflows;
            if (readDeactivatedWorkflows)
            {
                workflows = session.QueryOver<Process>().Where(pr => ((pr.ReleaseStatus == ReleaseStatus.Released) ||
                                                                      (pr.ReleaseStatus == ReleaseStatus.ReleasedActive) ||
                                                                      (pr.ReleaseStatus == ReleaseStatus.ToBeApproved) ||
                                                                      (pr.ReleaseStatus == ReleaseStatus.Deactivated)
                                                                      )).List();
            }
            else
            {
                workflows = session.QueryOver<Process>().Where(pr => ((pr.ReleaseStatus == ReleaseStatus.Released) ||
                                                                      (pr.ReleaseStatus == ReleaseStatus.ReleasedActive) ||
                                                                      (pr.ReleaseStatus == ReleaseStatus.ToBeApproved)
                                                                      )).List();
            }

            var listOfWFSummaries = new List<WorkflowSummary>();

            foreach (var wf in workflows)
            {
                var wSum = new WorkflowSummary(wf);

                var workstep = wf.Worksteps.FirstOrDefault(x => x.Name.ToLower().Contains(workstepPattern.ToLower()));
                if (workstep != null)
                {
                    wSum.WorkstepName = workstep.Name;
                    int previousStepNr = workstep.WorkstepNr - 1;
                    var previousStep = (previousStepNr < 1) ? null : wf.Worksteps.FirstOrDefault(x => x.WorkstepNr == previousStepNr);
                    if (previousStep != null) wSum.PreviousWorkstepName = previousStep.Name;
                }
                var part1 = wf.Parts.FirstOrDefault(x => x.StepNumber == 1);
                var part2 = wf.Parts.FirstOrDefault(x => x.StepNumber == 2);
                if (part1 != null) wSum.Part1Name = part1.Name;
                if (part2 != null) wSum.Part2Name = part2.Name;

                listOfWFSummaries.Add(wSum);
            }

            return listOfWFSummaries;
        }

        /// <summary>
        /// Finds a workstep and a part to be verified (typically a previous workstep and one of its parts).
        /// Updates this.verifiedWorkstep, this.verifiedPart and this.verifyReferencePart.
        /// Returns false if there is no workstep or part to be verified.
        /// </summary>
        /// <param name="dbSession">MySQL DB session</param>
        /// <param name="process">Process to be analyzed</param>
        /// <param name="workstep">Workstep to be analyzed</param>
        /// <returns>true if there is a verified workstep and part</returns>
        public static ScanVerificationInfo AnalyzeProcess(ISession dbSession, Process process, Workstep workstep)
        {
            try
            {
                if (workstep != null && workstep.ReferencePart != null)
                {
                    IList<Workstep> worksteps = dbSession.QueryOver<Workstep>()
                                                         .Where(x => (x.Workflow == process))
                                                         .Where(x => (x.WorkstepNr < workstep.WorkstepNr))
                                                         .OrderBy(x => x.WorkstepNr).Asc
                                                         .List();

                    for (int i = worksteps.Count - 1; i >= 0; i--)
                    {
                        if (worksteps[i].ReferencePart != null)
                        {
                            if (worksteps[i].ReferencePart.Id == workstep.ReferencePart.Id)
                            {
                                /// Compare reference part of the current workstep and reference part of another workstep
                                return new ScanVerificationInfo(worksteps[i], true, workstep.ReferencePart);
                            }

                            foreach (var thisStepPart in workstep.Parts)
                            {
                                if (worksteps[i].ReferencePart.Id == thisStepPart.Id)
                                {
                                    /// Compare  (non-reference) part of the current workstep and reference part of another workstep
                                    return new ScanVerificationInfo(worksteps[i], true, worksteps[i].ReferencePart);
                                }
                            }
                        }

                        foreach (var part in worksteps[i].Parts)
                        {
                            bool isUnique = (part.CodeLocation == CodeLocation.OnPart) && ((part.CodeType == (sbyte)CodeType.UniqueNr)
                                                                                        || (part.CodeType == (sbyte)CodeType.FlowtubeNr)
                                                                                        || (part.CodeType == (sbyte)CodeType.FlowtubeNrLU));
                            if (isUnique)
                            {
                                if (part.Id == workstep.ReferencePart.Id)
                                {
                                    /// Enable checking reference part
                                    return new ScanVerificationInfo(worksteps[i], false, part); /// !!!!!!!!!!!!!!!!!!
                                }

                                foreach (var thisStepPart in workstep.Parts)
                                {
                                    if (part.Id == thisStepPart.Id)
                                    {
                                        return new ScanVerificationInfo(worksteps[i], false, part);
                                    }
                                }
                            }
                        }
                    }
                }

                log.ErrorFormat("AnalyzeProcess(session , {0}, {1}) no ver. info found, returning 'null'",
                                (process != null) ? process.Name : "null",
                                (workstep != null) ? workstep.Name : "null");
                return null;
            }
            catch (Exception exc)
            {
                log.ErrorFormat("AnalyzeProcess(session , {0}, {1}) failed : {2}", process.Name, workstep.Name, exc.Message);
                return null;
            }
        }

        /// <summary>
        /// Obsolete, use WorkplaceRegistration class instead
        /// </summary>
        public static bool RegisterWorkplaceObsolete(ISession session, string workplace, string user, string ipAddress, string processName, string workstepName, DateTime valiUntil)
        {
            try
            {
                IList<WorkplaceRegistration> wpRegs = session.QueryOver<WorkplaceRegistration>()
                                                             .Where(x => (x.Workplace == workplace))
                                                             .List();
                if (wpRegs.Count == 0)
                {
                    WorkplaceRegistration wpReg = new WorkplaceRegistration(workplace, user, ipAddress, processName, workstepName);
                    wpRegs.Add(wpReg);
                    session.SaveOrUpdate(wpReg);
                    log.InfoFormat("RegisterWorkplace(., {0}, {1}, ...) successful (new)", workplace, user);
                    return true;
                }
                else if (wpRegs.Count == 1)
                {
                    WorkplaceRegistration wpReg = wpRegs[0];

                    wpReg.Active = true;
                    wpReg.TimeStamp = DateTime.Now;
                    wpReg.ValidUntil = valiUntil;
                    wpReg.UserName = user;
                    wpReg.IPAddress = ipAddress;
                    wpReg.ProcessName = processName;
                    wpReg.WorkstepName = workstepName;

                    session.SaveOrUpdate(wpReg);
                    log.InfoFormat("RegisterWorkplace(., {0}, {1}, ...) successful", workplace, user);
                    return true;
                }
                else
                {
                    log.ErrorFormat("RegisterWorkplace(., {0}, {1}, ...) failed", workplace, user);
                    return false;
                }
            }
            catch (Exception e)
            {
                log.ErrorFormat("RegisterWorkplace(., {0}, {1}, ...) exception: {2}", workplace, user, e.Message);
                return false;
            }
        }

        /// <summary>
        /// Obsolete, use WorkplaceRegistration class instead
        /// </summary>
        public static bool UnregisterWorkplaceObsolete(ISession session, string workplace)
        {
            try
            {
                IList<WorkplaceRegistration> wpRegs = session.QueryOver<WorkplaceRegistration>()
                                                             .Where(x => (x.Workplace == workplace))
                                                             .List();
                if (wpRegs.Count == 1)
                {
                    WorkplaceRegistration wpReg = wpRegs[0];

                    wpReg.Active = false;
                    wpReg.TimeStamp = DateTime.Now;

                    session.SaveOrUpdate(wpReg);
                    log.InfoFormat("UnregisterWorkplace(., {0}) successful", workplace);
                    return true;
                }
                else
                {
                    log.ErrorFormat("UnregisterWorkplace(., {0}) failed", workplace);
                    return false;
                }
            }
            catch (Exception e)
            {
                log.ErrorFormat("UnregisterWorkplace(., {0}) exception: {1}", workplace, e.Message);
                return false;
            }
        }
    }
}