context: using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Text;
  using Duplicati.Library.Main.Volumes;
  
  namespace Duplicati.Library.Main.Database
  {
      internal partial class LocalRestoreDatabase : LocalDatabase
      {
          protected string m_temptabsetguid = Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());
          protected string m_tempfiletable;
          protected string m_tempblocktable;
          protected string m_fileprogtable;
          protected string m_totalprogtable;
          protected string m_filesnewlydonetable;
  
          protected DateTime m_restoreTime;
  
          public DateTime RestoreTime { get { return m_restoreTime; } } 
  
          public LocalRestoreDatabase(string path)
              : this(new LocalDatabase(path, "Restore", false))
          {
              ShouldCloseConnection = true;
          }
  
          public LocalRestoreDatabase(LocalDatabase dbparent)
              : base(dbparent)
          {
          }
  
          /// <summary>
          /// Create tables and triggers for automatic tracking of progress during a restore operation.
          /// This replaces continuous requerying of block progress by iterating over blocks table.
          /// SQLite is much faster keeping information up to date with internal triggers.
          /// </summary>
          /// <param name="createFilesNewlyDoneTracker"> This allows to create another table that keeps track
          /// of all files that are done (all data blocks restored). </param>
          /// <remarks>
          /// The method is prepared to create a table that keeps track of all files being done completely.
          /// That means, it fires for files where the number of restored blocks equals the number of all blocks.
          /// It is intended to be used for fast identification of fully restored files to trigger their verification.
          /// It should be read after a commit and truncated after putting the files to a verification queue.
          /// Note: If a file is done once and then set back to a none restored state, the file is not automatically removed.
          ///       But if it reaches a restored state later, it will be readded (trigger will fire)
          /// </remarks>
          public void CreateProgressTracker(bool createFilesNewlyDoneTracker)
          {
              m_fileprogtable = "FileProgress-" + this.m_temptabsetguid;
              m_totalprogtable = "TotalProgress-" + this.m_temptabsetguid;
              m_filesnewlydonetable = createFilesNewlyDoneTracker ? "FilesNewlyDone-" + this.m_temptabsetguid : null;
  
              using (var cmd = m_connection.CreateCommand())
              {
                  // How to handle METADATA?
                  cmd.ExecuteNonQuery(string.Format(@"DROP TABLE IF EXISTS ""{0}"" ", m_fileprogtable));
                  cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" ("
                          + @"  ""FileId"" INTEGER PRIMARY KEY "
                          + @", ""TotalBlocks"" INTEGER NOT NULL, ""TotalSize"" INTEGER NOT NULL "
                          + @", ""BlocksRestored"" INTEGER NOT NULL, ""SizeRestored"" INTEGER NOT NULL "
                          + @")", m_fileprogtable));
  
                  cmd.ExecuteNonQuery(string.Format(@"DROP TABLE IF EXISTS ""{0}"" ", m_totalprogtable));
                  cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" ("
                          + @"  ""TotalFiles"" INTEGER NOT NULL, ""TotalBlocks"" INTEGER NOT NULL, ""TotalSize"" INTEGER NOT NULL "
                          + @", ""FilesFullyRestored"" INTEGER NOT NULL, ""FilesPartiallyRestored"" INTEGER NOT NULL "
                          + @", ""BlocksRestored"" INTEGER NOT NULL, ""SizeRestored"" INTEGER NOT NULL "
                          + @")", m_totalprogtable));
  
                  if (createFilesNewlyDoneTracker)
                  {
                      cmd.ExecuteNonQuery(string.Format(@"DROP TABLE IF EXISTS ""{0}"" ", m_filesnewlydonetable));
                      cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" ("
                              + @"  ""ID"" INTEGER PRIMARY KEY "
                              + @")", m_filesnewlydonetable));
                  }
  
                  try
                  {
                      // Initialize statistics with File- and Block-Data (it is valid to already have restored blocks in files)
                      // A rebuild with this function should be valid anytime.
                      // Note: FilesNewlyDone is NOT initialized, as in initialization nothing is really new.
                      string sql;
  
                      sql = string.Format(
                            @" INSERT INTO ""{0}"" (""FileId"", ""TotalBlocks"", ""TotalSize"", ""BlocksRestored"", ""SizeRestored"") "
                          + @" SELECT   ""F"".""ID"", IFNULL(COUNT(""B"".""ID""), 0), IFNULL(SUM(""B"".""Size""), 0)"
                          + @"        , IFNULL(COUNT(CASE ""B"".""Restored"" WHEN 1 THEN ""B"".""ID"" ELSE NULL END), 0) "
                          + @"        , IFNULL(SUM(CASE ""B"".""Restored"" WHEN 1 THEN ""B"".""Size"" ELSE 0 END), 0) "
                          + @"   FROM ""{1}"" ""F"" LEFT JOIN ""{2}"" ""B""" // allow for emtpy files (no data Blocks)
                          + @"        ON  ""B"".""FileID"" = ""F"".""ID"" "
                          + @"  WHERE ""B"".""Metadata"" IS NOT 1 " // Use "IS" because of Left Join
                          + @"  GROUP BY ""F"".""ID"" "
                          , m_fileprogtable, m_tempfiletable, m_tempblocktable);
  
                      // Will be one row per file.
                      int fileCnt = cmd.ExecuteNonQuery(sql);
  
                      sql = string.Format(
                            @"INSERT INTO ""{0}"" ("
                          + @"  ""TotalFiles"", ""TotalBlocks"", ""TotalSize"" "
                          + @", ""FilesFullyRestored"", ""FilesPartiallyRestored"", ""BlocksRestored"", ""SizeRestored"""
                          + @" ) "
                          + @" SELECT   IFNULL(COUNT(""P"".""FileId""), 0), IFNULL(SUM(""P"".""TotalBlocks""), 0), IFNULL(SUM(""P"".""TotalSize""), 0) "
                          + @"        , IFNULL(COUNT(CASE WHEN ""P"".""BlocksRestored"" = ""P"".""TotalBlocks"" THEN 1 ELSE NULL END), 0) "
                          + @"        , IFNULL(COUNT(CASE WHEN ""P"".""BlocksRestored"" BETWEEN 1 AND ""P"".""TotalBlocks"" - 1 THEN 1 ELSE NULL END), 0) "
                          + @"        , IFNULL(SUM(""P"".""BlocksRestored""), 0), IFNULL(SUM(""P"".""SizeRestored""), 0) "
                          + @"   FROM ""{1}"" ""P"" "
                          , m_totalprogtable, m_fileprogtable);
  
                      // Will result in a single line (no support to also track metadata)
                      int totalStatRowCount = cmd.ExecuteNonQuery(sql);
  
                      // Finally we create TRIGGERs to keep all our statistics up to date.
                      // This is lightning fast, as SQLite uses internal hooks and our indices to do the update magic.
                      // Note: We do assume that neither files nor blocks will be added or deleted during restore process
                      //       and that the size of each block stays constant so there is no need to track that information
                      //       with additional INSERT and DELETE triggers.
  
                      // A trigger to update the file-stat entry each time a block changes restoration state.
                      sql = string.Format(
                            @"CREATE TEMPORARY TRIGGER ""TrackRestoredBlocks_{1}"" AFTER UPDATE OF ""Restored"" ON ""{1}"" "
                          + @"  WHEN OLD.""Restored"" != NEW.""Restored"" AND NEW.""Metadata"" = 0 "
                          + @"  BEGIN UPDATE ""{0}"" "
                          + @"     SET ""BlocksRestored"" = ""{0}"".""BlocksRestored"" + (NEW.""Restored"" - OLD.""Restored"") "
                          + @"       , ""SizeRestored"" = ""{0}"".""SizeRestored"" + ((NEW.""Restored"" - OLD.""Restored"") * NEW.Size) "
                          + @"   WHERE ""{0}"".""FileId"" = NEW.""FileID"" "
                          + @"  ; END "
                          , m_fileprogtable, m_tempblocktable);
                      cmd.ExecuteNonQuery(sql);
  
                      // A trigger to update total stats each time a file stat changed (nested triggering by file-stats)
                      sql = string.Format(
                            @"CREATE TEMPORARY TRIGGER ""UpdateTotalStats_{1}"" AFTER UPDATE ON ""{1}"" "
                          + @"  BEGIN UPDATE ""{0}"" "
                          + @"     SET ""FilesFullyRestored"" = ""{0}"".""FilesFullyRestored"" "
                          + @"                 + (CASE WHEN NEW.""BlocksRestored"" = NEW.""TotalBlocks"" THEN 1 ELSE 0 END) "
                          + @"                 - (CASE WHEN OLD.""BlocksRestored"" = OLD.""TotalBlocks"" THEN 1 ELSE 0 END) "
                          + @"       , ""FilesPartiallyRestored"" = ""{0}"".""FilesPartiallyRestored"" "
                          + @"                 + (CASE WHEN NEW.""BlocksRestored"" BETWEEN 1 AND NEW.""TotalBlocks"" - 1 THEN 1 ELSE 0 END) "
                          + @"                 - (CASE WHEN OLD.""BlocksRestored"" BETWEEN 1 AND OLD.""TotalBlocks"" - 1 THEN 1 ELSE 0 END) "
                          + @"       , ""BlocksRestored"" = ""{0}"".""BlocksRestored"" + NEW.""BlocksRestored"" - OLD.""BlocksRestored"" " // simple delta
                          + @"       , ""SizeRestored"" = ""{0}"".""SizeRestored"" + NEW.""SizeRestored"" - OLD.""SizeRestored"" " // simple delta
                          + @"  ; END "
                          , m_totalprogtable, m_fileprogtable);
                      cmd.ExecuteNonQuery(sql);
  
  
                      if (createFilesNewlyDoneTracker)
                      {
                          // A trigger checking if a file is done (all blocks restored in file-stat) (nested triggering by file-stats)
                          sql = string.Format(
                                @"CREATE TEMPORARY TRIGGER ""UpdateFilesNewlyDone_{1}"" AFTER UPDATE OF ""BlocksRestored"", ""TotalBlocks"" ON ""{1}"" "
                              + @"  WHEN NEW.""BlocksRestored"" = NEW.""TotalBlocks""  "
                              + @"  BEGIN "
                              + @"     INSERT OR IGNORE INTO ""{0}"" (""ID"") VALUES (NEW.""FileId""); "
                              + @"  END "
                              , m_filesnewlydonetable, m_fileprogtable);
                          cmd.ExecuteNonQuery(sql);
                      }
  
                  }
                  catch (Exception ex)
                  {
                      m_fileprogtable = null;
                      m_totalprogtable = null;
                      Console.WriteLine(ex.ToString());
                      throw;
                  }
              }
  
          }
  
  
          public Tuple<long, long> PrepareRestoreFilelist(DateTime restoretime, long[] versions, Library.Utility.IFilter filter, ILogWriter log)
          {
              var guid = Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());
  
              m_tempfiletable = "Fileset-" + guid;
              m_tempblocktable = "Blocks-" + guid;
  
              using(var cmd = m_connection.CreateCommand())
              {
                  var filesetIds = GetFilesetIDs(NormalizeDateTime(restoretime), versions).ToList();
                  while(filesetIds.Count > 0)
                  {
                      var filesetId = filesetIds[0];
                      filesetIds.RemoveAt(0);
                      
                      m_restoreTime = ParseFromEpochSeconds(cmd.ExecuteScalarInt64(@"SELECT ""Timestamp"" FROM ""Fileset"" WHERE ""ID"" = ?", 0, filesetId));
                      
                      var ix = this.FilesetTimes.Select((value, index) => new { value.Key, index })
                              .Where(n => n.Key == filesetId)
                              .Select(pair => pair.index + 1)
                              .FirstOrDefault() - 1;
                              
                      log.AddMessage(string.Format("Searching backup {0} ({1}) ...", ix, m_restoreTime));
                      
                      cmd.Parameters.Clear();
      
                      cmd.ExecuteNonQuery(string.Format(@"DROP TABLE IF EXISTS ""{0}"" ", m_tempfiletable));
                      cmd.ExecuteNonQuery(string.Format(@"DROP TABLE IF EXISTS ""{0}"" ", m_tempblocktable));
                      cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" (""ID"" INTEGER PRIMARY KEY, ""Path"" TEXT NOT NULL, ""BlocksetID"" INTEGER NOT NULL, ""MetadataID"" INTEGER NOT NULL, ""TargetPath"" TEXT NULL, ""DataVerified"" BOOLEAN NOT NULL) ", m_tempfiletable));
                      cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" (""ID"" INTEGER PRIMARY KEY, ""FileID"" INTEGER NOT NULL, ""Index"" INTEGER NOT NULL, ""Hash"" TEXT NOT NULL, ""Size"" INTEGER NOT NULL, ""Restored"" BOOLEAN NOT NULL, ""Metadata"" BOOLEAN NOT NULL)", m_tempblocktable));
                      cmd.ExecuteNonQuery(string.Format(@"CREATE INDEX ""{0}_Index"" ON ""{0}"" (""TargetPath"")", m_tempfiletable));
                      cmd.ExecuteNonQuery(string.Format(@"CREATE INDEX ""{0}_HashSizeIndex"" ON ""{0}"" (""Hash"", ""Size"")", m_tempblocktable));
                      // better suited to speed up commit on UpdateBlocks
                      cmd.ExecuteNonQuery(string.Format(@"CREATE INDEX ""{0}_FileIdIndexIndex"" ON ""{0}"" (""FileId"", ""Index"")", m_tempblocktable));
  
                      if (filter == null || filter.Empty)
                      {
                          // Simple case, restore everything
                          cmd.CommandText = string.Format(@"INSERT INTO ""{0}"" (""Path"", ""BlocksetID"", ""MetadataID"", ""DataVerified"") SELECT ""File"".""Path"", ""File"".""BlocksetID"", ""File"".""MetadataID"", 0 FROM ""File"", ""FilesetEntry"" WHERE ""File"".""ID"" = ""FilesetEntry"".""FileID"" AND ""FilesetEntry"".""FilesetID"" = ? ", m_tempfiletable);
                          cmd.AddParameter(filesetId);
                          cmd.ExecuteNonQuery();
                      }
                      else if (Library.Utility.Utility.IsFSCaseSensitive && filter is Library.Utility.FilterExpression && (filter as Library.Utility.FilterExpression).Type == Duplicati.Library.Utility.FilterType.Simple)
                      {
                          // If we get a list of filenames, the lookup table is faster
                          // unfortunately we cannot do this if the filesystem is case sensitive as
                          // SQLite only supports ASCII compares
                          using(var tr = m_connection.BeginTransaction())
                          {
                              var p = (filter as Library.Utility.FilterExpression).GetSimpleList();
                              var m_filenamestable = "Filenames-" + guid;
                              cmd.Transaction = tr;
                              cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" (""Path"" TEXT NOT NULL) ", m_filenamestable));
                              cmd.CommandText = string.Format(@"INSERT INTO ""{0}"" (""Path"") VALUES (?)", m_filenamestable);
                              cmd.AddParameter();
                              
                              foreach(var s in p)
                              {
                                  cmd.SetParameterValue(0, s);
                                  cmd.ExecuteNonQuery();
                              }
  
                              cmd.CommandText = string.Format(@"INSERT INTO ""{0}"" (""Path"", ""BlocksetID"", ""MetadataID"", ""DataVerified"") SELECT ""File"".""Path"", ""File"".""BlocksetID"", ""File"".""MetadataID"", 0 FROM ""File"", ""FilesetEntry"" WHERE ""File"".""ID"" = ""FilesetEntry"".""FileID"" AND ""FilesetEntry"".""FilesetID"" = ? AND ""Path"" IN (SELECT DISTINCT ""Path"" FROM ""{1}"") ", m_tempfiletable, m_filenamestable);
                              cmd.SetParameterValue(0, filesetId);
                              var c = cmd.ExecuteNonQuery();
                              
                              cmd.Parameters.Clear();
                              
                              if (c != p.Length && c != 0)
                              {
                                  var sb = new StringBuilder();
                                  sb.AppendLine();
                                  
                                  using(var rd = cmd.ExecuteReader(string.Format(@"SELECT ""Path"" FROM ""{0}"" WHERE ""Path"" NOT IN (SELECT ""Path"" FROM ""{1}"")", m_filenamestable, m_tempfiletable)))
                                      while (rd.Read())
                                          sb.AppendLine(rd.GetValue(0).ToString());
      
                                  var actualrestoretime = ParseFromEpochSeconds(cmd.ExecuteScalarInt64(@"SELECT ""Timestamp"" FROM ""Fileset"" WHERE ""ID"" = ?", 0, filesetId));
                                  log.AddWarning(string.Format("{0} File(s) were not found in list of files for backup at {1}, will not be restored: {2}", p.Length - c, actualrestoretime.ToLocalTime(), sb), null);
                                  cmd.Parameters.Clear();
                              }
                              
                              cmd.ExecuteNonQuery(string.Format(@"DROP TABLE IF EXISTS ""{0}"" ", m_filenamestable));
                              
                              using(new Logging.Timer("CommitPrepareFileset"))
                                  tr.Commit();
                          }
                      }
                      else
                      {
                          // Restore but filter elements based on the filter expression
                          // If this is too slow, we could add a special handler for wildcard searches too
                          cmd.CommandText = string.Format(@"SELECT ""File"".""Path"", ""File"".""BlocksetID"", ""File"".""MetadataID"" FROM ""File"", ""FilesetEntry"" WHERE ""File"".""ID"" = ""FilesetEntry"".""FileID"" AND ""FilesetID"" = ?");
                          cmd.AddParameter(filesetId);
      
                          object[] values = new object[3];
                          using(var cmd2 = m_connection.CreateCommand())
                          {
                              cmd2.CommandText = string.Format(@"INSERT INTO ""{0}"" (""Path"", ""BlocksetID"", ""MetadataID"", ""DataVerified"") VALUES (?,?,?,0)", m_tempfiletable);
                              cmd2.AddParameter();
                              cmd2.AddParameter();
                              cmd2.AddParameter();
      
                              using(var rd = cmd.ExecuteReader())
                                  while (rd.Read())
                                  {
                                      rd.GetValues(values);
                                      if (values[0] != null && values[0] != DBNull.Value && Library.Utility.FilterExpression.Matches(filter, values[0].ToString()))
                                      {
                                          cmd2.SetParameterValue(0, values[0]);
                                          cmd2.SetParameterValue(1, values[1]);
                                          cmd2.SetParameterValue(2, values[2]);
                                          cmd2.ExecuteNonQuery();
                                      }
                                  }
                          }
                      }
                      
                      
                      using(var rd = cmd.ExecuteReader(string.Format(@"SELECT COUNT(DISTINCT ""{0}"".""Path""), SUM(""Blockset"".""Length"") FROM ""{0}"", ""Blockset"" WHERE ""{0}"".""BlocksetID"" = ""Blockset"".""ID"" ", m_tempfiletable)))
                      {
                          var filecount = 0L;
                          var filesize = 0L;
  
                          if (rd.Read())
                          {
                              filecount = rd.ConvertValueToInt64(0, 0);
                              filesize = rd.ConvertValueToInt64(1, 0);
                          }
  
                          if (filecount > 0)
                          {
                              log.AddVerboseMessage("Needs to restore {0} files ({1})", filecount, Library.Utility.Utility.FormatSizeString(filesize));
                              return new Tuple<long, long>(filecount, filesize);
                          }
                      }                
                  }
              }
              
              return new Tuple<long, long>(0, 0);
          }
  
          public string GetFirstPath()
          {
              using (var cmd = m_connection.CreateCommand())
              {
                  cmd.CommandText = string.Format(@"SELECT ""Path"" FROM ""{0}"" ORDER BY LENGTH(""Path"") DESC LIMIT 1", m_tempfiletable);
  
gt:                 var v0 = cmd.ExecuteScalar();