using CareFix.Api.Infrastructure;
using CareFix.Api.Security;
using CareFix.Core;
using Dapper;
using Microsoft.Data.SqlClient;

namespace CareFix.Api.Knowledge;

/// <summary>Imports the data dictionary, relations, rules and playbooks from CSV (templates in /kb-templates).</summary>
public sealed class KbImportService(ControlDb db, KnowledgeBase kb, SqlGuard guard, AuditService audit)
{
    private static readonly Dictionary<string, string[]> Required = new(StringComparer.OrdinalIgnoreCase)
    {
        ["tables"] = ["TableName", "Module", "Meaning"],
        ["columns"] = ["TableName", "ColumnName", "Meaning"],
        ["relations"] = ["ParentTable", "ChildTable", "JoinKeys"],
        ["rules"] = ["Module", "RuleText"],
        ["playbooks"] = ["Title", "Keywords", "Description"],
    };

    public async Task<object> ImportAsync(int userId, string kind, string csvText, bool replace, CancellationToken ct)
    {
        kind = (kind ?? "").ToLowerInvariant();
        if (!Required.TryGetValue(kind, out var required)) throw AppException.BadRequest("Kind must be tables, columns, relations, rules or playbooks.");
        var rows = Csv.Read(csvText);
        if (rows.Count == 0) throw AppException.BadRequest("The CSV has no data rows.");
        var missing = required.Where(r => !rows[0].ContainsKey(r)).ToList();
        if (missing.Count > 0) throw AppException.BadRequest("Missing column(s) in CSV header: " + string.Join(", ", missing));

        var errors = new List<string>();
        var imported = 0;
        await using var c = await db.OpenAsync(ct);
        await using var tx = (SqlTransaction)await c.BeginTransactionAsync(ct);

        if (replace && kind is "relations" or "rules")
            await c.ExecuteAsync(kind == "relations" ? "DELETE CF_KB_RELATION" : "DELETE CF_KB_RULE", transaction: tx);

        var line = 1;
        foreach (var r in rows)
        {
            line++;
            string G(string k) => r.TryGetValue(k, out var v) ? v : "";
            try
            {
                switch (kind)
                {
                    case "tables":
                        Need(G("TableName"), "TableName");
                        await c.ExecuteAsync("""
                            MERGE CF_KB_TABLE AS t USING (SELECT @TableName AS TableName) s ON t.TableName = s.TableName
                            WHEN MATCHED THEN UPDATE SET Module=@Module, Meaning=@Meaning, UpdatedAt=SYSUTCDATETIME()
                            WHEN NOT MATCHED THEN INSERT (TableName, Module, Meaning) VALUES (@TableName, @Module, @Meaning);
                            """, new { TableName = G("TableName"), Module = Csv.NullIfEmpty(G("Module")), Meaning = Csv.NullIfEmpty(G("Meaning")) }, tx);
                        break;
                    case "columns":
                        Need(G("TableName"), "TableName"); Need(G("ColumnName"), "ColumnName");
                        var risk = Csv.NullIfEmpty(G("RiskLevel")) ?? "Medium";
                        if (risk is not ("Low" or "Medium" or "High")) throw new FormatException("RiskLevel must be Low, Medium or High");
                        await c.ExecuteAsync("""
                            MERGE CF_KB_COLUMN AS t USING (SELECT @TableName AS TableName, @ColumnName AS ColumnName) s
                              ON t.TableName = s.TableName AND t.ColumnName = s.ColumnName
                            WHEN MATCHED THEN UPDATE SET Meaning=@Meaning, ValueCodes=@ValueCodes, IsPii=@IsPii, Editable=@Editable, RiskLevel=@RiskLevel, UpdatedAt=SYSUTCDATETIME()
                            WHEN NOT MATCHED THEN INSERT (TableName, ColumnName, Meaning, ValueCodes, IsPii, Editable, RiskLevel)
                                 VALUES (@TableName, @ColumnName, @Meaning, @ValueCodes, @IsPii, @Editable, @RiskLevel);
                            """, new
                        {
                            TableName = G("TableName"), ColumnName = G("ColumnName"), Meaning = Csv.NullIfEmpty(G("Meaning")),
                            ValueCodes = Csv.NullIfEmpty(G("ValueCodes")), IsPii = Csv.Bool(G("IsPii")),
                            Editable = Csv.Bool(G("Editable"), true), RiskLevel = risk,
                        }, tx);
                        break;
                    case "relations":
                        Need(G("ParentTable"), "ParentTable"); Need(G("ChildTable"), "ChildTable"); Need(G("JoinKeys"), "JoinKeys");
                        await c.ExecuteAsync("INSERT CF_KB_RELATION (ParentTable, ChildTable, JoinKeys, CascadeNote) VALUES (@a, @b, @k, @n)",
                            new { a = G("ParentTable"), b = G("ChildTable"), k = G("JoinKeys"), n = Csv.NullIfEmpty(G("CascadeNote")) }, tx);
                        break;
                    case "rules":
                        Need(G("RuleText"), "RuleText");
                        var lockSql = Csv.NullIfEmpty(G("LockCheckSql"));
                        if (lockSql is not null)
                        {
                            var check = guard.ValidateSelect(lockSql, null);
                            if (!check.Ok) throw new FormatException("LockCheckSql: " + check.Reason);
                            if (!lockSql.Contains("@PkValue", StringComparison.OrdinalIgnoreCase)) throw new FormatException("LockCheckSql must use @PkValue");
                            if (Csv.NullIfEmpty(G("LockTable")) is null) throw new FormatException("LockTable is required when LockCheckSql is given");
                        }
                        await c.ExecuteAsync("INSERT CF_KB_RULE (Module, RuleText, LockTable, LockCheckSql, LockMessage) VALUES (@m, @t, @lt, @ls, @lm)",
                            new { m = Csv.NullIfEmpty(G("Module")), t = G("RuleText"), lt = Csv.NullIfEmpty(G("LockTable")), ls = lockSql, lm = Csv.NullIfEmpty(G("LockMessage")) }, tx);
                        break;
                    case "playbooks":
                        Need(G("Title"), "Title");
                        var diag = Csv.NullIfEmpty(G("DiagnosisSql"));
                        if (diag is not null && !guard.ValidateSelect(diag, null).Ok) throw new FormatException("DiagnosisSql must be a single SELECT");
                        await c.ExecuteAsync("""
                            INSERT CF_PLAYBOOK (Title, IssueType, Module, Keywords, Description, DiagnosisSql, FixGuidance, Risk, CreatedBy)
                            VALUES (@Title, @IssueType, @Module, @Keywords, @Description, @DiagnosisSql, @FixGuidance, @Risk, @userId)
                            """, new
                        {
                            Title = G("Title"), IssueType = Csv.NullIfEmpty(G("IssueType")), Module = Csv.NullIfEmpty(G("Module")),
                            Keywords = Csv.NullIfEmpty(G("Keywords")), Description = Csv.NullIfEmpty(G("Description")),
                            DiagnosisSql = diag, FixGuidance = Csv.NullIfEmpty(G("FixGuidance")),
                            Risk = Csv.NullIfEmpty(G("Risk")) ?? "Medium", userId,
                        }, tx);
                        break;
                }
                imported++;
            }
            catch (FormatException ex) { errors.Add($"Row {line}: {ex.Message}"); }
        }

        if (errors.Count > 0 && imported == 0) { await tx.RollbackAsync(ct); throw AppException.BadRequest(string.Join("\n", errors.Take(20))); }
        await tx.CommitAsync(ct);
        kb.Invalidate();
        await audit.LogAsync(userId, null, "KnowledgeImported", new { kind, imported, skipped = errors.Count, replace }, ct);
        return new { imported, skipped = errors.Count, errors = errors.Take(50) };
    }

