using System.Data.OleDb;
using System.Text;
using System.Text.Json;
using Omika.Data.HfSql.Connection;

namespace Omika.Tests.Connection;

/// <summary>
/// Sonde RUNTIME (lecture seule) : résout la racine documentaire et un échantillon
/// de photos Omika pour valider le chemin physique réel. N'écrit jamais en base.
/// Résultats dans artifacts/runtime-photopath.txt.
/// </summary>
public class PhotoPathRuntimeTests
{
    private const string ConfigPath = @"D:\Avalonia\Omika\config\hfsql-test.local.json";
    private const string OutputPath = @"D:\Avalonia\Omika\artifacts\runtime-photopath.txt";

    [Fact]
    public async Task Runtime_PhotoPath_Resolve()
    {
        var sb = new StringBuilder();
        sb.AppendLine("=== OMIKA PHOTO PATH — RUNTIME PROBE (read-only) ===");

        var config = LoadConfig();
        if (config is null)
        {
            sb.AppendLine("CONFIG: MISSING (skip)");
            await File.WriteAllTextAsync(OutputPath, sb.ToString());
            return;
        }

        var options = new HfSqlConnectionOptions
        {
            Server = config.Server,
            Port = config.Port,
            Database = config.Omika.Database,
            User = config.Omika.User,
            Credential = config.Omika.Credential,
            FilePasswords = new HfSqlFilePasswordOptions
            {
                ProtectionStd = config.FilePasswords.ProtectionStd,
                ProtectionPlus = config.FilePasswords.ProtectionPlus
            }
        };

        var factory = new HfSqlOleDbConnectionFactory();
        using var connection = (OleDbConnection)factory.Create(options);
        try
        {
            connection.Open();
            sb.AppendLine("CONNECTION: OPEN");

            // 1. Racine documentaire.
            string root = Scalar(connection, "SELECT PAP_Valeur FROM ParametreApplication WHERE PAP_Code = 'CHEMIN_REPERTOIRE_BASE_DOCUMENTS'");
            sb.AppendLine($"DOCUMENT ROOT = {root}");

            // 2. Un échantillon de documents (photos par extension).
            var photos = Query(connection,
                "SELECT DC_PKID, DC_Nom, DC_Chemin, DC_CleCategorie FROM Document " +
                "WHERE DC_Nom LIKE '%.jpg' OR DC_Nom LIKE '%.jpeg' OR DC_Nom LIKE '%.png' OR DC_Nom LIKE '%.bmp'");
            sb.AppendLine($"PHOTO CANDIDATES = {photos.Count}");
            foreach (var p in photos.Take(6))
            {
                string pkid = p.GetValueOrDefault("DC_PKID", "");
                string nom = p.GetValueOrDefault("DC_Nom", "");
                string chemin = p.GetValueOrDefault("DC_Chemin", "");
                string candidat = Combine(root, chemin, nom);
                sb.AppendLine($"  DC_PKID={pkid} DC_Nom={nom} DC_Chemin={chemin} candidate={candidat} exists={File.Exists(candidat)}");
            }
        }
        catch (Exception ex)
        {
            sb.AppendLine($"ERROR: {ex.GetType().Name} {ex.Message}");
        }
        finally
        {
            await File.WriteAllTextAsync(OutputPath, sb.ToString());
        }
    }

    private static string Scalar(OleDbConnection c, string sql)
    {
        using var cmd = new OleDbCommand(sql, c);
        return cmd.ExecuteScalar()?.ToString() ?? "<null>";
    }

    private static List<Dictionary<string, string>> Query(OleDbConnection c, string sql)
    {
        var result = new List<Dictionary<string, string>>();
        using var cmd = new OleDbCommand(sql, c);
        using var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            var row = new Dictionary<string, string>();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                row[reader.GetName(i)] = reader.IsDBNull(i) ? "" : reader.GetValue(i)?.ToString() ?? "";
            }
            result.Add(row);
        }
        return result;
    }

    private static string Combine(string root, string chemin, string nom)
    {
        if (string.IsNullOrWhiteSpace(root)) return "<no-root>";
        string combined = string.IsNullOrWhiteSpace(chemin)
            ? nom
            : System.IO.Path.Combine(chemin.Trim('\\', '/'), nom);
        try
        {
            return System.IO.Path.GetFullPath(System.IO.Path.Combine(root, combined));
        }
        catch
        {
            return "<invalid>";
        }
    }

    private sealed class TestConfig
    {
        public int Port { get; set; }
        public string Server { get; set; } = "";
        public FilePasswordsConfig FilePasswords { get; set; } = new();
        public DatabaseConfig Omika { get; set; } = new();
    }
    private sealed class FilePasswordsConfig
    {
        public string? ProtectionStd { get; set; }
        public string? ProtectionPlus { get; set; }
    }
    private sealed class DatabaseConfig
    {
        public string Credential { get; set; } = "";
        public string Database { get; set; } = "";
        public string User { get; set; } = "";
    }

    private static TestConfig? LoadConfig()
    {
        if (!File.Exists(ConfigPath)) return null;
        try
        {
            return JsonSerializer.Deserialize<TestConfig>(
                File.ReadAllText(ConfigPath),
                new JsonSerializerOptions { PropertyNameCaseInsensitive = true });
        }
        catch { return null; }
    }
}