using OnlineStore.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DeviceLibrary
{
    public class databaseProc
    {
        SQLiteHelper sh = new SQLiteHelper();
        IdWorker id = new IdWorker(1, 1);

        static databaseProc _databaseProc;

        public static databaseProc Current
        {
            get
            {
                if (_databaseProc == null)
                {
                    _databaseProc = new databaseProc();
                }
                return _databaseProc;
            }
        }
        public long GetID() {
            return id.nextId();
        }
        private databaseProc()
        {
            SQLiteHelper.SetConnectionString("sys.db");

            string sql;

            //添加MaterialQuantity表
            sql = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='ReelInfo';";
            int hastable = (int)(long)sh.ExecuteScalar(sql);
            if (hastable == 0)
            {
                sql = @"
CREATE TABLE ""ReelInfo"" (
  ""ID"" integer NOT NULL COLLATE BINARY,
  ""PN"" text,
  ""RI"" TEXT,
  ""ISNG"" integer,
  ""NGMSG"" TEXT,
  ""QTY"" integer,
  ""LBLState"" TEXT,
  ""OMSG"" TEXT,
  ""2D_Barcode"" TEXT,
  ""DateTime"" date,
  PRIMARY KEY (""ID"")
);
                ";
                sh.ExecuteNonQuery(sql);
            }
            else
            {
                //添加后期添加的字段
                sql = "select sql from sqlite_master where name='ReelInfo';";
                string sc = (string)sh.ExecuteScalar(sql);
                sc = sc.Replace("\"", "");
                if (!sc.Contains("Slot integer"))
                {
                    sql = "alter table ReelInfo add column Slot integer;";
                    //sh.ExecuteNonQuery(sql);
                }
                if (!sc.Contains("imgfileid varchar(80)"))
                {
                    sql = "alter table MaterialQuantity add column imgfileid varchar(80);";
                    //sh.ExecuteNonQuery(sql);
                }
            }

        }
        public void InsertOrUpdateRI(long uid,string PN,string RI,bool isng ,string ngmsg, int quantity, string lblstate, string OMSG, string tdbarcode)
        {
            string sql = "SELECT COUNT(*) FROM ReelInfo WHERE ID = "+ uid.ToString();
            long sc = (long)sh.ExecuteScalar(sql);
            if (sc == 0)
            {
                sql = "INSERT INTO ReelInfo VALUES(@id, @PN, @RI, @isng, @ngmsg,@quantity,@lblstate,@OMSG,@tdbarcode,@datetime)";
                SQLiteParameter[] para = new SQLiteParameter[] {
                    new SQLiteParameter("@id",uid),
                    new SQLiteParameter("@PN",PN),
                    new SQLiteParameter("@RI",RI),
                    new SQLiteParameter("@isng",isng),
                    new SQLiteParameter("@ngmsg",ngmsg),
                    new SQLiteParameter("@quantity",quantity),
                    new SQLiteParameter("@lblstate",lblstate),
                    new SQLiteParameter("@OMSG",OMSG),
                    new SQLiteParameter("@tdbarcode",tdbarcode),
                    new SQLiteParameter("@datetime",DateTime.Now)
                };
                sh.ExecuteNonQuery(sql, para);
            }
            else {
                sql = @"
UPDATE ReelInfo 
SET
ISNG=@ISNG,
NGMSG=@NGMSG,
QTY=@QTY,
LBLState=@LBLState,
OMSG=@OMSG
WHERE
id =@id";
                SQLiteParameter[] para = new SQLiteParameter[] {
                    new SQLiteParameter("@id",uid),
                    new SQLiteParameter("@ISNG",isng),
                    new SQLiteParameter("@NGMSG",ngmsg),
                    new SQLiteParameter("@QTY",quantity),
                    new SQLiteParameter("@LBLState",lblstate),
                    new SQLiteParameter("@OMSG",OMSG),
                };
                sh.ExecuteNonQuery(sql, para);
            }            
        }

        public void InsertLog(string device, string action, object state, string log = "")
        {
            string sql = "INSERT INTO ReelInfo VALUES(NULL, @device,@action, @state, @log,@datetime,NULL)";
            SQLiteParameter[] para = new SQLiteParameter[] {
            new SQLiteParameter("@device",device),
            new SQLiteParameter("@action",action),
            new SQLiteParameter("@state",state.ToString()),
            new SQLiteParameter("@log",log),
            new SQLiteParameter("@datetime",DateTime.Now)
            };
            sh.ExecuteNonQuery(sql, para);
        }
        public long insertImage(byte[] b)
        {
            long ids = id.nextId();
            string sql = "INSERT INTO img VALUES(@id, @imgblob, @datetime)";
            SQLiteParameter[] para = new SQLiteParameter[] {
            new SQLiteParameter("@id",ids),
            new SQLiteParameter("@imgblob",b),
            new SQLiteParameter("@datetime",DateTime.Now)
            };
            sh.ExecuteNonQuery(sql, para);
            return ids;

        }
        public Image getImg(long ids)
        {
            string sql = @"SELECT  imgblob FROM img WHERE id = @id LIMIT 1";

            SQLiteParameter[] para = new SQLiteParameter[] {
                new SQLiteParameter("@id",ids)
                };
            byte[] buffer = (byte[])sh.ExecuteScalar(sql, para);
            MemoryStream ms = new MemoryStream(buffer);
            Image i = Image.FromStream(ms);
            ms.Close();
            return i;

        }
        public DataTable GetDatabydate(DateTime startData, DateTime endDate)
        {
            string where = "";
            string sql = @"SELECT * FROM ReelInfo WHERE
(datetime BETWEEN @startData AND @endDate) " + where + " ORDER BY datetime ASC";

            SQLiteParameter[] para = new SQLiteParameter[] {
            new SQLiteParameter("@startData",startData),
            new SQLiteParameter("@endDate",endDate)
            };
            DataTable dt = sh.ExecuteQuery(sql, para);
            return dt;
        }
        public DataTable GetLogbydate(DateTime startData, DateTime endDate)
        {
            string sql = @"select device,action,state,log,datetime from logs WHERE datetime BETWEEN @startData AND @endDate ORDER BY datetime desc";

            SQLiteParameter[] para = new SQLiteParameter[] {
            new SQLiteParameter("@startData",startData),
            new SQLiteParameter("@endDate",endDate)
            };
            DataTable dt = sh.ExecuteQuery(sql, para);
            return dt;
        }
        public DataTable GetDaySUM(string user)
        {

            string sql = "SELECT count(id) as count,substr(datetime,1,10) as date FROM MaterialQuantity GROUP BY date ORDER BY datetime desc";

            if (!string.IsNullOrEmpty(user))
                sql = "SELECT count(id) as count,substr(datetime,1,10) as date FROM MaterialQuantity where JobNumber=@JobNumber GROUP BY date ORDER BY datetime desc";
            SQLiteParameter[] para = new SQLiteParameter[] {
                new SQLiteParameter("@JobNumber",user)
            };
            DataTable dt = sh.ExecuteQuery(sql, para);
            return dt;
        }
        public bool AddUser(string username, string jobnumber, string password, int role)
        {
            if (string.IsNullOrEmpty(username))
                username = jobnumber;
            string sql = "INSERT INTO Users VALUES(@username, @jobnumber, @password,@role,@createdate,@lastlogindate)";
            SQLiteParameter[] para = new SQLiteParameter[] {
                new SQLiteParameter("@username",username),
                new SQLiteParameter("@jobnumber",jobnumber),
                new SQLiteParameter("@password",password),
                new SQLiteParameter("@role",role),
                new SQLiteParameter("@createdate",DateTime.Now),
                new SQLiteParameter("@lastlogindate",DateTime.Now)
            };
            int c = sh.ExecuteNonQuery(sql, para);
            if (c == 1)
            {
                return true;
            }
            else
                return false;
        }
        public bool TestUser(string username, string jobnumber, string password, out int role)
        {
            role = 0;
            string sqlpart = " jobnumber = @jobnumber ";
            if (string.IsNullOrEmpty(jobnumber))
                sqlpart = " username = @username ";

            SQLiteParameter[] para = new SQLiteParameter[] {
                new SQLiteParameter("@username",username),
                new SQLiteParameter("@jobnumber",jobnumber),
                new SQLiteParameter("@password",password),
            };

            string sql = @"select username,jobnumber,role from users WHERE " + sqlpart + " and password=@password";
            var dt = sh.ExecuteQuery(sql, para);
            if (dt.Rows.Count == 0)
            {
                return false;
            }
            else
            {
                role = (int)(long)dt.Rows[0]["role"];
                sql = @"update users set lastlogindate = @lastlogindate WHERE " + sqlpart;
                para = new SQLiteParameter[] {
                    new SQLiteParameter("@username",username),
                    new SQLiteParameter("@jobnumber",jobnumber),
                    new SQLiteParameter("@lastlogindate",DateTime.Now),
                };
                sh.ExecuteNonQuery(sql, para);
                return true;
            }
        }
        public void ModifyUser(string username, string jobnumber, string password, int role)
        {
            string sqlpart = " jobnumber = @jobnumber ";
            if (string.IsNullOrEmpty(jobnumber))
                sqlpart = " username = @username ";

            string sql = @"update users set password = @password,jobnumber=@jobnumber,role=@role WHERE " + sqlpart;
            SQLiteParameter[] para = new SQLiteParameter[] {
                new SQLiteParameter("@username",username),
                new SQLiteParameter("@jobnumber",jobnumber),
                new SQLiteParameter("@password",password),
                new SQLiteParameter("@role",role)
            };
            sh.ExecuteNonQuery(sql, para);
        }
        public void DeleteUser(string username)
        {
            string sql = @"delete from Users  WHERE username = @username";
            SQLiteParameter[] para = new SQLiteParameter[] {
                new SQLiteParameter("@username",username),
            };
            sh.ExecuteNonQuery(sql, para);
        }
        public DataTable GetUserList()
        {
            string sql = @"select * from users";
            return sh.ExecuteQuery(sql);
        }
        public void backup()
        {
            Directory.CreateDirectory("dbbackup");
            var n = DateTime.Now.Month - 1;
            var bckfile = "dbbackup\\Sys-" + n + ".bck";
            if (File.Exists(bckfile))
            {
                return;
            }
            File.Copy("sys.db", bckfile);
            var d = DateTime.Now.AddMonths(-1).ToString("yyyy-MM-01");
            //string sql = "delete FROM MaterialQuantity WHERE datetime < '"+ d + "';delete FROM logs WHERE datetime < '" + d + "';";
            //sh.ExecuteNonQuery(sql);
        }



    }

    public class IdWorker
    {
        private long workerId;
        private long datacenterId;
        private long sequence = 0L;

        private static long twepoch = 1288834974657L;

        private static long workerIdBits = 5L;
        private static long datacenterIdBits = 5L;
        private static long maxWorkerId = -1L ^ (-1L << (int)workerIdBits);
        private static long maxDatacenterId = -1L ^ (-1L << (int)datacenterIdBits);
        private static long sequenceBits = 12L;

        private long workerIdShift = sequenceBits;
        private long datacenterIdShift = sequenceBits + workerIdBits;
        private long timestampLeftShift = sequenceBits + workerIdBits + datacenterIdBits;
        private long sequenceMask = -1L ^ (-1L << (int)sequenceBits);

        private long lastTimestamp = -1L;
        private static object syncRoot = new object();

        public IdWorker(long workerId, long datacenterId)
        {

            // sanity check for workerId
            if (workerId > maxWorkerId || workerId < 0)
            {
                throw new ArgumentException(string.Format("worker Id can't be greater than %d or less than 0", maxWorkerId));
            }
            if (datacenterId > maxDatacenterId || datacenterId < 0)
            {
                throw new ArgumentException(string.Format("datacenter Id can't be greater than %d or less than 0", maxDatacenterId));
            }
            this.workerId = workerId;
            this.datacenterId = datacenterId;
        }

        public long nextId()
        {
            lock (syncRoot)
            {
                long timestamp = timeGen();

                if (timestamp < lastTimestamp)
                {
                    throw new ApplicationException(string.Format("Clock moved backwards.  Refusing to generate id for %d milliseconds", lastTimestamp - timestamp));
                }

                if (lastTimestamp == timestamp)
                {
                    sequence = (sequence + 1) & sequenceMask;
                    if (sequence == 0)
                    {
                        timestamp = tilNextMillis(lastTimestamp);
                    }
                }
                else
                {
                    sequence = 0L;
                }

                lastTimestamp = timestamp;

                return ((timestamp - twepoch) << (int)timestampLeftShift) | (datacenterId << (int)datacenterIdShift) | (workerId << (int)workerIdShift) | sequence;
            }
        }

        protected long tilNextMillis(long lastTimestamp)
        {
            long timestamp = timeGen();
            while (timestamp <= lastTimestamp)
            {
                timestamp = timeGen();
            }
            return timestamp;
        }

        protected long timeGen()
        {
            return (long)(DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)).TotalMilliseconds;
        }
    }
}