using System.Text;
using System.Text.RegularExpressions;
using CareFix.Api.Infrastructure;
using Dapper;
using Microsoft.Extensions.Caching.Memory;

namespace CareFix.Api.Knowledge;

public sealed class ColumnInfo
{
    public string ColumnName { get; set; } = "";
    public string DataType { get; set; } = "";
    public bool IsNullable { get; set; }
    public bool IsPk { get; set; }
    public string? Meaning { get; set; }
    public string? ValueCodes { get; set; }
    public bool? IsPii { get; set; }
    public bool? Editable { get; set; }
    public string? RiskLevel { get; set; }
}

public sealed class LockRule
{
    public int RuleId { get; set; }
    public string RuleText { get; set; } = "";
    public string LockCheckSql { get; set; } = "";
    public string? LockMessage { get; set; }
}

public sealed class PlaybookRow
{
    public int PlaybookId { get; set; }
    public string Title { get; set; } = "";
    public string? IssueType { get; set; }
    public string? Module { get; set; }
    public string? Keywords { get; set; }
    public string? Description { get; set; }
    public string? DiagnosisSql { get; set; }
    public string? FixGuidance { get; set; }
    public string Risk { get; set; } = "Medium";
    public int SuccessCount { get; set; }
}

/// <summary>Caresoft's data dictionary, rules, playbooks and per-hospital schema snapshot.</summary>
public sealed class KnowledgeBase(ControlDb db, IMemoryCache cache)
{
    private sealed class HitRow
    {
        public string TableName { get; set; } = "";
        public string ColumnName { get; set; } = "";
        public string DataType { get; set; } = "";
        public string? ColumnMeaning { get; set; }
        public string? TableMeaning { get; set; }
        public string? Module { get; set; }
        public int Score { get; set; }
    }

    public void Invalidate(int? hospitalId = null)
    {
        if (hospitalId is not null) cache.Remove($"tables:{hospitalId}");
        cache.Remove("pii");
    }

    public async Task<HashSet<string>> KnownTablesAsync(int hospitalId, CancellationToken ct)
    {
        return (await cache.GetOrCreateAsync($"tables:{hospitalId}", async e =>
        {
            e.AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(10);
            await using var c = await db.OpenAsync(ct);
            var t = await c.QueryAsync<string>("SELECT DISTINCT TableName FROM CF_HOSPITAL_SCHEMA WHERE HospitalId = @hospitalId", new { hospitalId });
            return new HashSet<string>(t, StringComparer.OrdinalIgnoreCase);
        }))!;
    }

    public async Task<HashSet<string>> PiiColumnsAsync(CancellationToken ct)
    {
        return (await cache.GetOrCreateAsync("pii", async e =>
        {
            e.AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(10);
            await using var c = await db.OpenAsync(ct);
            var cols = await c.QueryAsync<string>("SELECT DISTINCT ColumnName FROM CF_KB_COLUMN WHERE IsPii = 1");
            return new HashSet<string>(cols, StringComparer.OrdinalIgnoreCase);
        }))!;
    }

    public async Task<ColumnInfo?> ColumnInfoAsync(int hospitalId, string table, string column, CancellationToken ct)
    {
        await using var c = await db.OpenAsync(ct);
        return await c.QuerySingleOrDefaultAsync<ColumnInfo>("""
            SELECT TOP 1 s.ColumnName, s.DataType, s.IsNullable, s.IsPk, k.Meaning, k.ValueCodes, k.IsPii, k.Editable, k.RiskLevel
            FROM CF_HOSPITAL_SCHEMA s
            LEFT JOIN CF_KB_COLUMN k ON k.TableName = s.TableName AND k.ColumnName = s.ColumnName
            WHERE s.HospitalId = @hospitalId AND s.SchemaName = 'dbo' AND s.TableName = @table AND s.ColumnName = @column
            """, new { hospitalId, table, column });
    }

