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

namespace Server_v2
{
    class DataBase
    {
        SQLiteConnection SQLconnection;
        private SQLiteCommand SQLcommand;

        const int MAX_NUMBER_OF_VERSIONS = 3;

        //lock per rendere la classe thread safe messa nei metodi che eseguono le query
        public static object lockDB = new object();

        #region CONSTRUCTOR

        /// <summary>
        /// Costruttore Database:
        /// Crea tabelle se non è mai stato creato un DB.
        /// TESTATO!
        /// </summary>
        public DataBase()
        {
            if (!File.Exists(".\\Database.db"))
            {
                SQLconnection = new SQLiteConnection("Data Source=Database.db;Version=3;New=True;Compress=True;");

                string createUser_query = "CREATE TABLE USER(Username TEXT PRIMARY KEY," +
                                                            "Password TEXT," +
                                                            "Sale TEXT," +
                                                            "ClientPath TEXT, " +
                                                            "ServerPath TEXT);";

                string createFolder_query = "CREATE TABLE FOLDER(FolderPath TEXT," +
                                                                "User TEXT," +
                                                                "ParentPath TEXT," +
                                                                "TimestampFolder DATETIME DEFAULT CURRENT_TIMESTAMP," +
                                                                "TimestampParent DATETIME," +
                                                                "Present TEXT DEFAULT 'True'," +

                                                                "PRIMARY KEY(FolderPath,User,TimestampFolder), " +
                                                                "FOREIGN KEY(User) REFERENCES USER (Username) ON DELETE CASCADE ON UPDATE CASCADE, " +
                                                                "FOREIGN KEY(ParentPath, User, TimestampParent) " +
                                                                "REFERENCES FOLDER(FolderPath, User, TimestampFolder) ON DELETE CASCADE ON UPDATE CASCADE);";

                string createFile_query = "CREATE TABLE FILE(Filename TEXT," +
                                                            "User TEXT," +
                                                            "Folder TEXT," +
                                                            "Checksum TEXT," +
                                                            "TimestampFile DATETIME DEFAULT CURRENT_TIMESTAMP," +
                                                            "TimestampFolder DATETIME," +
                                                            "Present TEXT DEFAULT 'True'," +

                                                            "PRIMARY KEY(Filename, User, Folder, Checksum, TimestampFile)," +
                                                            "FOREIGN KEY(Folder, User, TimestampFolder) " +
                                                            "REFERENCES FOLDER(FolderPath, User, TimestampFolder) ON DELETE CASCADE ON UPDATE CASCADE);";

                string createLoggedIn_query = "CREATE TABLE LOGGED_IN(Username TEXT," +
                                                                     "IPAddress TEXT," +
                                                                     "PRIMARY KEY(Username)," +
                                                                     "FOREIGN KEY(Username) " +
                                                                     "REFERENCES USER(Username) ON DELETE CASCADE ON UPDATE CASCADE);";
                try
                {
                    ExecuteQuery(createUser_query);
                    Console.WriteLine("USER Table created");
                    ExecuteQuery(createFolder_query);
                    Console.WriteLine("FOLDER Table created");
                    ExecuteQuery(createFile_query);
                    Console.WriteLine("FILE Table created");
                    ExecuteQuery(createLoggedIn_query);
                    Console.WriteLine("LOGGED_IN Table created");
                }
                catch (Exception e)
                {
                    Console.WriteLine("Database not created. Exception:");
                    Console.WriteLine(e.Message);
                    File.Delete(".\\Database.db");
                }
            }
        }

        #endregion

        #region ADD METHODS

