context: using System;
  using System.Collections.Generic;
  using System.Globalization;
  using System.IO;
  using System.Linq;
  using System.Numerics;
  using System.Text.RegularExpressions;
  
  using Microsoft.EntityFrameworkCore;
  using Newtonsoft.Json;
  
  using ACE.Adapter.GDLE;
  using ACE.Adapter.Lifestoned;
  using ACE.Common.Extensions;
  using ACE.Database;
  using ACE.Database.Models.World;
  using ACE.Database.SQLFormatters.World;
  using ACE.Entity;
  using ACE.Entity.Enum;
  using ACE.Entity.Enum.Properties;
  using ACE.Server.Entity;
  using ACE.Server.Factories;
  using ACE.Server.Managers;
  using ACE.Server.Network;
  using ACE.Server.Network.GameMessages.Messages;
  using ACE.Server.Physics.Extensions;
  using ACE.Server.WorldObjects;
  
  namespace ACE.Server.Command.Handlers.Processors
  {
      public class DeveloperContentCommands
      {
          public enum FileType
          {
              Undefined,
              Encounter,
              LandblockInstance,
              Quest,
              Recipe,
              Spell,
              Weenie,
          }
  
          public static FileType GetContentType(string[] parameters, ref string param)
          {
              for (var i = 0; i < parameters.Length; i++)
              {
                  var otherIdx = i == 0 ? 1 : 0;
  
                  param = parameters[otherIdx];
  
                  var fileType = parameters[i].ToLower();
  
                  if (fileType.StartsWith("landblock"))
                      return FileType.LandblockInstance;
                  else if (fileType.StartsWith("quest"))
                      return FileType.Quest;
                  else if (fileType.StartsWith("recipe"))
                      return FileType.Recipe;
                  else if (fileType.StartsWith("weenie"))
                      return FileType.Weenie;
              }
              return FileType.Undefined;
          }
  
          [CommandHandler("import-json", AccessLevel.Developer, CommandHandlerFlag.None, 1, "Imports json data from the Content folder", "<wcid>")]
          public static void HandleImportJson(Session session, params string[] parameters)
          {
              var param = parameters[0];
              var contentType = FileType.Weenie;
  
              if (parameters.Length > 1)
              {
                  contentType = GetContentType(parameters, ref param);
  
                  if (contentType == FileType.Undefined)
                  {
                      CommandHandlerHelper.WriteOutputInfo(session, $"Unknown content type '{parameters[1]}'");
                      return;
                  }
              }
              switch (contentType)
              {
                  case FileType.LandblockInstance:
                      ImportJsonLandblock(session, param);
                      break;
  
                  case FileType.Quest:
                      ImportJsonQuest(session, param);
                      break;
  
                  case FileType.Recipe:
                      ImportJsonRecipe(session, param);
                      break;
  
                  case FileType.Weenie:
                      ImportJsonWeenie(session, param);
                      break;
              }
          }
  
          public static void ImportJsonWeenie(Session session, string wcid)
          {
              DirectoryInfo di = VerifyContentFolder(session);
              if (!di.Exists) return;
  
              var sep = Path.DirectorySeparatorChar;
  
              var json_folder = $"{di.FullName}{sep}json{sep}weenies{sep}";
  
              var prefix = wcid + " - ";
  
              if (wcid.Equals("all", StringComparison.OrdinalIgnoreCase))
                  prefix = "";
  
              di = new DirectoryInfo(json_folder);
  
              var files = di.Exists ? di.GetFiles($"{prefix}*.json") : null;
  
              if (files == null || files.Length == 0)
              {
                  CommandHandlerHelper.WriteOutputInfo(session, $"Couldn't find {json_folder}{prefix}*.json");
                  return;
              }
  
              foreach (var file in files)
                  ImportJsonWeenie(session, json_folder, file.Name);
          }
  
          public static void ImportJsonRecipe(Session session, string recipeId)
          {
              DirectoryInfo di = VerifyContentFolder(session);
              if (!di.Exists) return;
  
              var sep = Path.DirectorySeparatorChar;
  
              var json_folder = $"{di.FullName}{sep}json{sep}recipes{sep}";
  
              var prefix = recipeId.PadLeft(5, '0') + " - ";
  
              if (recipeId.Equals("all", StringComparison.OrdinalIgnoreCase))
                  prefix = "";
  
              di = new DirectoryInfo(json_folder);
  
              var files = di.Exists ? di.GetFiles($"{prefix}*.json") : null;
  
              if (files == null || files.Length == 0)
              {
                  CommandHandlerHelper.WriteOutputInfo(session, $"Couldn't find {json_folder}{prefix}*.json");
                  return;
              }
  
              foreach (var file in files)
                  ImportJsonRecipe(session, json_folder, file.Name);
          }
  
          public static void ImportJsonLandblock(Session session, string landblockId)
          {
              DirectoryInfo di = VerifyContentFolder(session);
              if (!di.Exists) return;
  
              var sep = Path.DirectorySeparatorChar;
  
              var json_folder = $"{di.FullName}{sep}json{sep}landblocks{sep}";
  
              var prefix = landblockId;
  
              if (landblockId.Equals("all", StringComparison.OrdinalIgnoreCase))
                  prefix = "";
  
              di = new DirectoryInfo(json_folder);
  
              var files = di.Exists ? di.GetFiles($"{prefix}*.json") : null;
  
              if (files == null || files.Length == 0)
              {
                  CommandHandlerHelper.WriteOutputInfo(session, $"Couldn't find {json_folder}{prefix}*.json");
                  return;
              }
  
              foreach (var file in files)
                  ImportJsonLandblock(session, json_folder, file.Name);
          }
  
          public static void ImportJsonQuest(Session session, string questName)
          {
              DirectoryInfo di = VerifyContentFolder(session);
              if (!di.Exists) return;
  
              var sep = Path.DirectorySeparatorChar;
  
              var json_folder = $"{di.FullName}{sep}json{sep}quests{sep}";
  
              var prefix = questName;
  
              if (questName.Equals("all", StringComparison.OrdinalIgnoreCase))
                  prefix = "";
  
              di = new DirectoryInfo(json_folder);
  
              var files = di.Exists ? di.GetFiles($"{prefix}*.json") : null;
  
              if (files == null || files.Length == 0)
              {
                  CommandHandlerHelper.WriteOutputInfo(session, $"Couldn't find {json_folder}{prefix}*.json");
                  return;
              }
  
              foreach (var file in files)
                  ImportJsonQuest(session, json_folder, file.Name);
          }
  
          [CommandHandler("import-sql", AccessLevel.Developer, CommandHandlerFlag.None, 1, "Imports sql data from the Content folder", "<wcid>")]
          public static void HandleImportSQL(Session session, params string[] parameters)
          {
              var param = parameters[0];
              var contentType = FileType.Weenie;
  
              if (parameters.Length > 1)
              {
                  contentType = GetContentType(parameters, ref param);
  
                  if (contentType == FileType.Undefined)
                  {
                      CommandHandlerHelper.WriteOutputInfo(session, $"Unknown content type '{parameters[1]}'");
                      return;
                  }
              }
              try
              {
                  switch (contentType)
                  {
                      case FileType.LandblockInstance:
                          ImportSQLLandblock(session, param);
                          break;
  
                      case FileType.Quest:
                          ImportSQLQuest(session, param);
                          break;
  
                      case FileType.Recipe:
                          ImportSQLRecipe(session, param);
                          break;
  
                      case FileType.Weenie:
                          ImportSQLWeenie(session, param);
                          break;
                  }
              }
              catch(Exception e)
              {
                  CommandHandlerHelper.WriteOutputError(session, $"There was an error importing the SQL:\n\n{e.Message}");
              }
          }
  
          public static void ImportSQLWeenie(Session session, string wcid)
          {
              DirectoryInfo di = VerifyContentFolder(session);
              if (!di.Exists) return;
  
              var sep = Path.DirectorySeparatorChar;
  
              var sql_folder = $"{di.FullName}{sep}sql{sep}weenies{sep}";
  
              var prefix = wcid.PadLeft(5, '0') + " ";
  
              if (wcid.Equals("all", StringComparison.OrdinalIgnoreCase))
                  prefix = "";
  
              di = new DirectoryInfo(sql_folder);
  
              var files = di.Exists ? di.GetFiles($"{prefix}*.sql") : null;
  
              if (files == null || files.Length == 0)
              {
                  CommandHandlerHelper.WriteOutputInfo(session, $"Couldn't find {sql_folder}{prefix}*.sql");
                  return;
              }
  
              foreach (var file in files)
                  ImportSQLWeenie(session, sql_folder, file.Name);
          }
  
          public static void ImportSQLRecipe(Session session, string recipeId)
          {
              DirectoryInfo di = VerifyContentFolder(session);
              if (!di.Exists) return;
  
              var sep = Path.DirectorySeparatorChar;
  
              var sql_folder = $"{di.FullName}{sep}sql{sep}recipes{sep}";
  
              var prefix = recipeId.PadLeft(5, '0') + " ";
  
              if (recipeId.Equals("all", StringComparison.OrdinalIgnoreCase))
                  prefix = "";
  
              di = new DirectoryInfo(sql_folder);
  
              var files = di.Exists ? di.GetFiles($"{prefix}*.sql") : null;
  
              if (files == null || files.Length == 0)
              {
                  CommandHandlerHelper.WriteOutputInfo(session, $"Couldn't find {sql_folder}{prefix}*.sql");
                  return;
              }
  
              foreach (var file in files)
                  ImportSQLRecipe(session, sql_folder, file.Name);
          }
  
          public static void ImportSQLLandblock(Session session, string landblockId)
          {
              DirectoryInfo di = VerifyContentFolder(session);
              if (!di.Exists) return;
  
              var sep = Path.DirectorySeparatorChar;
  
              var sql_folder = $"{di.FullName}{sep}sql{sep}landblocks{sep}";
  
              var prefix = landblockId;
  
              if (landblockId.Equals("all", StringComparison.OrdinalIgnoreCase))
                  prefix = "";
  
              di = new DirectoryInfo(sql_folder);
  
              var files = di.Exists ? di.GetFiles($"{prefix}*.sql") : null;
  
              if (files == null || files.Length == 0)
              {
                  CommandHandlerHelper.WriteOutputInfo(session, $"Couldn't find {sql_folder}{prefix}*.sql");
                  return;
              }
  
              foreach (var file in files)
                  ImportSQLLandblock(session, sql_folder, file.Name);
          }
  
          public static void ImportSQLQuest(Session session, string questName)
          {
              DirectoryInfo di = VerifyContentFolder(session);
              if (!di.Exists) return;
  
              var sep = Path.DirectorySeparatorChar;
  
              var sql_folder = $"{di.FullName}{sep}sql{sep}quests{sep}";
  
              var prefix = questName;
  
              if (questName.Equals("all", StringComparison.OrdinalIgnoreCase))
                  prefix = "";
  
              di = new DirectoryInfo(sql_folder);
  
              var files = di.Exists ? di.GetFiles($"{prefix}*.sql") : null;
  
              if (files == null || files.Length == 0)
              {
                  CommandHandlerHelper.WriteOutputInfo(session, $"Couldn't find {sql_folder}{prefix}*.sql");
                  return;
              }
  
              foreach (var file in files)
                  ImportSQLQuest(session, sql_folder, file.Name);
          }
  
          /// <summary>
          /// Returns the absolute content folder path, and verifies it exists
          /// </summary>
          private static DirectoryInfo VerifyContentFolder(Session session, bool showError = true)
          {
              var content_folder = PropertyManager.GetString("content_folder").Item;
  
              var sep = Path.DirectorySeparatorChar;
  
              // handle relative path
              if (content_folder.StartsWith("."))
              {
                  var cwd = Directory.GetCurrentDirectory() + sep;
                  content_folder = cwd + content_folder;
              }
  
              var di = new DirectoryInfo(content_folder);
  
              if (!di.Exists && showError)
              {
                  CommandHandlerHelper.WriteOutputInfo(session, $"Couldn't find content folder: {di.FullName}");
                  CommandHandlerHelper.WriteOutputInfo(session, "To set your content folder, /modifystring content_folder <path>");
              }
              return di;
          }
  
          /// <summary>
          /// Converts JSON to SQL, imports to database, and clears the weenie cache
          /// </summary>
          private static void ImportJsonWeenie(Session session, string json_folder, string json_file)
          {
              if (!uint.TryParse(Regex.Match(json_file, @"\d+").Value, out var wcid))
              {
                  CommandHandlerHelper.WriteOutputInfo(session, $"Couldn't find wcid from {json_file}");
                  return;
              }
  
              // convert json -> sql
              var sqlFile = json2sql_weenie(session, json_folder, json_file);
              if (sqlFile == null) return;
  
              // import sql to db
              var sql_folder = json_folder.Replace("json", "sql");
              ImportSQL(sql_folder + sqlFile);
              CommandHandlerHelper.WriteOutputInfo(session, $"Imported {sqlFile}");
  
              // clear this weenie out of the cache
              DatabaseManager.World.ClearCachedWeenie(wcid);
          }
  
          private static void ImportJsonRecipe(Session session, string json_folder, string json_file)
          {
              if (!uint.TryParse(Regex.Match(json_file, @"\d+").Value, out var recipeId))
              {
                  CommandHandlerHelper.WriteOutputInfo(session, $"Couldn't find recipe id from {json_file}");
                  return;
              }
  
              // convert json -> sql
              var sqlFile = json2sql_recipe(session, json_folder, json_file);
              if (sqlFile == null) return;
  
              // import sql to db
              var sql_folder = json_folder.Replace("json", "sql");
              ImportSQL(sql_folder + sqlFile);
              CommandHandlerHelper.WriteOutputInfo(session, $"Imported {sqlFile}");
  
              // clear recipe cache
              DatabaseManager.World.ClearCookbookCache();
          }
  
          private static void ImportJsonLandblock(Session session, string json_folder, string json_file)
          {
              if (!ushort.TryParse(Regex.Match(json_file, @"[0-9A-F]{4}", RegexOptions.IgnoreCase).Value, NumberStyles.HexNumber, CultureInfo.InvariantCulture, out var landblockId))
              {
                  CommandHandlerHelper.WriteOutputInfo(session, $"Couldn't find landblock id from {json_file}");
                  return;
              }
  
              // convert json -> sql
              var sqlFile = json2sql_landblock(session, json_folder, json_file);
              if (sqlFile == null) return;
  
              // import sql to db
              var sql_folder = json_folder.Replace("json", "sql");
              ImportSQL(sql_folder + sqlFile);
              CommandHandlerHelper.WriteOutputInfo(session, $"Imported {sqlFile}");
  
              // clear any cached instances for this landblock
              DatabaseManager.World.ClearCachedInstancesByLandblock(landblockId);
          }
  
          private static void ImportJsonQuest(Session session, string json_folder, string json_file)
          {
              var questName = json_file.TrimEnd(".json");
  
              // convert json -> sql
              var sqlFile = json2sql_quest(session, json_folder, json_file);
              if (sqlFile == null) return;
  
              // import sql to db
              var sql_folder = json_folder.Replace("json", "sql");
              ImportSQL(sql_folder + sqlFile);
              CommandHandlerHelper.WriteOutputInfo(session, $"Imported {sqlFile}");
  
              // clear cached quest
              DatabaseManager.World.ClearCachedQuest(questName);
          }
  
          public static WeenieSQLWriter WeenieSQLWriter;
  
          /// <summary>
          /// Converts a json file to sql file
          /// </summary>
          public static string json2sql_weenie(Session session, string folder, string json_filename)
          {
              var json_file = folder + json_filename;
  
              // read json into lsd weenie
              var success = LifestonedLoader.TryLoadWeenie(json_file, out var weenie);
  
              if (!success)
              {
                  CommandHandlerHelper.WriteOutputInfo(session, $"Failed to load {json_file}");
                  return null;
              }
  
              // convert to ace weenie
              success = LifestonedConverter.TryConvert(weenie, out var output);
  
              if (!success)
              {
                  CommandHandlerHelper.WriteOutputInfo(session, $"Failed to convert {json_file}");
                  return null;
              }
  
              // output to sql
              var sqlFolder = folder.Replace("json", "sql");
  
              var di = new DirectoryInfo(sqlFolder);
  
              if (!di.Exists)
                  di.Create();
  
              var sqlFilename = "";
  
              try
              {
                  if (WeenieSQLWriter == null)
                  {
                      WeenieSQLWriter = new WeenieSQLWriter();
                      WeenieSQLWriter.WeenieNames = DatabaseManager.World.GetAllWeenieNames();
                      WeenieSQLWriter.SpellNames = DatabaseManager.World.GetAllSpellNames();
                      WeenieSQLWriter.TreasureDeath = DatabaseManager.World.GetAllTreasureDeath();
                      WeenieSQLWriter.TreasureWielded = DatabaseManager.World.GetAllTreasureWielded();
                  }
  
                  if (output.LastModified == DateTime.MinValue)
                      output.LastModified = DateTime.UtcNow;
  
                  sqlFilename = WeenieSQLWriter.GetDefaultFileName(output);
                  var sqlFile = new StreamWriter(sqlFolder + sqlFilename);
  
                  WeenieSQLWriter.CreateSQLDELETEStatement(output, sqlFile);
                  sqlFile.WriteLine();
  
                  WeenieSQLWriter.CreateSQLINSERTStatement(output, sqlFile);
  
                  var metadata = new Adapter.GDLE.Models.Metadata(weenie);
                  if (metadata.HasInfo)
                  {
                      var jsonEx = JsonConvert.SerializeObject(metadata, LifestonedConverter.SerializerSettings);
                      sqlFile.WriteLine($"\n/* Lifestoned Changelog:\n{jsonEx}\n*/");
                  }
  
                  sqlFile.Close();
              }
              catch (Exception e)
              {
                  Console.WriteLine(e);
                  CommandHandlerHelper.WriteOutputInfo(session, $"Failed to convert {json_file}");
                  return null;
              }
  
              CommandHandlerHelper.WriteOutputInfo(session, $"Converted {json_filename} to {sqlFilename}");
  
              return sqlFilename;
          }
  
          public static CookBookSQLWriter CookBookSQLWriter;
          public static RecipeSQLWriter RecipeSQLWriter;
  
          public static string json2sql_recipe(Session session, string folder, string json_filename)
          {
              var json_file = folder + json_filename;
  
              // read json into lsd recipe
              var success = GDLELoader.TryLoadRecipeCombined(json_file, out var result);
  
              if (!success)
              {
                  CommandHandlerHelper.WriteOutputInfo(session, $"Failed to load {json_file}");
                  return null;
              }
  
              // convert to ace cookbooks + recipe
              success = GDLEConverter.TryConvert(result, out var cookbooks, out var recipe);
  
              if (!success)
              {
                  CommandHandlerHelper.WriteOutputInfo(session, $"Failed to convert {json_file}");
                  return null;
              }
  
              // output to sql
              var sqlFolder = folder.Replace("json", "sql");
  
              var di = new DirectoryInfo(sqlFolder);
  
              if (!di.Exists)
                  di.Create();
  
              var sqlFilename = "";
  
              try
              {
                  if (RecipeSQLWriter == null)
                  {
                      RecipeSQLWriter = new RecipeSQLWriter();
                      RecipeSQLWriter.WeenieNames = DatabaseManager.World.GetAllWeenieNames();
                  }
  
                  if (CookBookSQLWriter == null)
                  {
                      CookBookSQLWriter = new CookBookSQLWriter();
                      CookBookSQLWriter.WeenieNames = DatabaseManager.World.GetAllWeenieNames();
                  }
  
                  if (recipe.LastModified == DateTime.MinValue)
                      recipe.LastModified = DateTime.UtcNow;
  
                  foreach (var cookbook in cookbooks)
                  {
                      if (cookbook.LastModified == DateTime.MinValue)
                          cookbook.LastModified = DateTime.UtcNow;
                  }
  
                  sqlFilename = RecipeSQLWriter.GetDefaultFileName(recipe, cookbooks);
                  var sqlFile = new StreamWriter(sqlFolder + sqlFilename);
  
                  RecipeSQLWriter.CreateSQLDELETEStatement(recipe, sqlFile);
                  sqlFile.WriteLine();
  
                  RecipeSQLWriter.CreateSQLINSERTStatement(recipe, sqlFile);
                  sqlFile.WriteLine();
  
                  CookBookSQLWriter.CreateSQLDELETEStatement(cookbooks, sqlFile);
                  sqlFile.WriteLine();
  
                  CookBookSQLWriter.CreateSQLINSERTStatement(cookbooks, sqlFile);
  
                  sqlFile.Close();
              }
              catch (Exception e)
              {
                  Console.WriteLine(e);
                  CommandHandlerHelper.WriteOutputInfo(session, $"Failed to convert {json_file}");
                  return null;
              }
  
              CommandHandlerHelper.WriteOutputInfo(session, $"Converted {json_filename} to {sqlFilename}");
  
              return sqlFilename;
          }
  
          public static string json2sql_landblock(Session session, string folder, string json_filename)
          {
              var json_file = folder + json_filename;
  
              // read json into gdle spawnmap
              var success = GDLELoader.TryLoadLandblock(json_file, out var result);
  
              if (!success)
              {
                  CommandHandlerHelper.WriteOutputInfo(session, $"Failed to load {json_file}");
                  return null;
              }
  
              // convert to ace landblock_instances
              success = GDLEConverter.TryConvert(result, out var landblockInstances, out var landblockInstanceLinks);
  
              if (!success)
              {
                  CommandHandlerHelper.WriteOutputInfo(session, $"Failed to convert {json_file}");
                  return null;
              }
  
              // link up instances
              // TODO: move this to TryConvert
              foreach (var link in landblockInstanceLinks)
              {
                  var parent = landblockInstances.FirstOrDefault(i => i.Guid == link.ParentGuid);
                  if (parent == null)
                  {
                      CommandHandlerHelper.WriteOutputInfo(session, $"Couldn't find parent guid for {link.ParentGuid:X8}");
                      continue;
                  }
                  parent.LandblockInstanceLink.Add(link);
  
                  var child = landblockInstances.FirstOrDefault(i => i.Guid == link.ChildGuid);
                  if (child == null)
                  {
                      CommandHandlerHelper.WriteOutputInfo(session, $"Couldn't find child guid for {link.ChildGuid:X8}");
                      continue;
                  }
                  child.IsLinkChild = true;
              }
  
              // output to sql
              var sqlFolder = folder.Replace("spawnmaps", "landblock_instances").Replace("json", "sql");
  
              var di = new DirectoryInfo(sqlFolder);
  
              if (!di.Exists)
                  di.Create();
  
              var sqlFilename = "";
  
              try
              {
                  if (LandblockInstanceWriter == null)
                  {
                      LandblockInstanceWriter = new LandblockInstanceWriter();
                      LandblockInstanceWriter.WeenieNames = DatabaseManager.World.GetAllWeenieNames();
                  }
  
                  foreach (var landblockInstance in landblockInstances)
                  {
                      if (landblockInstance.LastModified == DateTime.MinValue)
                          landblockInstance.LastModified = DateTime.UtcNow;
                  }
  
                  foreach (var landblockInstanceLink in landblockInstanceLinks)
                  {
                      if (landblockInstanceLink.LastModified == DateTime.MinValue)
                          landblockInstanceLink.LastModified = DateTime.UtcNow;
                  }
  
                  sqlFilename = LandblockInstanceWriter.GetDefaultFileName(landblockInstances[0]);
                  var sqlFile = new StreamWriter(sqlFolder + sqlFilename);
  
                  LandblockInstanceWriter.CreateSQLDELETEStatement(landblockInstances, sqlFile);
                  sqlFile.WriteLine();
  
                  LandblockInstanceWriter.CreateSQLINSERTStatement(landblockInstances, sqlFile);
  
                  sqlFile.Close();
              }
              catch (Exception e)
              {
                  Console.WriteLine(e);
                  CommandHandlerHelper.WriteOutputInfo(session, $"Failed to convert {json_file}");
                  return null;
              }
  
              CommandHandlerHelper.WriteOutputInfo(session, $"Converted {json_filename} to {sqlFilename}");
  
              return sqlFilename;
          }
  
          public static QuestSQLWriter QuestSQLWriter;
  
          public static string json2sql_quest(Session session, string folder, string json_filename)
          {
              var json_file = folder + json_filename;
  
              // read json quest
              var success = GDLELoader.TryLoadQuest(json_file, out var result);
  
              if (!success)
              {
                  CommandHandlerHelper.WriteOutputInfo(session, $"Failed to load {json_file}");
                  return null;
              }
  
              // convert to sql quest
              success = GDLEConverter.TryConvert(result, out var quest);
  
              if (!success)
              {
                  CommandHandlerHelper.WriteOutputInfo(session, $"Failed to convert {json_file}");
                  return null;
              }
  
              // output to sql
  
gt:             var sqlFolder = folder.Replace("json", "sql");