    public async Task<object> SearchSchemaAsync(int hospitalId, string query, CancellationToken ct)
    {
        var terms = Regex.Split(query ?? "", "[^A-Za-z0-9_]+")
            .Where(t => t.Length >= 3).Distinct(StringComparer.OrdinalIgnoreCase).Take(6).ToList();
        if (terms.Count == 0) return new { message = "Give at least one search word of 3 or more letters, e.g. 'pharmacy issue' or 'refund'." };

        await using var c = await db.OpenAsync(ct);
        var hits = new Dictionary<(string, string), HitRow>();
        foreach (var term in terms)
        {
            var rows = await c.QueryAsync<HitRow>("""
                SELECT TOP 80 s.TableName, s.ColumnName, s.DataType, k.Meaning AS ColumnMeaning, t.Meaning AS TableMeaning, t.Module,
                       CASE WHEN s.TableName LIKE @p ESCAPE '\' OR t.Meaning LIKE @p ESCAPE '\' THEN 2 ELSE 1 END AS Score
                FROM CF_HOSPITAL_SCHEMA s
                LEFT JOIN CF_KB_COLUMN k ON k.TableName = s.TableName AND k.ColumnName = s.ColumnName
                LEFT JOIN CF_KB_TABLE t ON t.TableName = s.TableName
                WHERE s.HospitalId = @hospitalId
                  AND (s.TableName LIKE @p ESCAPE '\' OR s.ColumnName LIKE @p ESCAPE '\' OR k.Meaning LIKE @p ESCAPE '\' OR t.Meaning LIKE @p ESCAPE '\')
                ORDER BY Score DESC
                """, new { hospitalId, p = "%" + EscapeLike(term) + "%" });
            foreach (var r in rows)
            {
                var key = (r.TableName.ToUpperInvariant(), r.ColumnName.ToUpperInvariant());
                if (hits.TryGetValue(key, out var h)) h.Score += r.Score; else hits[key] = r;
            }
        }
        if (hits.Count == 0) return new { message = "No tables or columns matched. Try other words (English names used in HIS, e.g. BILL, RECEIPT, IPD, PHARMACY, ISSUE, REFUND)." };

        return hits.Values
            .GroupBy(h => h.TableName, StringComparer.OrdinalIgnoreCase)
            .Select(g => new
            {
                table = g.Key,
                module = g.First().Module,
                tableMeaning = g.First().TableMeaning,
                score = g.Sum(x => x.Score),
                columns = g.OrderByDescending(x => x.Score).Take(12)
                           .Select(x => new { column = x.ColumnName, type = x.DataType, meaning = x.ColumnMeaning }),
            })
            .OrderByDescending(x => x.score).Take(15).ToList();
    }

    public async Task<object> DescribeTableAsync(int hospitalId, string table, CancellationToken ct)
    {
        await using var c = await db.OpenAsync(ct);
        var cols = (await c.QueryAsync<ColumnInfo>("""
            SELECT s.ColumnName, s.DataType, s.IsNullable, s.IsPk, k.Meaning, k.ValueCodes, k.IsPii, k.Editable, k.RiskLevel
            FROM CF_HOSPITAL_SCHEMA s
            LEFT JOIN CF_KB_COLUMN k ON k.TableName = s.TableName AND k.ColumnName = s.ColumnName
            WHERE s.HospitalId = @hospitalId AND s.SchemaName = 'dbo' AND s.TableName = @table
            ORDER BY s.IsPk DESC, s.ColumnName
            """, new { hospitalId, table })).ToList();
        if (cols.Count == 0) throw AppException.BadRequest($"Table {table} is not in this hospital's schema. Use search_schema.");

        var info = await c.QuerySingleOrDefaultAsync<(string? Module, string? Meaning)>(
            "SELECT Module, Meaning FROM CF_KB_TABLE WHERE TableName = @table", new { table });
        var relations = await c.QueryAsync("""
            SELECT ParentTable AS parent, ChildTable AS child, JoinKeys AS joinKeys, CascadeNote AS note
            FROM CF_KB_RELATION WHERE ParentTable = @table OR ChildTable = @table
            """, new { table });
        var rules = await c.QueryAsync<string>("""
            SELECT RuleText + ISNULL(' (lock: ' + LockMessage + ')', '') FROM CF_KB_RULE
            WHERE LockTable = @table OR (LockTable IS NULL AND Module IS NOT NULL AND Module = @module)
            """, new { table, module = info.Module });

        return new
        {
            table,
            module = info.Module,
            meaning = info.Meaning,
            columns = cols.Select(x => new
            {
                column = x.ColumnName, type = x.DataType, nullable = x.IsNullable, primaryKey = x.IsPk,
                meaning = x.Meaning, valueCodes = x.ValueCodes,
                editable = x.Editable ?? true, risk = x.RiskLevel, patientIdentifying = x.IsPii ?? false,
            }),
            linkedTables = relations,
            rules,
        };
    }

