using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.IO;
using System.Windows.Forms;

namespace PwdManager
{
    class DBManager
    {
        private static string connStr = "./pwdmanager.db";
        //创建数据库
        public static void CreateDB()
        {
            SQLiteConnection.CreateFile("./pwdmanager.db");
        }

        public static string getConnStr()
        {
            if (!File.Exists("./pwdmanager.db"))
            {
                CreateDB();
                initDB();
            }
            SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
            connstr.DataSource = connStr;
            return connstr.ToString();
        }

        //数据库初始化
        public static bool initDB()
        {
            ExecuteSql("create table user(username varchar(20) primary key, password varchar(20));");
            ExecuteSql("create table records(name varchar(20) primary key, username varchar(100), account varchar(100), userpassword varchar(100), webaddr varchar(100), picpath varchar(100));");
            if (ExecuteSql("insert into user values('qian_f', 'qian_f');") != 1)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        //执行Sql语句
        private static int ExecuteSql(string sqlStr)
        {
            using (SQLiteConnection conn = new SQLiteConnection(getConnStr()))
            {
                try
                {
                    conn.Open();
                    SQLiteCommand comm = conn.CreateCommand();
                    comm.CommandText = sqlStr;
                    comm.CommandType = System.Data.CommandType.Text;
                    return comm.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                    return -1;
                }
            }
        }

        //sql查询语句
        private static SQLiteDataReader ExecQuery(string queryStr)
        {
            using (SQLiteConnection conn = new SQLiteConnection(getConnStr()))
            {
                conn.Open();
                SQLiteCommand comm = conn.CreateCommand();
                comm.CommandText = queryStr;
                comm.CommandType = System.Data.CommandType.Text;
                return comm.ExecuteReader();
            }
        }

        //插入记录
        public static bool InsetRecord(string name, string username, string account, string password, string webaddr)
        {
            string str = "insert into records values('" + name + "', '" + username + "', '" + account + "', '" + password + "', '" + webaddr + "', '');";
            if (ExecuteSql(str) != 1)
            {
                MessageBox.Show("添加记录失败！");
                return false;
            }
            else return true;
        }

        //删除记录
        public static bool DelRecord(string name)
        {
            string str = "delete from records where name = '" + name + "';";
            if (ExecuteSql(str) != 1)
            {
                MessageBox.Show("删除记录失败！");
                return true;
            }
            else
            {
                return false;
            }
        }

        //修改记录
        public static bool UpdateRecord(string name, string username, string account, string password, string webaddr)
        {
            string str = "update records set username='" + username + "', account='" + account + "', userpassword='" + password + "', webaddr='" + webaddr + "' where name='" + name + "';";
            if (ExecuteSql(str) != 1)
            {
                MessageBox.Show("修改记录失败！");
                return false;
            }
            else return true;
        }

        //校验登录用户和密码
        public static bool CheckLogin(string name, string password)
        {
            using (SQLiteConnection conn = new SQLiteConnection(getConnStr()))
            {
                conn.Open();
                string query = "select password from user where username='" + name + "';";
                SQLiteCommand comm = conn.CreateCommand();
                comm.CommandText = query;
                comm.CommandType = System.Data.CommandType.Text;
                SQLiteDataReader reader = comm.ExecuteReader();
                if (reader.HasRows == false)
                {
                    return false;
                }
                reader.Read();
                if (reader[0].ToString().Equals(password))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }
    }
}