using System.Text;
using System.Text.Json;
using Microsoft.Extensions.Logging.Abstractions;
using Omika.Core.Contracts.Documents;
using Omika.Core.Interfaces;
using Omika.Core.Services;
using Omika.Data.Configuration;
using Omika.Data.HfSql.Connection;
using Omika.Data.Repositories.Omika;

namespace Omika.Tests.Connection;

/// <summary>
/// SONDE DIAGNOSTIC (lecture seule) du MODÈLE EMAIL OMIKA : charge un vrai
/// <c>MailModele</c> d'OMIKA_TEST, sépare sujet/corps, liste les rubriques
/// <c>GetChampsMailAsync</c>, puis reproduit la substitution
/// <c>EmailCreationService</c> pour exposer la première divergence fonctionnelle.
/// N'écrit JAMAIS en base. Résultats dans artifacts/diag-email.txt.
/// </summary>
public class RapportEmailDiagnosticTests
{
    private const string ConfigPath = @"D:\Avalonia\Omika\config\hfsql-test.local.json";
    private const string ArtifactDir = @"D:\Avalonia\Omika\artifacts";

    [Fact]
    public async Task Diagnostic_Trace_Email_Omika()
    {
        var sb = new StringBuilder();
        sb.AppendLine("=== OMIKA EMAIL — DIAGNOSTIC (read-only) ===");

        TestConfig? config = LoadConfig();
        if (config is null)
        {
            sb.AppendLine("CONFIG: MISSING (skip)");
            await WriteArtifact("diag-email.txt", sb);
            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();
        await using var connection = new HfSqlOleDbConnection(factory, options);
        try
        {
            await connection.OpenAsync();
            sb.AppendLine($"CONNECTION: OPEN (DB={config.Omika.Database})");
        }
        catch (Exception ex)
        {
            sb.AppendLine($"CONNECTION FAILED (skip): {ex.Message}");
            await WriteArtifact("diag-email.txt", sb);
            return;
        }

        try
        {
            // 1. Liste des modèles email.
            IReadOnlyList<IReadOnlyDictionary<string, object?>> modeles = await connection.QueryAsync(
                "SELECT MM_PKID, MM_Nom, MM_Sujet, MM_MsgDoc, MM_Extra, MM_EnvoiMob FROM MailModele ORDER BY MM_PKID");
            sb.AppendLine($"MAIL MODELES = {modeles.Count}");

            long modeleChoisi = 0;
            byte[]? corpsChoisi = null;
            string? sujetChoisi = null;
            string? extraChoisi = null;
            foreach (var m in modeles)
            {
                byte[]? docx = m.GetValueOrDefault("MM_MsgDoc") as byte[];
                string? sujet = m.GetValueOrDefault("MM_Sujet")?.ToString();
                string? extra = m.GetValueOrDefault("MM_Extra")?.ToString();
                string texte = docx is null ? string.Empty : DocxTextExtractor.Extraire(docx);
                string combiné = (sujet ?? string.Empty) + "\n" + texte;
                var jetons = RubriqueSubstitutionEngine.ExtractTokens(combiné);
                bool interet = jetons.Count > 0;
                sb.AppendLine($"  MM_PKID={m.GetValueOrDefault("MM_PKID")} nom={Truncate(m.GetValueOrDefault("MM_Nom")?.ToString())} " +
                              $"sujet={Truncate(sujet, 40)} msgDoc={docx?.Length ?? 0}o extra={(extra is null ? "null" : extra.Length + "o")} " +
                              $"envoiMob={m.GetValueOrDefault("MM_EnvoiMob")} jetons={jetons.Count}");
                if (interet && modeleChoisi == 0)
                {
                    modeleChoisi = Convert.ToInt64(m.GetValueOrDefault("MM_PKID"));
                    corpsChoisi = docx;
                    sujetChoisi = sujet;
                    extraChoisi = extra;
                }

                // Préfère le modèle qui a un MM_Extra non vide (rubriques EXTRA réelles).
                if (!string.IsNullOrWhiteSpace(extra))
                {
                    modeleChoisi = Convert.ToInt64(m.GetValueOrDefault("MM_PKID"));
                    corpsChoisi = docx;
                    sujetChoisi = sujet;
                    extraChoisi = extra;
                }
            }

            if (modeleChoisi == 0)
            {
                sb.AppendLine("NO MAIL MODEL WITH PLACEHOLDERS (skip).");
                await WriteArtifact("diag-email.txt", sb);
                return;
            }

            // 2. Modèle choisi : sujet + corps + extra.
            string corpsTexte = corpsChoisi is null ? string.Empty : DocxTextExtractor.Extraire(corpsChoisi);
            sb.AppendLine();
            sb.AppendLine($"CHOSEN MM_PKID={modeleChoisi}");
            sb.AppendLine($"MM_EXTRA = {extraChoisi}");
            sb.AppendLine("── MM_EXTRA PARSED ──");
            if (!string.IsNullOrWhiteSpace(extraChoisi))
            {
                foreach (RapportChampDto x in RubriqueExtraMobParser.Parser(extraChoisi))
                {
                    sb.AppendLine($"  extra rub={x.Rubrique} nom={Truncate(x.Nom, 25)} type={x.Type} typeMembre={x.TypeMembre} masque={x.MasqueSaisie} groupe={x.GroupeApp}");
                }
            }
            sb.AppendLine($"── SUJET (brut) ──");
            sb.AppendLine(sujetChoisi ?? "<null>");
            sb.AppendLine($"── SUJET PLACEHOLDERS ──");
            foreach (string j in RubriqueSubstitutionEngine.ExtractTokens(sujetChoisi))
            {
                sb.AppendLine($"  ${j}$");
            }
            sb.AppendLine($"── CORPS PLACEHOLDERS ──");
            foreach (string j in RubriqueSubstitutionEngine.ExtractTokens(corpsTexte))
            {
                sb.AppendLine($"  ${j}$");
            }
            sb.AppendLine($"── CORPS (extrait, {corpsTexte.Length} chars) ──");
            sb.AppendLine(corpsTexte.Length <= 1500 ? corpsTexte : corpsTexte[..1500] + "…");

            // Structure DOCX : quels placeholders sont dans un TableRow (w:tbl) vs un paragraphe.
            sb.AppendLine();
            sb.AppendLine("── DOCX STRUCTURE (placeholder → dans table ?) ──");
            if (corpsChoisi is not null)
            {
                await File.WriteAllBytesAsync(Path.Combine(ArtifactDir, "diag-email-modele.docx"), corpsChoisi);
                using var docxStream = new MemoryStream(corpsChoisi);
                using var doc = DocumentFormat.OpenXml.Packaging.WordprocessingDocument.Open(docxStream, false);
                var bodyEl = doc.MainDocumentPart!.Document.Body!;
                foreach (var tr in bodyEl.Descendants<DocumentFormat.OpenXml.Wordprocessing.TableRow>())
                {
                    string txt = string.Concat(tr.Descendants<DocumentFormat.OpenXml.Wordprocessing.Text>().Select(t => t.Text));
                    if (txt.Contains('$') || txt.Contains('<'))
                    {
                        sb.AppendLine($"  TABLE ROW: {Truncate(txt, 120)}");
                    }
                }
                foreach (var p in bodyEl.Descendants<DocumentFormat.OpenXml.Wordprocessing.Paragraph>())
                {
                    string txt = string.Concat(p.Descendants<DocumentFormat.OpenXml.Wordprocessing.Text>().Select(t => t.Text));
                    if (txt.Contains('$') || txt.Contains('<'))
                    {
                        bool dansTable = p.Ancestors<DocumentFormat.OpenXml.Wordprocessing.Table>().Any();
                        sb.AppendLine($"  PARAGRAPH (dansTable={dansTable}): {Truncate(txt, 120)}");
                    }
                }
            }

            // 3. Intervention avec données (photos etc.) — première intervention.
            long interventionId = 0;
            IReadOnlyList<IReadOnlyDictionary<string, object?>> intervs = await connection.QueryAsync(
                "SELECT IN_PKID FROM Intervention");
            foreach (var i in intervs)
            {
                interventionId = Convert.ToInt64(i.GetValueOrDefault("IN_PKID"));
                break;
            }

            var parameters = new FakeApplicationParameters("C:\\Documents_Demo");
            var repository = new OmikaRapportChampRepository(
                connection, NullLogger<OmikaRapportChampRepository>.Instance, parameters);

            // 4. GetChampsMailAsync réel.
            IReadOnlyList<RapportChampDto> champs = await repository.GetChampsMailAsync(modeleChoisi, interventionId);
            sb.AppendLine();
            sb.AppendLine($"── GetChampsMailAsync ({champs.Count}) ──");
            foreach (RapportChampDto c in champs)
            {
                sb.AppendLine($"  rub={c.Rubrique} nom={Truncate(c.Nom, 25)} groupe={c.GroupeApp} " +
                              $"type={c.Type} typeMembre={c.TypeMembre} masque={c.MasqueSaisie} modif={c.Modifiable} " +
                              $"bd={c.DisponibleBase} listeMembre={c.ListeMembre.Count} " +
                              $"bdVal={(c.ListeDonnesBD is null ? "null" : c.ListeDonnesBD.Length + "o")} " +
                              $"mobVal={(c.ListeDonnesMob is null ? "null" : c.ListeDonnesMob.Length + "o")}");
                foreach (RapportChampSousElementDto m in c.ListeMembre)
                {
                    sb.AppendLine($"    membre rub={m.Rubrique} type={m.Type} membreMob={m.MembreMob}");
                }
                if (!string.IsNullOrWhiteSpace(c.ListeDonnesBD) && c.ListeDonnesBD.Length < 300)
                {
                    sb.AppendLine($"    ListeDonnesBD = {c.ListeDonnesBD}");
                }
            }

            // 5. Reproduction via le VRAI moteur Email (EmailDocxRenderer + DocxHtmlConverter).
            string sujetFinal = SubstituerSujet(sujetChoisi, champs);
            byte[] docxRendu = EmailDocxRenderer.Rendre(corpsChoisi ?? [], champs);
            string corpsFinal = DocxHtmlConverter.Convertir(docxRendu);
            string corpsTexteFinal = DocxTextExtractor.Extraire(docxRendu);

            sb.AppendLine();
            sb.AppendLine("── SUBJECT FINAL ──");
            sb.AppendLine(sujetFinal);
            sb.AppendLine("── BODY FINAL (HTML extrait) ──");
            sb.AppendLine(corpsFinal.Length <= 1500 ? corpsFinal : corpsFinal[..1500] + "…");

            // Restants.
            sb.AppendLine();
            sb.AppendLine("── UNRESOLVED $...$ (subject + corps texte rendu) ──");
            var restants = RubriqueSubstitutionEngine.ExtractTokens(sujetFinal + "\n" + corpsTexteFinal);
            foreach (string r in restants)
            {
                sb.AppendLine($"  ${r}$");
            }
            sb.AppendLine($"UNRESOLVED COUNT = {restants.Count}");
            sb.AppendLine($"BODY IS HTML = {(corpsFinal.Contains("<table", StringComparison.OrdinalIgnoreCase) || corpsFinal.Contains("<p", StringComparison.OrdinalIgnoreCase) ? "YES" : "NO")}");
            sb.AppendLine($"RAW JSON IN EMAIL = {(sujetFinal.Contains('{') && sujetFinal.Contains('}') || corpsFinal.Contains("\"cheminComplet\"") ? "YES" : "NO")}");

            await WriteArtifact("diag-email.txt", sb);
        }
        catch (Exception ex)
        {
            sb.AppendLine();
            sb.AppendLine($"ERROR: {ex.GetType().Name}: {ex}");
            await WriteArtifact("diag-email.txt", sb);
        }
    }

    private static string SubstituerSujet(string? sujet, IReadOnlyList<RapportChampDto> champs)
    {
        if (string.IsNullOrEmpty(sujet))
        {
            return sujet ?? string.Empty;
        }

        var remplacements = new Dictionary<string, string>(StringComparer.Ordinal);
        var connus = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        foreach (RapportChampDto champ in champs)
        {
            if (string.IsNullOrWhiteSpace(champ.Rubrique))
            {
                continue;
            }
            connus.Add(champ.Rubrique);
            foreach (RapportChampSousElementDto m in champ.ListeMembre)
            {
                if (!string.IsNullOrWhiteSpace(m.Rubrique)) connus.Add(m.Rubrique);
            }

            if (champ.ListeMembre is { Count: > 0 })
            {
                remplacements[champ.Rubrique] = EmailDocxRenderer.FormaterTableauTexte(champ);
            }
            else
            {
                string? valeur = champ.ListeDonnesMob ?? champ.ListeDonnesBD;
                remplacements[champ.Rubrique] = EmailDocxRenderer.FormaterValeur(valeur, champ.TypeMembre ?? champ.Type, champ.MasqueSaisie);
            }
        }

        var builder = new StringBuilder(sujet);
        foreach (var paire in remplacements)
        {
            if (!string.IsNullOrEmpty(paire.Key)) builder.Replace(paire.Key, paire.Value);
        }
        foreach (string jeton in connus)
        {
            builder.Replace(jeton, string.Empty);
        }
        return builder.ToString();
    }

    private static async Task WriteArtifact(string name, StringBuilder sb)
    {
        Directory.CreateDirectory(ArtifactDir);
        await File.WriteAllTextAsync(Path.Combine(ArtifactDir, name), sb.ToString());
    }

    private static string Truncate(string? s, int max = 60) => s is null ? "<null>" : (s.Length <= max ? s : s[..max] + "…");

    private sealed class FakeApplicationParameters : IApplicationParameterRepository
    {
        private readonly string _root;
        public FakeApplicationParameters(string root) => _root = root;
        public Task<string?> GetValueAsync(string code, CancellationToken ct = default)
            => Task.FromResult<string?>(code == "CHEMIN_REPERTOIRE_BASE_DOCUMENTS" ? _root : null);
    }

    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;
        }
    }
}