    public async Task<List<LockRule>> LockRulesAsync(string table, CancellationToken ct)
    {
        await using var c = await db.OpenAsync(ct);
        return (await c.QueryAsync<LockRule>(
            "SELECT RuleId, RuleText, LockCheckSql, LockMessage FROM CF_KB_RULE WHERE LockTable = @table AND LockCheckSql IS NOT NULL",
            new { table })).ToList();
    }

    public async Task<List<PlaybookRow>> MatchPlaybooksAsync(string issueText, int take, CancellationToken ct)
    {
        await using var c = await db.OpenAsync(ct);
        var all = await c.QueryAsync<PlaybookRow>("SELECT * FROM CF_PLAYBOOK WHERE IsActive = 1");
        var text = issueText.ToLowerInvariant();
        return all
            .Select(p => (p, score: (p.Keywords ?? "").Split(',', StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries)
                                     .Count(k => text.Contains(k.ToLowerInvariant()))))
            .Where(x => x.score > 0)
            .OrderByDescending(x => x.score).ThenByDescending(x => x.p.SuccessCount)
            .Take(take).Select(x => x.p).ToList();
    }

    public async Task<PlaybookRow?> GetPlaybookAsync(int id, CancellationToken ct)
    {
        await using var c = await db.OpenAsync(ct);
        return await c.QuerySingleOrDefaultAsync<PlaybookRow>("SELECT * FROM CF_PLAYBOOK WHERE PlaybookId = @id AND IsActive = 1", new { id });
    }

    /// <summary>Per-ticket context for the AI: hospital, matching playbooks, general rules.</summary>
    public async Task<string> ContextForTicketAsync(int hospitalId, string issueText, CancellationToken ct)
    {
        await using var c = await db.OpenAsync(ct);
        var h = await c.QuerySingleAsync<(string Name, string? City, string? HisVersion, string? Notes, DateTime? SchemaCapturedAt)>(
            "SELECT Name, City, HisVersion, Notes, SchemaCapturedAt FROM CF_HOSPITAL WHERE HospitalId = @hospitalId", new { hospitalId });
        var rules = await c.QueryAsync<(string? Module, string RuleText)>("SELECT TOP 40 Module, RuleText FROM CF_KB_RULE ORDER BY Module, RuleId");
        var playbooks = await MatchPlaybooksAsync(issueText, 3, ct);

        var sb = new StringBuilder();
        sb.AppendLine($"Hospital: {h.Name}{(h.City is null ? "" : ", " + h.City)}. HIS version: {h.HisVersion ?? "not recorded"}.");
        if (!string.IsNullOrWhiteSpace(h.Notes)) sb.AppendLine($"Hospital notes and customisations: {h.Notes}");
        if (h.SchemaCapturedAt is null) sb.AppendLine("WARNING: the schema snapshot for this hospital has not been captured; run_select will fail until an admin captures it.");
        if (playbooks.Count > 0)
        {
            sb.AppendLine("\nKnown fix patterns that may match this issue (call get_playbook for the full pattern):");
            foreach (var p in playbooks) sb.AppendLine($"- Playbook {p.PlaybookId}: {p.Title} [{p.Module}] — {p.Description}");
        }
        var ruleList = rules.ToList();
        if (ruleList.Count > 0)
        {
            sb.AppendLine("\nCaresoft business rules:");
            foreach (var r in ruleList) sb.AppendLine($"- [{r.Module ?? "General"}] {r.RuleText}");
        }
        return sb.ToString();
    }

    private static string EscapeLike(string s) => s.Replace("\\", "\\\\").Replace("%", "\\%").Replace("_", "\\_").Replace("[", "\\[");
}