dotnet_dbsync/TvSyncApp.cs

using Npgsql;
using Serilog;
using System.Text.Json;
using dbsync.Config;
using dbsync.Services;
using TvSyncApp.Services;
using System.Collections.Concurrent;

namespace TvSyncApp
{
    public class Program
    
    {

        public static Dictionary<string, string> directories { get; private set; } = new();

        public static async Task<int> Main(string[] args)
        {
            string settingsPath = Path.Combine(Directory.GetCurrentDirectory(), "appsettings.json");
            var settings = Settings.Load(settingsPath)
               ?? throw new Exception("Failed to load settings from appsettings.json");

            Console.WriteLine($"Log level: {settings.Global!.LogLevel}");
            Console.WriteLine($"Database type: {settings.Database!.DbType}");
            // Console.WriteLine($"TVMaze series API: {settings.Json.SeriesApi}");
            
            string rootDir = Directory.GetCurrentDirectory();

            directories = new Dictionary<string, string>
            {
                ["SERIESDIR"] = Path.Combine(rootDir, "cache", "series"),
                ["EPISODEDIR"] = Path.Combine(rootDir, "cache", "episodes"),
                ["CASTDIR"] = Path.Combine(rootDir, "cache", "cast"),
                ["CREWDIR"] = Path.Combine(rootDir, "cache", "crew"),
                ["ALIASES"] = Path.Combine(rootDir, "cache", "aliases"),
                ["CREDITS"] = Path.Combine(rootDir, "cache", "credits"),
                ["GUESTCAST"] = Path.Combine(rootDir, "cache", "guestcast"),
                ["GUESTCREW"] = Path.Combine(rootDir, "cache", "guestcrew"),
                ["CONFIG"] = Path.Combine(rootDir, "config"),
                ["TEMP"] = Path.Combine(rootDir, "temp")
            };

            CreateDirectories(rootDir);

            Log.Logger = new LoggerConfiguration()
                .MinimumLevel.Debug()
                .WriteTo.Console()
                .WriteTo.File(Path.Combine(rootDir, "log", "sync.log"))
                .CreateLogger();

            Log.Information("Starting sync process...");

            // --- Fetch series IDs from PostgreSQL ---
            string connectionString = $"Host={settings.DbSettings!.Hostname};Port={settings.DbSettings.PgsqlPort};Username={settings.DbSettings.PgsqlUsername};Password={settings.DbSettings.PgsqlPassword};Database={settings.DbSettings.DbName}";
            var service = new TvSyncService(connectionString)!;
            List<SeriesUpdate> seriesUpdates = GetSeriesIdsFromDb(connectionString);

            List<string> seriesIds = seriesUpdates
            .Select(x => x.SeriesId.ToString())
            .ToList();

            var downloadedUpdates = await service.DownloadTvMazeUpdatesAsync(
                settings.Json!.UpdatesApi!,
                directories["CONFIG"]);
            service.UpdateTvUpdatesTable(downloadedUpdates);

            var previousDict = seriesUpdates
                .ToDictionary(x => x.SeriesId, x => x.Timestamp);

            Log.Information("Fetched {Count} series IDs from tvupdates table.", seriesIds.Count);
            var updatedSeries = GetUpdatedSeriesOptimized(previousDict, downloadedUpdates);

            
            if (updatedSeries.Count == 0)
            {
                Log.Information("No new or updated series found. Exiting.");
                Log.CloseAndFlush();
                return 0; // success exit code
            }
            
            var filesNeeded = service.DetermineFilesNeeded(
                downloadedUpdates,
                previousDict,
                directories);

            var skipIds = new Dictionary<string, string>(); // load skip file here

            List<int> seriesIdsNeeded = updatedSeries.Keys.ToList();

            
            // Convert to string list if your Loader expects strings
            List<string> updateList = seriesIdsNeeded.ConvertAll(id => id.ToString());
            
            // Optionally, you can also fetch timestamps if needed
            Dictionary<string, long> updateDict = new Dictionary<string, long>();

            var loader = new Loader();

            var result = await loader.LoadJsonAsync(directories, updateList, updateDict, settings);

            Log.Information("Sync complete. Success: {Success}, Failed: {Failed}", result.Success, result.Failed);
            Log.CloseAndFlush();

            return 0;
        }
        private static List<SeriesUpdate> GetSeriesIdsFromDb(string connectionString)
        {
            var results = new List<SeriesUpdate>();

            try
            {
                using var conn = new NpgsqlConnection(connectionString);
                conn.Open();

                const string query = @"SELECT seriesid, timestamp FROM updates.tvupdates ORDER BY seriesid";

                using var cmd = new NpgsqlCommand(query, conn);
                using var reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    if (!reader.IsDBNull(0) && !reader.IsDBNull(1))
                    {
                        results.Add(new SeriesUpdate
                        {
                            SeriesId = reader.GetInt32(0),
                            Timestamp = long.Parse(reader.GetString(1))
                        });
                    }
                }

                SaveSeriesUpdatesToFile(results);
            }
            catch (Exception ex)
            {
                Log.Error(ex, "Failed to fetch series updates or manage JSON output.");
            }

