using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.Windows.Forms;
using WindowsFormsApp1.entity.tb;
using WindowsFormsApp1.entity;

namespace WindowsFormsApp1.utils
{
    class SQLiteDataBaseUtil
    {

        SQLiteConnection con;
        private static SQLiteDataBaseUtil instance;
        public static SQLiteDataBaseUtil getInstance() {
            if (instance == null)
            {
                SQLiteConnection.CreateFile("BKZ");//创建数据库
                instance = new SQLiteDataBaseUtil();
                instance.con = new SQLiteConnection("Data Source=BKZ;Version=3;");
                instance.init();
            }
            return instance;
        }


        //初始化数据库
        private void init() {
            CreateTBAccountTable();
            CreateLoginTable();
            CreateConfigTable();
        }



        /*
         * 
         * 淘宝账号管理
         * 
         */

        private void CreateTBAccountTable() {
            this.con = new SQLiteConnection("Data Source=record.sqlite;Version=3;");
            con.Open();
            string sql = "CREATE TABLE  IF NOT EXISTS tb_account_info (`index` int, accountName varchar(256), tbUid varchar(128), loginState int)";
            SQLiteCommand command = new SQLiteCommand(sql, con);
            command.ExecuteNonQuery();
            con.Close();
        }

        //添加本地淘宝账号
        public void AddTaoBaoAccount(TBAccountLogin account) {
            List<TBAccountLogin> list= GetByTBUid(account.TbUid);
            if (list != null && list.Count > 0)
                return;
            con.Open();
            string sql = "insert into tb_account_info (`index`,accountName, tbUid,loginState) values ("+ account .Index+ ", '"+ account .NickName+ "',"+account.TbUid+", "+(account.Login?1:0)+")";
            SQLiteCommand command = new SQLiteCommand(sql, con);
            command.ExecuteNonQuery();
            con.Close();
        }

        //掉线
        public void TBOffLine(int index) {
            con.Open();
            string sql = "update tb_account_info set loginState=0 where `index`=" + index;
            SQLiteCommand command = new SQLiteCommand(sql, con);
            command.ExecuteNonQuery();
            con.Close();
        }

        //上线
        public void TBOnLine(TBAccountLogin account) {
            AddTaoBaoAccount(account);
            con.Open();
            string sql = "update tb_account_info set loginState=1 where tbUid=" + account.TbUid;
            SQLiteCommand command = new SQLiteCommand(sql, con);
            command.ExecuteNonQuery();
            con.Close();
        }


        //根据淘宝UID查询
        public List<TBAccountLogin> GetByTBUid(String tbUid) {
            List<TBAccountLogin> list = new List<TBAccountLogin>();
            con.Open();
            string sql = "select * from tb_account_info where tbUid="+tbUid;
            if (tbUid == null) {
                sql = "select * from tb_account_info";
            }
            SQLiteCommand command = new SQLiteCommand(sql, con);
            SQLiteDataReader reader = command.ExecuteReader();
            while (reader.Read()) {
                TBAccountLogin account = new TBAccountLogin();
                account.NickName = reader["accountName"].ToString();
                account.Login = Convert.ToInt32(reader["loginState"]) == 1 ? true : false;
                account.TbUid = reader["tbUid"].ToString();
                account.Index =Convert.ToInt32(reader["index"].ToString());
                list.Add(account);
            }
            con.Close();
            return list;
        }

        /**
         * 
         * 登录账号管理
         * 
         */


        private void CreateLoginTable() {
            this.con = new SQLiteConnection("Data Source=record.sqlite;Version=3;");
            con.Open();
            string sql = "CREATE TABLE  IF NOT EXISTS account_info (`account` varchar(256) PRIMARY KEY, `token` varchar(256), expireTime varchar(64),login_state int)";
            SQLiteCommand command = new SQLiteCommand(sql, con);
            command.ExecuteNonQuery();
            con.Close();
        }


        //添加账户
        public void AddAccount(UserInfo userInfo) {
            DeleteByAccount(userInfo.Account);
            this.con = new SQLiteConnection("Data Source=record.sqlite;Version=3;");
            con.Open();
            string sql = "insert into  account_info(`account`,`token`,expireTime,login_state) values('" + userInfo.Account+ "','"+ userInfo.Token+ "','"+ TimeUtil.GetGeneralTime(userInfo.SdljShareExpireTime,"yyyyMMdd HH:mm:ss")+ "',1)";
            SQLiteCommand command = new SQLiteCommand(sql, con);
            command.ExecuteNonQuery();
            con.Close();
        }

        //删除账户
        public void DeleteByAccount(String account)
        {
            con.Open();
            string sql = "delete from account_info where `account`='" + account + "'";
            SQLiteCommand command = new SQLiteCommand(sql, con);
            command.ExecuteNonQuery();
            con.Close();
        }

        //查询账户
        public UserInfo GetLoginAccount()
        {
            con.Open();
            string sql = "select * from account_info where login_state=1 limit 1";
            SQLiteCommand command = new SQLiteCommand(sql, con);
            SQLiteDataReader reader = command.ExecuteReader();
            UserInfo user = new UserInfo();
            if (reader.Read())
            {
                user.Account = reader["account"].ToString();
                user.Token = reader["token"].ToString();
               // user.SdljShareExpireTime =new DateTime( reader["expireTime"].ToString());
            }
            else {
                user = null;
            }
            reader.Close();
            con.Close();
            return user;
        }

        /*
         *
         *系统配置参数
         * 
         */


        private void CreateConfigTable()
        {
            this.con = new SQLiteConnection("Data Source=record.sqlite;Version=3;");
            con.Open();
            string sql = "CREATE TABLE  IF NOT EXISTS config (`key` varchar(256) PRIMARY KEY, `value` varchar(256))";
            SQLiteCommand command = new SQLiteCommand(sql, con);
            command.ExecuteNonQuery();
            con.Close();
        }


        //添加账户
        public void AddConfig(Config config)
        {
            DeleteConfig(config.Key);
            this.con = new SQLiteConnection("Data Source=record.sqlite;Version=3;");
            con.Open();
            string sql = "insert into  config(`key`,`value`) values('" + config.Key + "','" + config.Value + "')";
            SQLiteCommand command = new SQLiteCommand(sql, con);
            command.ExecuteNonQuery();
            con.Close();
        }

        //删除账户
        public void DeleteConfig(String key)
        {
            con.Open();
            string sql = "delete from config where `key`='" + key + "'";
            SQLiteCommand command = new SQLiteCommand(sql, con);
            command.ExecuteNonQuery();
            con.Close();
        }

        //查询账户
        public Config GetConfig(String key)
        {
            con.Open();
            string sql = "select * from config where `key`='"+ key + "'";
            SQLiteCommand command = new SQLiteCommand(sql, con);
            SQLiteDataReader reader = command.ExecuteReader();
            Config config = new Config();
            if (reader.Read())
            {
                config.Key = reader["key"].ToString();
                config.Value = reader["value"].ToString();
            }
            else
            {
                config = null;
            }
            reader.Close();
            con.Close();
            return config;
        }
















    }
}