context: using MediaBrowser.Common.Configuration;
  using MediaBrowser.Controller.Entities;
  using MediaBrowser.Controller.Entities.Audio;
  using MediaBrowser.Controller.Entities.Movies;
  using MediaBrowser.Controller.Entities.TV;
  using MediaBrowser.Controller.LiveTv;
  using MediaBrowser.Controller.Persistence;
  using MediaBrowser.Model.Entities;
  using MediaBrowser.Model.Logging;
  using MediaBrowser.Model.Querying;
  using MediaBrowser.Model.Serialization;
  using System;
  using System.Collections.Generic;
  using System.Data;
  using System.Globalization;
  using System.IO;
  using System.Linq;
  using System.Runtime.Serialization;
  using System.Threading;
  using System.Threading.Tasks;
  
  namespace MediaBrowser.Server.Implementations.Persistence
  {
      /// <summary>
      /// Class SQLiteItemRepository
      /// </summary>
      public class SqliteItemRepository : IItemRepository
      {
          private IDbConnection _connection;
  
          private readonly ILogger _logger;
  
          private readonly TypeMapper _typeMapper = new TypeMapper();
  
          /// <summary>
          /// Gets the name of the repository
          /// </summary>
          /// <value>The name.</value>
          public string Name
          {
              get
              {
                  return "SQLite";
              }
          }
  
          /// <summary>
          /// Gets the json serializer.
          /// </summary>
          /// <value>The json serializer.</value>
          private readonly IJsonSerializer _jsonSerializer;
  
          /// <summary>
          /// The _app paths
          /// </summary>
          private readonly IApplicationPaths _appPaths;
  
          /// <summary>
          /// The _save item command
          /// </summary>
          private IDbCommand _saveItemCommand;
  
          private readonly string _criticReviewsPath;
  
          private SqliteChapterRepository _chapterRepository;
          private SqliteMediaStreamsRepository _mediaStreamsRepository;
  
          private IDbCommand _deleteChildrenCommand;
          private IDbCommand _saveChildrenCommand;
          private IDbCommand _deleteItemCommand;
  
          private IDbCommand _deletePeopleCommand;
          private IDbCommand _savePersonCommand;
  
          private const int LatestSchemaVersion = 6;
  
          /// <summary>
          /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class.
          /// </summary>
          /// <param name="appPaths">The app paths.</param>
          /// <param name="jsonSerializer">The json serializer.</param>
          /// <param name="logManager">The log manager.</param>
          /// <exception cref="System.ArgumentNullException">
          /// appPaths
          /// or
          /// jsonSerializer
          /// </exception>
          public SqliteItemRepository(IApplicationPaths appPaths, IJsonSerializer jsonSerializer, ILogManager logManager)
          {
              if (appPaths == null)
              {
                  throw new ArgumentNullException("appPaths");
              }
              if (jsonSerializer == null)
              {
                  throw new ArgumentNullException("jsonSerializer");
              }
  
              _appPaths = appPaths;
              _jsonSerializer = jsonSerializer;
  
              _criticReviewsPath = Path.Combine(_appPaths.DataPath, "critic-reviews");
  
              _logger = logManager.GetLogger(GetType().Name);
  
              var chapterDbFile = Path.Combine(_appPaths.DataPath, "chapters.db");
              var chapterConnection = SqliteExtensions.ConnectToDb(chapterDbFile, _logger).Result;
              _chapterRepository = new SqliteChapterRepository(chapterConnection, logManager);
  
              var mediaStreamsDbFile = Path.Combine(_appPaths.DataPath, "mediainfo.db");
              var mediaStreamsConnection = SqliteExtensions.ConnectToDb(mediaStreamsDbFile, _logger).Result;
              _mediaStreamsRepository = new SqliteMediaStreamsRepository(mediaStreamsConnection, logManager);
          }
  
          /// <summary>
          /// Opens the connection to the database
          /// </summary>
          /// <returns>Task.</returns>
          public async Task Initialize()
          {
              var dbFile = Path.Combine(_appPaths.DataPath, "library.db");
  
              _connection = await SqliteExtensions.ConnectToDb(dbFile, _logger).ConfigureAwait(false);
  
              string[] queries = {
  
                                  "create table if not exists TypedBaseItems (guid GUID primary key, type TEXT, data BLOB)",
                                  "create index if not exists idx_TypedBaseItems on TypedBaseItems(guid)",
  
                                  "create table if not exists ChildrenIds (ParentId GUID, ItemId GUID, PRIMARY KEY (ParentId, ItemId))",
                                  "create index if not exists idx_ChildrenIds on ChildrenIds(ParentId,ItemId)",
  
                                  "create table if not exists People (ItemId GUID, Name TEXT NOT NULL, Role TEXT, PersonType TEXT, SortOrder int, ListOrder int)",
  
                                  //pragmas
                                  "pragma temp_store = memory",
  
                                  "pragma shrink_memory"
                                 };
  
              _connection.RunQueries(queries, _logger);
  
              _connection.AddColumn(_logger, "TypedBaseItems", "Path", "Text");
              _connection.AddColumn(_logger, "TypedBaseItems", "StartDate", "DATETIME");
              _connection.AddColumn(_logger, "TypedBaseItems", "EndDate", "DATETIME");
              _connection.AddColumn(_logger, "TypedBaseItems", "ChannelId", "Text");
              _connection.AddColumn(_logger, "TypedBaseItems", "IsMovie", "BIT");
              _connection.AddColumn(_logger, "TypedBaseItems", "IsSports", "BIT");
              _connection.AddColumn(_logger, "TypedBaseItems", "IsKids", "BIT");
              _connection.AddColumn(_logger, "TypedBaseItems", "CommunityRating", "Float");
              _connection.AddColumn(_logger, "TypedBaseItems", "CustomRating", "Text");
              _connection.AddColumn(_logger, "TypedBaseItems", "IndexNumber", "INT");
              _connection.AddColumn(_logger, "TypedBaseItems", "IsLocked", "BIT");
              _connection.AddColumn(_logger, "TypedBaseItems", "Name", "Text");
              _connection.AddColumn(_logger, "TypedBaseItems", "OfficialRating", "Text");
  
              _connection.AddColumn(_logger, "TypedBaseItems", "MediaType", "Text");
              _connection.AddColumn(_logger, "TypedBaseItems", "Overview", "Text");
              _connection.AddColumn(_logger, "TypedBaseItems", "ParentIndexNumber", "INT");
              _connection.AddColumn(_logger, "TypedBaseItems", "PremiereDate", "DATETIME");
              _connection.AddColumn(_logger, "TypedBaseItems", "ProductionYear", "INT");
              _connection.AddColumn(_logger, "TypedBaseItems", "ParentId", "GUID");
              _connection.AddColumn(_logger, "TypedBaseItems", "Genres", "Text");
              _connection.AddColumn(_logger, "TypedBaseItems", "ParentalRatingValue", "INT");
              _connection.AddColumn(_logger, "TypedBaseItems", "SchemaVersion", "INT");
              _connection.AddColumn(_logger, "TypedBaseItems", "SortName", "Text");
              _connection.AddColumn(_logger, "TypedBaseItems", "RunTimeTicks", "BIGINT");
  
              _connection.AddColumn(_logger, "TypedBaseItems", "OfficialRatingDescription", "Text");
              _connection.AddColumn(_logger, "TypedBaseItems", "HomePageUrl", "Text");
              _connection.AddColumn(_logger, "TypedBaseItems", "VoteCount", "INT");
              _connection.AddColumn(_logger, "TypedBaseItems", "DisplayMediaType", "Text");
              _connection.AddColumn(_logger, "TypedBaseItems", "DateCreated", "DATETIME");
              _connection.AddColumn(_logger, "TypedBaseItems", "DateModified", "DATETIME");
  
              _connection.AddColumn(_logger, "TypedBaseItems", "ForcedSortName", "Text");
              _connection.AddColumn(_logger, "TypedBaseItems", "IsOffline", "BIT");
  
              PrepareStatements();
  
              _mediaStreamsRepository.Initialize();
              _chapterRepository.Initialize();
          }
  
          /// <summary>
          /// The _write lock
          /// </summary>
          private readonly SemaphoreSlim _writeLock = new SemaphoreSlim(1, 1);
  
          private string[] _retriveItemColumns =
          {
              "type",
              "data",
              "IsOffline"
          };
  
          /// <summary>
          /// Prepares the statements.
          /// </summary>
          private void PrepareStatements()
          {
              var saveColumns = new List<string>
              {
                  "guid",
                  "type",
                  "data",
  				"Path",
                  "StartDate",
                  "EndDate",
                  "ChannelId",
                  "IsKids",
                  "IsMovie",
                  "IsSports",
                  "CommunityRating",
                  "CustomRating",
                  "IndexNumber",
                  "IsLocked",
                  "Name",
                  "OfficialRating",
                  "MediaType",
                  "Overview",
                  "ParentIndexNumber",
                  "PremiereDate",
                  "ProductionYear",
                  "ParentId",
                  "Genres",
                  "ParentalRatingValue",
                  "SchemaVersion",
                  "SortName",
                  "RunTimeTicks",
                  "OfficialRatingDescription",
                  "HomePageUrl",
                  "VoteCount",
                  "DisplayMediaType",
                  "DateCreated",
                  "DateModified",
                  "ForcedSortName",
                  "IsOffline"
              };
              _saveItemCommand = _connection.CreateCommand();
              _saveItemCommand.CommandText = "replace into TypedBaseItems (" + string.Join(",", saveColumns.ToArray()) + ") values (";
  
              for (var i = 1; i <= saveColumns.Count; i++)
              {
                  if (i > 1)
                  {
                      _saveItemCommand.CommandText += ",";
                  }
                  _saveItemCommand.CommandText += "@" + i.ToString(CultureInfo.InvariantCulture);
  
                  _saveItemCommand.Parameters.Add(_saveItemCommand, "@" + i.ToString(CultureInfo.InvariantCulture));
              }
              _saveItemCommand.CommandText += ")";
  
              _deleteChildrenCommand = _connection.CreateCommand();
              _deleteChildrenCommand.CommandText = "delete from ChildrenIds where ParentId=@ParentId";
              _deleteChildrenCommand.Parameters.Add(_deleteChildrenCommand, "@ParentId");
  
              _deleteItemCommand = _connection.CreateCommand();
              _deleteItemCommand.CommandText = "delete from TypedBaseItems where guid=@Id";
              _deleteItemCommand.Parameters.Add(_deleteItemCommand, "@Id");
  
  
gt:             _saveChildrenCommand = _connection.CreateCommand();