        /// <summary>
        /// Aggiungi un utente alla tabella USER e la cartella da monitorare alla tabella FOLDER.
        /// Se esiste ritorna false senza inserirlo, se non esiste lo crea.
        ///
        /// NOTA: Il folder da monitorare che viene inserito nella tabella FOLDER ha come parent
        /// una cartella fittizia "." che ha come data di creazione la mia data di nascita.
        /// Fatto con una transazione che permette di mantenere le proprietà ACID.
        ///
        /// ATTENZIONE: Non permettere all'utente di registrarsi con il carattere "'".
        ///
        /// TESTATO!
        /// </summary>
        /// <param name="user">Utente da inserire</param>
       /*
        public bool AddUser(UserClass user)
        {
            try
            {
                if (!Exists(user))
                {
                    string insert_query = "BEGIN TRANSACTION; " +
                                          // Insert di un utente
                                          "INSERT INTO USER (Username,Password,Sale,ClientPath,ServerPath)" +
                                          "VALUES ('" + user.Username + "'," +
                                                  "'" + user.Password + "'," +
                                                  "'" + user.Sale + "'," +
                                                  "'" + user.ClientPath + "'," +
                                                  "'" + user.ServerPath + "');" +
                                          // Insert della cartella da monitorare nella tabella FOLDER
                                          "INSERT INTO FOLDER (FolderPath,User,ParentPath,TimestampParent,Present)" +
                                          "VALUES ('" + user.ClientPath + "'," +
                                                  "'" + user.Username + "'," +
                                                  "'NULL'," +
                                                  "'" + DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss") + "'," +
                                                  "'" + true.ToString() + "');" +
                                          "END TRANSACTION;";
                    ExecuteQuery(insert_query);
                    return true;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            return false;
        }
        */
        public bool AddUser(UserClass user)
        {
            try
            {
                if (!Exists(user))
                {
                    SQLiteCommand command = new SQLiteCommand(null, null);

                    command.CommandText = @"BEGIN TRANSACTION; " +
                                          // Insert di un utente
                                          "INSERT INTO USER (Username, Password, Sale, ClientPath, ServerPath)" +
                                          "VALUES (@uname, @upsw, @usal, @uCpath, @uSPath);" +
                                          // Insert della cartella da monitorare nella tabella FOLDER
                                          @"INSERT INTO FOLDER (FolderPath, User, ParentPath, TimestampParent, Present)" +
                                          "VALUES (@uCpath, @uname, @pp, @date, @bool);" +
                                          " END TRANSACTION;";

                    SQLiteParameter unameParam = new SQLiteParameter("@uname", DbType.String);
                    SQLiteParameter upswParam = new SQLiteParameter("@upsw", DbType.String);
                    SQLiteParameter usalParam = new SQLiteParameter("@usal", DbType.String);
                    SQLiteParameter uCpathParam = new SQLiteParameter("@uCpath", DbType.String);
                    SQLiteParameter uSpathParam = new SQLiteParameter("@uSpath", DbType.String);
                    SQLiteParameter ppParam = new SQLiteParameter("@pp", DbType.String);
                    SQLiteParameter dateParam = new SQLiteParameter("@date", SqlDbType.DateTime);
                    SQLiteParameter boolParam = new SQLiteParameter("@bool", DbType.String);

                    unameParam.Value = user.Username;
                    upswParam.Value = user.Password;
                    usalParam.Value = user.Sale;
                    uCpathParam.Value = user.ClientPath;
                    uSpathParam.Value = user.ServerPath;
                    ppParam.Value = "NULL";
                    dateParam.Value = DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss");
                    boolParam.Value = true.ToString();

                    command.Parameters.Add(unameParam);
                    command.Parameters.Add(upswParam);
                    command.Parameters.Add(usalParam);
                    command.Parameters.Add(uCpathParam);
                    command.Parameters.Add(uSpathParam);
                    command.Parameters.Add(ppParam);
                    command.Parameters.Add(dateParam);
                    command.Parameters.Add(boolParam);

//                    command.Prepare();
                    ExecuteQuery2(command);
                    return true;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            return false;
        }

        /// <summary>
        /// Aggiunge una cartella alla tabela FOLDER.
        /// L'oggetto FolderClass deve avere definito:
        /// - FolderPath
        /// - User
        /// TESTATO!
        /// </summary>
        /// <param name="folder">Cartella da aggiungere</param>
        public bool AddFolder(FolderClass folder)
        {
            string parentPath;
            DateTime timestampParent;

            if (Exists(folder)) return false;

            //Creo la cartella padre
            folder.Parent = new FolderClass();
            folder.Parent.Present = true;
            folder.Parent.User = folder.User;
            folder.Parent.FolderPath = Path.GetDirectoryName(folder.FolderPath);
            parentPath = folder.Parent.FolderPath;
            timestampParent = getTimestampFolder(folder.Parent);

            string insert_query = "INSERT INTO FOLDER (FolderPath,User,ParentPath,TimestampParent,Present)" +
                                  "VALUES ('" + folder.FolderPath + "'," +
                                          "'" + folder.User.Username + "'," +
                                          "'" + parentPath + "'," +
                                          "'" + timestampParent.ToString("yyyy-MM-dd HH:mm:ss") + "'," +
                                          "'" + true.ToString() + "')";
            try
            {
                ExecuteQuery(insert_query);
            }
            catch (Exception)
            {
                Console.WriteLine("Error");
            }

            return true;
        }

        /// <summary>
        /// Aggiunge un file alla tabella FILE.
        /// Devono essere definiti:
        /// - Filename
        /// - User
        /// - Folder
        /// - Checksum
        /// A sua volta l'oggetto folder vede avere definito:
        /// - FolderPath
        /// - User
        ///
        /// NOTA: Controllare la presenza della cartella che contiene il file.
        ///
        /// TESTATO!
        /// </summary>
        /// <param name="file"></param>
        public string AddFile(FileClass file)
        {
            //Se aggiungo un file la cartella deve essere presente. Esiste una sola cartella presente
            //con queste caratteristiche.
            int n = 0;
            file.Folder.Present = true;
            DateTime timestampFolder = getTimestampFolder(file.Folder);

            //ritorno un checksum se si elimina un file
            string checksum = null;

            if (this.Exists(file, file.Folder))
            {
                setFilePresent(file);
                return null;
            }

            //Conto il numero di versioni del file
            string select_query = "SELECT COUNT(*) " +
                                  "FROM FILE " +
                                  "WHERE Filename = '" + file.Filename + "' AND " +
                                        "User = '" + file.User.Username + "' AND " +
                                        "Folder = '" + file.Folder.FolderPath + "' AND " +
                                        "TimestampFolder = '" + timestampFolder.ToString("yyyy-MM-dd HH:mm:ss") + "';";
            lock (lockDB)
            {
                SQLiteDataReader r = ExecuteQuerySelect(select_query);
                while (r.Read())
                    n = r.GetInt16(0);
                CloseConnection();
            }

            if (n >= MAX_NUMBER_OF_VERSIONS)
            {
                //Se raggiungo il numero massimo di versioni

                //Identifico il file con timestamp più vecchio

                checksum = getOldestChecksum(file);

                string insert_delete_query =
                                      "BEGIN TRANSACTION;" +
                                      //Cancello il file più vecchio
                                      "DELETE FROM FILE WHERE Filename = '" + file.Filename + "' AND " +
                                                             "User = '" + file.User.Username + "' AND " +
                                                             "Folder = '" + file.Folder.FolderPath + "' AND " +
                                                             "Checksum = '" + checksum + "'; " +
                                      //Inserisco il nuovo file
                                      "INSERT INTO FILE (Filename,User,Folder,Checksum,TimestampFolder)" +
                                      "VALUES ('" + file.Filename + "'," +
                                              "'" + file.User.Username + "'," +
                                              "'" + file.Folder.FolderPath + "'," +
                                              "'" + file.Checksum + "'," +
                                              "'" + timestampFolder.ToString("yyyy-MM-dd HH:mm:ss") + "');" +
                                      "END TRANSACTION;";
                ExecuteQuery(insert_delete_query);

                //se ho un file con questo checksum che punta a cartelle presenti diversa da quella di questo file
                //non devo eliminare questo file e ripongo il checksum a null

                select_query = "SELECT COUNT(*) " +
                               "FROM FILE " +
                               "WHERE Checksum = '" + file.Checksum + "' AND " +
                                     "User = '" + file.User.Username + "' AND " +
                                     "Folder <> '" + file.Folder.FolderPath + "';";

                lock (lockDB)
                {
                    SQLiteDataReader r = ExecuteQuerySelect(select_query);
                    while (r.Read())
                        n = r.GetInt16(0);
                    CloseConnection();
                }

                if (n != 0)
                    checksum = null;
            }
            else
            {
                string insert_query = "INSERT INTO FILE (Filename,User,Folder,Checksum,TimestampFolder)" +
                                      "VALUES ('" + file.Filename + "'," +
                                              "'" + file.User.Username + "'," +
                                              "'" + file.Folder.FolderPath + "'," +
                                              "'" + file.Checksum + "'," +
                                              "'" + timestampFolder.ToString("yyyy-MM-dd HH:mm:ss") + "');";

                ExecuteQuery(insert_query);
            }


            return checksum;
        }

        public void setFilePresent(FileClass file)
        {
            string update_query = "UPDATE FILE " +
                                  "SET Present = '" + true.ToString() + "', TimestampFile = '" + DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss") + "' " +
                                  "WHERE Filename = '" + file.Filename + "' AND " +
                                        "Folder = '" + file.Folder.FolderPath + "' AND " +
                                        "Present = '" + false.ToString() + "' AND " +
                                        "User = '" + file.User.Username + "' AND " +
                                        "Checksum = '" + file.Checksum + "';";
            ExecuteQueryFK(update_query);
        }

        /// <summary>
        /// Aggiunge un utente alla tabella degli utenti connessi.
        /// Devono essere definiti nell'oggetto UserClass:
        /// - Username
        /// - Ip
        /// </summary>
        /// <param name="user"></param>
        public void LoggedIn(UserClass user)
        {
            string insert_query = "INSERT INTO LOGGED_IN (Username, IPAddress)" +
                                  "VALUES ('" + user.Username + "'," +
                                          "'" + user.Ip + "');";
            ExecuteQuery(insert_query);
        }

        #endregion

        #region GET METHODS

        public bool IsFile(string path, UserClass user)
        {
            if (IsFolder(path, user)) return false;

            int n = 0;
            string select_query = "SELECT COUNT(*) " +
                                  "FROM FILE " +
                                  "WHERE Filename = '" + Path.GetFileName(path) + "' AND " +
                                        "Folder = '" + Path.GetDirectoryName(path) + "' AND " +
                                        "Present = '" + true.ToString() + "' AND " +
                                        "User = '" + user.Username + "';";
            lock (lockDB)
            {
                SQLiteDataReader r = ExecuteQuerySelect(select_query);
                while (r.Read())
                    n = r.GetInt16(0);
                CloseConnection();
            }

            if (n == 0) return false;

            return true;
        }

        public bool IsFolder(string path, UserClass user)
        {
            int n = 0;
            string select_query = "SELECT COUNT(*) " +
                                  "FROM FOLDER " +
                                  "WHERE FolderPath = '" + path + "' AND " +
                                        "User = '" + user.Username + "' AND " +
                                        "Present = '" + true.ToString() + "';";

            lock (lockDB)
            {
                SQLiteDataReader r = ExecuteQuerySelect(select_query);
                while (r.Read())
                    n = r.GetInt16(0);
                CloseConnection();
            }

            if (n == 0) return false;

            return true;
        }

        /// <summary>
        /// Ottiene il timestamp della cartella richiesta. L'oggetto FolderCLass deve avere
        /// definiti:
        /// - FolderPath
        /// - Present
        /// - User
        ///
        /// TESTATO!
        /// </summary>
        /// <param name="folder">Cartella richiesta</param>
        /// <returns></returns>
        private DateTime getTimestampFolder(FolderClass folder)
        {
            DateTime dateTime = new DateTime();
            string select_query = "SELECT TimestampFolder " +
                                  "FROM Folder " +
                                  "WHERE FolderPath = '" + folder.FolderPath + "' " +
                                        "AND Present = '" + folder.Present.ToString() + "' " +
                                        "AND User = '" + folder.User.Username + "' ";
            lock (lockDB)
            {
                SetConnection();

                SQLiteDataReader r = ExecuteQuerySelect(select_query);
                while (r.Read())
                    dateTime = (DateTime)r["TimestampFolder"];

                CloseConnection();
            }

            return dateTime;
        }

        /// <summary>
        /// Ottiene il timestamp del file. L'oggetto file deve avere definito:
        /// - Filename
        /// - Checksum
        /// - Username
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        private DateTime getTimestampFile(FileClass file)
        {
            DateTime dateTime = new DateTime();
            string select_query = "SELECT TimestampFile " +
                                  "FROM FILE " +
                                  "WHERE Filename = '" + file.Filename + "' " +
                                        "AND Checksum = '" + file.Checksum + "' " +
                                        "AND User = '" + file.User.Username + "' ";
            lock (lockDB)
            {
                SetConnection();

                SQLiteDataReader r = ExecuteQuerySelect(select_query);
                while (r.Read())
                    dateTime = (DateTime)r["TimestampFile"];

                CloseConnection();
            }

            return dateTime;
        }

        /// <summary>
        /// Ritorna il checksum con timestamp più vecchio.
        /// Il file passato deve avere definito:
        /// - Filename
        /// - User
        /// - Folder
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        private string getOldestChecksum(FileClass file)
        {
            string checksum = null;
            string select_query = "SELECT Checksum  " +
                                  "FROM FILE " +
                                  "WHERE Filename = '" + file.Filename + "' AND " +
                                        "User = '" + file.User.Username + "' AND " +
                                        "Folder = '" + file.Folder.FolderPath + "' AND " +
                                        "TimestampFile IN (SELECT min(TimestampFile)" +
                                                          "FROM FILE " +
                                                          "WHERE Filename = '" + file.Filename + "' AND " +
                                                                "User = '" + file.User.Username + "' AND " +
                                                                "Folder = '" + file.Folder.FolderPath + "');";

            lock (lockDB)
            {
                SQLiteDataReader r = ExecuteQuerySelect(select_query);
                while (r.Read())
                    checksum = (string)r["Checksum"];
                CloseConnection();
            }
            return checksum;
        }

        /// <summary>
        /// Ritorna la password dell'utente. Prima di chiamare questa funzione verificare che l'utente esista!
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public string getPassword(UserClass user)
        {
            string select_query = "SELECT Password " +
                                  "FROM USER " +
                                  "WHERE Username = '" + user.Username + "'";
            string psw = null;

            try
            {
                lock (lockDB)
                {
                    SQLiteDataReader r = ExecuteQuerySelect(select_query);
                    while (r.Read())
                        psw = (string)r["Password"];
                    CloseConnection();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return psw;
        }

        /// <summary>
        /// Ritorna il Sale di un utente.
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public string getSale(UserClass user)
        {
            string select_query = "SELECT Sale " +
                                  "FROM USER " +
                                  "WHERE Username = '" + user.Username + "'";
            string sale = null;
            try
            {
                lock (lockDB)
                {
                    SQLiteDataReader r = ExecuteQuerySelect(select_query);
                    while (r.Read())
                        sale = (string)r["Sale"];
                    CloseConnection();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return sale;
        }

        /// <summary>
        /// Ritorna il path dell'utente della cartella sincronizzata.
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public string getClientPath(UserClass user)
        {
            string select_query = "SELECT ClientPath " +
                                  "FROM USER " +
                                  "WHERE Username = '" + user.Username + "'";
            string path = null;
            lock (lockDB)
            {
                SQLiteDataReader r = ExecuteQuerySelect(select_query);
                while (r.Read())
                    path = (string)r["ClientPath"];
                CloseConnection();
            }
            return path;
        }

        public FolderClass getFolder(string folderpath, UserClass user)
        {
            FolderClass folder = new FolderClass();
            string select_query = "SELECT FolderPath, Present " +
                                  "FROM FOLDER " +
                                  "WHERE FolderPath = '" + folderpath + "' " +
                                        "AND Present = '" + true.ToString() + "' " +
                                        "AND User = '" + user.Username + "' ";
            lock (lockDB)
            {
                SetConnection();

                SQLiteDataReader r = ExecuteQuerySelect(select_query);
                while (r.Read())
                {
                    folder.FolderPath = (string)r["FolderPath"];
                    folder.Present = true;
                    folder.User = user;
                }
                CloseConnection();
            }

            return folder;
        }

        public List<FileClass> getFilePresent(UserClass user)
        {
            List<FileClass> files = new List<FileClass>();
            string select_query = "SELECT Filename, Folder, Checksum " +
                                  "FROM FILE " +
                                  "WHERE User = '" + user.Username + "' AND " +
                                        "Present = '" + true.ToString() + "'; ";
            lock (lockDB)
            {
                SQLiteDataReader r = ExecuteQuerySelect(select_query);
                while (r.Read())
                {
                    FileClass file = new FileClass();
                    file.Filename = (string)r["Filename"];
                    file.Checksum = (string)r["Checksum"];
                    file.User = user;
                    file.Folder.FolderPath = (string)r["Folder"];
                    file.Folder.Present = true;
                    file.Folder.User = user;
                    files.Add(file);
                }
                CloseConnection();
            }
            return files;
        }

        public List<FolderClass> getFolderPresent(UserClass user)
        {
            List<FolderClass> folders = new List<FolderClass>();
            string select_query = "SELECT FolderPath, ParentPath " +
                                  "FROM FOLDER F, USER U  " +
                                  "WHERE F.User = U.Username AND F.FolderPath <> U.ClientPath AND " +
                                        "F.User = '" + user.Username + "' AND " +
                                        "F.Present = '" + true.ToString() + "'; ";
            lock (lockDB)
            {
                SQLiteDataReader r = ExecuteQuerySelect(select_query);
                while (r.Read())
                {
                    FolderClass folder = new FolderClass();
                    folder.FolderPath = (string)r["FolderPath"];
                    folder.Parent.FolderPath = (string)r["ParentPath"];
                    folder.Parent.User = user;
                    folder.Present = true;
                    folder.User = user;
                    folders.Add(folder);
                }
                CloseConnection();
            }
            return folders;
        }

        public List<FileClass> getAllFiles(UserClass user)
        {
            List<FileClass> allFilesVersions = new List<FileClass>();

            string select_query = "SELECT Filename, Folder, Checksum, TimestampFile  " +
                                  "FROM FILE " +
                                  "WHERE User = '" + user.Username + "'; ";
            lock (lockDB)
            {
                SQLiteDataReader r = ExecuteQuerySelect(select_query);
                while (r.Read())
                {
                    FileClass file = new FileClass();
                    file.Filename = (string)r["Filename"];
                    file.Checksum = (string)r["Checksum"];
                    file.Timestamp = r["TimestampFile"].ToString();
                    file.User = user;
                    file.Folder.FolderPath = (string)r["Folder"];
                    file.Folder.Present = true;
                    file.Folder.User = user;
                    allFilesVersions.Add(file);
                }
                CloseConnection();
            }
            return allFilesVersions;
        }

        #endregion

        #region DELETE

        /// <summary>
        /// Cancella il file più vecchio. Non vi è la necessità di eliminare un file specifico
        /// </summary>
        /// <param name="file"></param>
        private void deleteOldestFile(FileClass file)
        {
            DateTime dt = DateTime.MinValue;
            string select_query = "SELECT min(Timestamp) " +
                                  "FROM FILE " +
                                  "WHERE Filename = '" + file.Filename + "' AND " +
                                        "User = '" + file.User.Username + "' AND " +
                                        "Folder = '" + file.Folder.FolderPath + "'";
            lock (lockDB)
            {
                SQLiteDataReader r = ExecuteQuerySelect(select_query);
                while (r.Read())
                    dt = r.GetDateTime(0);
                CloseConnection();
            }
            if (dt != DateTime.MinValue)
            {
                string delete_query = "DELETE FROM FILE WHERE Filename = '" + file.Filename + "' AND " +
                                                             "User = '" + file.User.Username + "' AND " +
                                                             "Folder = '" + file.Folder.FolderPath + "' AND " +
                                                             "TimestampFile = '" + dt.ToString("yyyy-MM-dd HH:mm:ss") + "'";
                ExecuteQuery(delete_query);
            }
        }

        /// <summary>
        /// Mette come assenti la cartella eliminata e tutte le sue sotto cartelle e i suoi file.
        /// </summary>
        /// <param name="folder"></param>
        public void setFolderAbsent(FolderClass folder)
        {
            string update_query = "BEGIN TRANSACTION;" +

                                  "UPDATE FOLDER " +
                                  "SET Present = '" + false.ToString() + "' " +
                                  "WHERE FolderPath LIKE '" + folder.FolderPath + "%' AND " +
                                        "User = '" + folder.User.Username + "' AND " +
                                        "Present = '" + true.ToString() + "';" +

                                  "UPDATE FILE " +
                                  "SET Present = '" + false.ToString() + "' " +
                                  "WHERE Folder LIKE '" + folder.FolderPath + "%' AND " +
                                        "User = '" + folder.User.Username + "' AND " +
                                        "Present = '" + true.ToString() + "';" +

                                  "END TRANSACTION;";
            ExecuteQueryFK(update_query);
        }

        /// <summary>
        /// Mette come assenti i file eliminati.
        /// Deve essere definito il folderPath del folder del file.
        /// </summary>
        /// <param name="file"></param>
        public void setFileAbsent(FileClass file)
        {
            string update_query = "UPDATE FILE " +
                                  "SET Present = '" + false.ToString() + "' " +
                                  "WHERE Filename = '" + file.Filename + "' AND " +
                                        "Folder = '" + file.Folder.FolderPath + "' AND " +
                                        "Present = '" + true.ToString() + "' AND " +
                                        "User = '" + file.User.Username + "';";
            ExecuteQueryFK(update_query);
        }

        /// <summary>
        /// Elimina un utente nella tabella USER.
        /// L'oggetto user deve avere definito:
        /// - Username
        /// </summary>
        /// <param name="user"></param>
        public void deleteUser(UserClass user)
        {
            string delete_query = "DELETE FROM USER WHERE Username = '" + user.Username + "'";

            ExecuteQueryFK(delete_query);
        }

        /// <summary>
        /// Cancella tutti i file e le cartelle presenti nelle due liste
        /// </summary>
        /// <param name="listFiles"></param>
        /// <param name="listFolders"></param>
        public void realigns(List<FileClass> listFiles, List<FolderClass> listFolders)
        {
            string update_query = "BEGIN TRANSACTION; ";
            foreach (FileClass file in listFiles)
            {
                update_query = update_query +
                              "UPDATE FILE " +
                              "SET Present = '" + false.ToString() + "' " +
                              "WHERE Filename = '" + file.Filename + "' AND " +
                                    "Folder = '" + file.Folder.FolderPath + "' AND " +
                                    "Present = '" + true.ToString() + "' AND " +
                                    "User = '" + file.User.Username + "'; ";
            }
            foreach (FolderClass folder in listFolders)
            {
                update_query = update_query +

                              "UPDATE FOLDER " +
                              "SET Present = '" + false.ToString() + "' " +
                              "WHERE FolderPath LIKE '" + folder.FolderPath + "%' AND " +
                                    "User = '" + folder.User.Username + "' AND " +
                                    "Present = '" + true.ToString() + "'; " +

                              "UPDATE FILE " +
                              "SET Present = '" + false.ToString() + "' " +
                              "WHERE Folder LIKE '" + folder.FolderPath + "%' AND " +
                                    "User = '" + folder.User.Username + "' AND " +
                                    "Present = '" + true.ToString() + "'; ";
            }

            update_query = update_query + " END TRANSACTION; ";

            ExecuteQueryFK(update_query);
        }

        /// <summary>
        /// Elimina un utente dallla tabella LOGGED_IN
        /// Deve essere definito nell'oggetto UserClass Username.
        /// </summary>
        /// <param name="user"></param>
        public void LogOff(UserClass user)
        {
            string delete_query = "DELETE FROM LOGGED_IN WHERE Username = '" + user.Username + "'";

            ExecuteQueryFK(delete_query);
        }

        #endregion

        #region RENAME

        /// <summary>
        /// Vengono rinominati tutti i file con lo stesso nome contenuti nella stessa cartella.
        /// Non viene considerato il checksum in quanto si vuole mantenere allineati i nomi di tutte
        /// le versioni precedenti del file.
        /// </summary>
        /// <param name="file"></param>
        /// <param name="newFileName"></param>
        public void RenameFile(FileClass file, string newFileName)
        {
            string update_query = "UPDATE FILE " +
                                  "SET Filename = '" + newFileName + "'" +
                                  "WHERE Filename = '" + file.Filename + "' AND " +
                                        "User = '" + file.User.Username + "' AND " +
                                        "Folder = '" + file.Folder.FolderPath + "';";
            ExecuteQueryFK(update_query);
        }

        public void RenameFolder(FolderClass folder, string newFolderPath)
        {
            string[] oldfolders;
            string[] newfolders;
            List<string> folderPaths = new List<string>();

            string select_query = "SELECT FolderPath " +
                                  "FROM FOLDER " +
                                  "WHERE FolderPath LIKE '" + folder.FolderPath + "%' AND " +
                                        "User = '" + folder.User.Username + "';";

            lock (lockDB)
            {
                SQLiteDataReader r = ExecuteQuerySelect(select_query);

                while (r.Read())
                    folderPaths.Add((string)r["FolderPath"]);

                CloseConnection();
            }

            string update_query = "BEGIN TRANSACTION; ";

            update_query = update_query +
                           " UPDATE FOLDER " +
                           "SET FolderPath = '" + newFolderPath + "' " +
                           "WHERE FolderPath = '" + folder.FolderPath + "' AND " +
                                 "User = '" + folder.User.Username + "' AND " +
                                 "ParentPath = '" + Path.GetDirectoryName(Sanitizer.sanitize(newFolderPath)) + "' AND " +
                                 "Present = '" + true.ToString() + "'; ";

            foreach (string oldPath in folderPaths)
            {
                oldfolders = oldPath.Split('\\');
                newfolders = newFolderPath.Split('\\');

                oldfolders[newfolders.Length - 1] = newfolders[newfolders.Length - 1];

                string newPath = oldfolders[0];
                for (int i = 1; i < oldfolders.Length; i++)
                    newPath = newPath + "\\" + oldfolders[i];

                update_query = update_query +
                               " UPDATE FOLDER " +
                               "SET FolderPath = '" + newPath + "' " +
                               "WHERE FolderPath = '" + oldPath + "' AND " +
                                     "User = '" + folder.User.Username + "'; ";
            }

            update_query = update_query + " END TRANSACTION;";

            ExecuteQueryFK(update_query);

        }

        #endregion

        #region PRINT METHODS

        /// <summary>
        /// Stampa la tabella USER.
        /// TESTATO!
        /// </summary>
        public void printUsers()
        {
            string query = "SELECT * FROM USER ORDER BY Username";
            lock (lockDB)
            {
                SQLiteDataReader r = ExecuteQuerySelect(query);
                Console.WriteLine("\nUSER Table:");
                int i = 0;
                while (r.Read())
                {
                    Console.WriteLine("\nUsername:\t" + (string)r["Username"]);
                    Console.WriteLine("Password:\t" + (string)r["Password"]);
                    Console.WriteLine("Sale:\t\t" + (string)r["Sale"]);
                    Console.WriteLine("Client Path:\t" + (string)r["ClientPath"]);
                    Console.WriteLine("Server Path:\t" + (string)r["ServerPath"] + "\n");
                    i++;
                }
                if (i == 0)
                    Console.WriteLine("Tabella vuota.");
                CloseConnection();
            }
        }

        /// <summary>
        /// Stampa la tabella FOLDER.
        /// TESTATO!
        /// </summary>
        public void printFolders()
        {
            string query = "SELECT * FROM FOLDER ORDER BY User";
            lock (lockDB)
            {
                SQLiteDataReader r = ExecuteQuerySelect(query);
                Console.WriteLine("\nFOLDER Table:");
                int i = 0;
                while (r.Read())
                {
                    Console.WriteLine("\nFolder Path:\t\t" + (string)r["FolderPath"]);
                    Console.WriteLine("User:\t\t\t" + (string)r["User"]);
                    Console.WriteLine("Parent Path:\t\t" + (string)r["ParentPath"]);
                    Console.WriteLine("Timestamp Folder:\t" + r["TimestampFolder"].ToString());
                    Console.WriteLine("Timestamp Parent:\t" + r["TimestampParent"].ToString());
                    Console.WriteLine("Present:\t\t\t" + r["Present"].ToString() + "\n");
                    i++;
                }
                if (i == 0)
                    Console.WriteLine("Tabella vuota.");
                CloseConnection();
            }
        }

        /// <summary>
        /// Stampa la tabella FILE.
        /// TESTATO!
        /// </summary>
        public void printFiles()
        {
            string query = "SELECT * FROM FILE ORDER BY User,Filename";
            lock (lockDB)
            {
                SQLiteDataReader r = ExecuteQuerySelect(query);
                Console.WriteLine("\nFILE Table:");
                int i = 0;
                while (r.Read())
                {
                    Console.WriteLine("\nFile Name:\t\t" + (string)r["Filename"]);
                    Console.WriteLine("User:\t\t\t" + (string)r["User"]);
                    Console.WriteLine("Checksum:\t\t" + r["Checksum"].ToString());
                    Console.WriteLine("Folder:\t\t\t" + (string)r["Folder"]);
                    Console.WriteLine("Timestamp File:\t\t" + r["TimestampFile"].ToString());
                    Console.WriteLine("Timestamp Folder:\t" + r["TimestampFolder"].ToString());
                    Console.WriteLine("Present:\t\t\t" + r["Present"].ToString() + "\n");
                    i++;
                }
                if (i == 0)
                    Console.WriteLine("Tabella vuota.");
                CloseConnection();
            }
        }

        #endregion

        #region SEARCH METHODS

        /// <summary>
        /// Cerca tutti gli utenti.
        /// TESTATO
        /// </summary>
        /// <returns></returns>
        private List<string> SearchUsers()
        {
            List<string> res = new List<string>();
            try
            {
                lock (lockDB)
                {
                    SetConnection();
                    string select_query = "SELECT Username " +
                                          "FROM USER";
                    SQLiteDataReader r = ExecuteQuerySelect(select_query);
                    while (r.Read())
                        res.Add((string)r["Username"]);
                    CloseConnection();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return res;
        }

        /// <summary>
        /// Cerca tutti i file di un utente e ritorna la lista con i checksum dei file
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        private List<string> SearchUserAllFiles(UserClass user)
        {
            List<string> res = new List<string>();

            lock (lockDB)
            {
                string select_query = "SELECT Checksum " +
                                      "FROM FILE " +
                                      "WHERE User = '" + user.Username + "';";
                SQLiteDataReader r = ExecuteQuerySelect(select_query);
                while (r.Read())
                    res.Add((string)r["Checksum"]);
                CloseConnection();
            }

            return res;
        }

        /// <summary>
        /// Cerca tutti i file di un utente specifico nella cartella folder
        /// TESTATO
        /// </summary>
        /// <param name="utente"></param>
        /// <returns></returns>
        public List<string> SearchUserFilesInFolder(UserClass user, FolderClass folder)
        {
            List<string> res = new List<string>();

            lock (lockDB)
            {
                string select_query = "SELECT Checksum " +
                                      "FROM FILE " +
                                      "WHERE User = '" + user.Username + "' AND " +
                                            "Folder = '" + folder.FolderPath + "';";
                SQLiteDataReader r = ExecuteQuerySelect(select_query);
                while (r.Read())
                    res.Add((string)r["Checksum"]);
                CloseConnection();
            }

            return res;
        }

        public List<string> SearchUserPresentFolders(UserClass user)
        {
            List<string> res = new List<string>();

            lock (lockDB)
            {
                string select_query = "SELECT FolderPath " +
                                      "FROM FOLDER " +
                                      "WHERE User = '" + user.Username + "' AND " +
                                            "Present = '" + true.ToString() + "';";
                SQLiteDataReader r = ExecuteQuerySelect(select_query);
                while (r.Read())
                    res.Add((string)r["FolderPath"]);
                CloseConnection();
            }

            return res;
        }

        /// <summary>
        /// Controlla se un file esiste (true) o meno (false).
        /// TESTATO!
        /// </summary>
        /// <param name="path"></param>
        /// <param name="utente"></param>
        /// <returns></returns>
        public bool Exists(FileClass file)
        {
            List<string> ris = SearchUserAllFiles(file.User);
            return ris.Contains(file.Checksum);
        }

        /// <summary>
        /// Controlla se un fil esiste
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public bool Exists(FileClass file, FolderClass folder)
        {
            List<string> ris = SearchUserFilesInFolder(file.User, folder);
            return ris.Contains(file.Checksum);
        }

        /// <summary>
        /// Se l'utente esiste ritorna true altrimenti ritorna false.
        /// TESTATO!
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public bool Exists(UserClass user)
        {
            List<string> users = SearchUsers();
            return users.Contains(user.Username);
        }

        /// <summary>
        /// Se il la cartella esiste ritorna true altrimenti ritorna false.
        /// L'oggetto folder deve avere definito:
        /// - FolderPath
        /// - User
        /// </summary>
        /// <param name="folder"></param>
        /// <returns></returns>
        public bool Exists(FolderClass folder)
        {
            List<string> folders = SearchUserPresentFolders(folder.User);
            return folders.Contains(folder.FolderPath);
        }

        #endregion

        #region QUERYEXECUTOR

        private void SetConnection()
        {
            //In caso di mancanza viene creato il file .db. File compresso.
            SQLconnection = new SQLiteConnection("Data Source=Database.db;Version=3;New=False;Compress=True;");
        }

        private void CloseConnection()
        {
            try
            {
                SQLconnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }

        /// <summary>
        /// Esegue una Query diversa dalla select al database. La query in SQL è specificata
        /// nella stringa query.
        /// Se si esegue una delete o un update, non viene eseguita una DELETE CASCADE: per eliminare
        /// o aggiornare in cascata utilizzare la funzione ExecuteQueryCascade().
        /// Metodo thread safe.
        /// </summary>
        /// <param name="query">Query da eseguire come stringa</param>
        private void ExecuteQuery(string query)
        {
            lock (lockDB)
            {
                SetConnection();
                SQLconnection.Open();
                SQLcommand = SQLconnection.CreateCommand();
                SQLcommand.CommandText = query;
                SQLcommand.ExecuteNonQuery();
                SQLconnection.Close();
            }
        }

        private void ExecuteQuery2(SQLiteCommand query)
        {
            lock (lockDB)
            {

                SetConnection();
                SQLconnection.Open();
                SQLiteCommand x = new SQLiteCommand(null, SQLconnection);
                x.CommandText = query.CommandText;
                foreach (var aa in query.Parameters) {
                    x.Parameters.Add(aa);
                }
                x.Prepare();
                x.ExecuteNonQuery();
                SQLconnection.Close();
            }
        }

        /// <summary>
        /// Esegue una query abilitando la cancellazione o l'update in cascata
        /// </summary>
        /// <param name="query"></param>
        private void ExecuteQueryFK(string query)
        {
            lock (lockDB)
            {
                SetConnection();
                SQLconnection.Open();
                SQLcommand = SQLconnection.CreateCommand();
                SQLcommand.CommandText = "PRAGMA foreign_keys = ON";
                SQLcommand.ExecuteNonQuery();
                SQLcommand.CommandText = query;
                SQLcommand.ExecuteNonQuery();
                SQLconnection.Close();
            }
        }

        /// <summary>
        /// Esegue la query SELECT e ritorna il risultato.
        /// La query SELECT in SQL è specificata nella stringa query.
        /// Il metodo non è thread safe. Racchiuderlo in un lock.
        /// </summary>
        /// <param name="query">Query da eseguire come stringa</param>
        /// <returns></returns>
        private SQLiteDataReader ExecuteQuerySelect(string query)
        {
            SetConnection();
            SQLconnection.Open();
            SQLcommand = SQLconnection.CreateCommand();
            SQLcommand.CommandText = query;
            SQLiteDataReader r = SQLcommand.ExecuteReader();
            return r;
        }

        #endregion
    }
}