            return results;
        }

        private static void SaveSeriesUpdatesToFile(List<SeriesUpdate> data)
        {
            string fileName = "previous_tvmaze_updates.json";
            string filePath = Path.Combine(directories["CONFIG"], fileName);

            if (File.Exists(filePath))
            {
                string timestamp = DateTime.UtcNow.ToString("yyyyMMddHHmmss");
                string archiveName = $"previous_tvmaze_updates_{timestamp}.json";
                string archivePath = Path.Combine(directories["CONFIG"], archiveName);

                File.Move(filePath, archivePath);
            }

            var options = new JsonSerializerOptions
            {
                WriteIndented = true
            };

            string json = JsonSerializer.Serialize(data, options);
            File.WriteAllText(filePath, json);

            var archives = Directory.GetFiles(directories["CONFIG"], "previous_tvmaze_updates_*.json")
                .Select(f => new FileInfo(f))
                .OrderByDescending(f => f.CreationTimeUtc)
                .ToList();

            foreach (var oldFile in archives.Skip(5))
            {
                oldFile.Delete();
            }

            Log.Information("Saved {Count} series updates to {FilePath}", data.Count, filePath);
        }

        public static Dictionary<int, long> GetUpdatedSeriesOptimized(
            IReadOnlyDictionary<int, long> previousDict,
            IReadOnlyDictionary<int, long> downloadedUpdates)
        {
            var result = new Dictionary<int, long>(downloadedUpdates.Count);

            int updatedCount = 0;
            int newCount = 0;

            foreach (var (seriesId, newTimestamp) in downloadedUpdates)
            {
                if (previousDict.TryGetValue(seriesId, out var oldTimestamp))
                {
                    if (newTimestamp > oldTimestamp)
                    {
                        result[seriesId] = newTimestamp;
                        updatedCount++;
                    }
                }
                else
                {
                    result[seriesId] = newTimestamp;
                    newCount++;
                }
            }

            Log.Information(
                "Series comparison complete. Updated: {Updated}, New: {New}, Total Returned: {Total}",
                updatedCount,
                newCount,
                result.Count);

            return result;
        }

        private static void CreateDirectories(string rootDir)
        {
            Directory.CreateDirectory(Path.Combine(rootDir, "log"));
            Directory.CreateDirectory(Path.Combine(rootDir, "temp"));
            Directory.CreateDirectory(Path.Combine(rootDir, "cache", "series"));
            Directory.CreateDirectory(Path.Combine(rootDir, "cache", "episodes"));
            Directory.CreateDirectory(Path.Combine(rootDir, "cache", "cast"));
            Directory.CreateDirectory(Path.Combine(rootDir, "cache", "guestcast"));
            Directory.CreateDirectory(Path.Combine(rootDir, "cache", "crew"));
        }
    }

    public class SeriesUpdate
    {
        public int SeriesId { get; set; }
        public long Timestamp { get; set; }
    }

}