using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;

namespace TCPServer
{
    public class Database
    {
        SqlCeConnection conn;
        SqlCeCommand cmd;

        public bool TableExists(string tablename)
        {
            cmd.CommandText = String.Format(@"SELECT * FROM INFORMATION_SCHEMA.TABLES where (TABLE_NAME = '{0}') and (TABLE_TYPE = 'TABLE')", tablename);
            SqlCeDataReader rdr = cmd.ExecuteReader();
            bool state = rdr.Read();
            rdr.Close();
            return state;
        }
                
        public Database(string dbname)
        {
            conn = new SqlCeConnection(String.Format("Data Source = {0}", dbname));
            try
            {
                conn.Open();
            }
            catch (SqlCeException)
            {
                SqlCeEngine engine = new SqlCeEngine(String.Format("Data Source = {0}", dbname));
                engine.CreateDatabase();
                conn.Open();
            }
            cmd = conn.CreateCommand();
            if (!TableExists("BASUsers"))
            {
                cmd.CommandText = "CREATE TABLE BASUsers (UserID INT PRIMARY KEY, Email NCHAR(100), Credits INT)";
                cmd.ExecuteNonQuery();
            }
            if (!TableExists("BASMails"))
            {
                cmd.CommandText = "CREATE TABLE BASMails (MailID INT PRIMARY KEY, SenderID INT REFERENCES BASUsers(UserID), ReceiverID INT REFERENCES BASUsers(UserID), Stamp NCHAR(50))";
                cmd.ExecuteNonQuery();
            }
        }

        public List<User> GetUserData()
        {
            List<User> Users = new List<User>();
            cmd.CommandText = "SELECT UserID, Email, Credits From BASUsers";
            SqlCeDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                User user = new User(Convert.ToInt32(rdr.GetValue(0)), rdr.GetValue(1).ToString().Trim(), Convert.ToInt32(rdr.GetValue(2)));
                Users.Add(user);
            }
            return Users;
        }

        public List<MailData> GetIncomingMail(int UserID)
        {
            List<MailData> MailDataList = new List<MailData>();
            cmd.CommandText = String.Format("SELECT MailID, Email, Stamp From BASMails Join BASUsers On SenderID = UserID where ReceiverID = '{0}'", UserID);
            SqlCeDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                MailData data = new MailData(Convert.ToInt32(rdr.GetValue(0)), rdr.GetValue(1).ToString().Trim(), "", rdr.GetValue(2).ToString().Trim());
                MailDataList.Add(data);
            }
            rdr.Close();
            return MailDataList;
        }

        public List<MailData> GetOutgoingMail(int UserID)
        {
            List<MailData> MailDataList = new List<MailData>();
            cmd.CommandText = String.Format("SELECT MailID, Email, Stamp From BASMails Join BASUsers On ReceiverID = UserID where SenderID = '{0}'", UserID);
            SqlCeDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                MailData data = new MailData(Convert.ToInt32(rdr.GetValue(0)), "", rdr.GetValue(1).ToString().Trim(), rdr.GetValue(2).ToString().Trim());
                MailDataList.Add(data);
            }
            rdr.Close();
            return MailDataList;
        }

        private int ToIntDefault(object obj){
            return (obj is DBNull) ? 0 : Convert.ToInt32(obj);
        }

        public int InsertUser(string Email, int Credits)
        {
            cmd.CommandText = "Select Max(UserID) from BASUsers";
            int MaxID = ToIntDefault(cmd.ExecuteScalar());
            cmd.CommandText = String.Format("Insert Into BASUsers (UserID, Email, Credits) Values ('{0}', '{1}', '{2}')", MaxID + 1, Email, Credits);
            cmd.ExecuteNonQuery();
            return MaxID + 1;
        }

        public void DeleteUser(int UserID)
        {
            cmd.CommandText = String.Format("Delete From BASMails Where SenderID = '{0}' or ReceiverID = '{0}'", UserID);
            cmd.ExecuteNonQuery();
            cmd.CommandText = String.Format("Delete From BASUsers Where UserID = '{0}'", UserID);
            cmd.ExecuteNonQuery();
        }

        public void ChangeUser(int UserID, string Email, int Credits)
        {
            cmd.CommandText = String.Format("Update BASUsers Set Email = '{0}', Credits = '{1}' Where UserID = '{2}'", Email, Credits, UserID);
            cmd.ExecuteNonQuery();
        }

        public int InsertMail(int FromID, int ToID, string Stamp)
        {
            cmd.CommandText = "Select Max(MailID) from BASMails";
            int MaxID = ToIntDefault(cmd.ExecuteScalar());
            cmd.CommandText = String.Format("Insert Into BASMails (MailID, SenderID, ReceiverID, Stamp) Values ('{0}', '{1}', '{2}', '{3}')", MaxID + 1, FromID, ToID, Stamp);
            cmd.ExecuteNonQuery();
            return MaxID + 1;
        }

        public void ChangeMail(int MailID, int FromID, int ToID, string Stamp)
        {
            cmd.CommandText = String.Format("Update BASMails Set SenderID = '{0}', ReceiverID = '{1}', Stamp = '{2}' Where UserID = '{3}'", FromID, ToID, Stamp, MailID);
            cmd.ExecuteNonQuery();
        }

        public bool CheckMail(int FromID, int ToID, string Stamp)
        {
            cmd.CommandText = String.Format("Select * From BASMails Where SenderID = '{0}' and ReceiverID = '{1}' and Stamp = '{2}'", FromID, ToID, Stamp);
            var rdr = cmd.ExecuteReader();
            bool state = rdr.Read();
            rdr.Close();
            return state;
        }

        public void DeleteMail(int FromID, int ToID, string Stamp)
        {
            cmd.CommandText = String.Format("Delete From BASMails Where SenderID = '{0}' and ReceiverID = '{1}' and Stamp = '{2}' and MailID in (Select Min(MailID) From BASMails Where SenderID = '{0}' and ReceiverID = '{1}' and Stamp = '{2}')", FromID, ToID, Stamp);
            cmd.ExecuteNonQuery();
        }


    }
}