    public async Task<object> StatsAsync(CancellationToken ct)
    {
        await using var c = await db.OpenAsync(ct);
        return await c.QuerySingleAsync("""
            SELECT (SELECT COUNT(*) FROM CF_KB_TABLE) AS tables, (SELECT COUNT(*) FROM CF_KB_COLUMN) AS columns,
                   (SELECT COUNT(*) FROM CF_KB_RELATION) AS relations, (SELECT COUNT(*) FROM CF_KB_RULE) AS rules,
                   (SELECT COUNT(*) FROM CF_PLAYBOOK WHERE IsActive = 1) AS playbooks
            """);
    }

    public async Task<IEnumerable<dynamic>> PlaybooksAsync(CancellationToken ct)
    {
        await using var c = await db.OpenAsync(ct);
        return await c.QueryAsync("SELECT PlaybookId, Title, IssueType, Module, Keywords, Risk, IsActive, SuccessCount, CreatedAt FROM CF_PLAYBOOK ORDER BY SuccessCount DESC, Title");
    }

    public async Task SetPlaybookActiveAsync(int userId, int id, bool active, CancellationToken ct)
    {
        await using var c = await db.OpenAsync(ct);
        if (await c.ExecuteAsync("UPDATE CF_PLAYBOOK SET IsActive = @active WHERE PlaybookId = @id", new { id, active }) == 0)
            throw AppException.NotFound("Playbook not found.");
        await audit.LogAsync(userId, null, active ? "PlaybookActivated" : "PlaybookRetired", new { id }, ct);
    }

    private static void Need(string v, string name)
    {
        if (string.IsNullOrWhiteSpace(v)) throw new FormatException($"{name} is empty");
    }
}