using System.Text.RegularExpressions;
using Microsoft.Data.Sqlite;

namespace Rmsys.Infrastructure;

/// <summary>
/// 数据包校验：生成的 .db 能不能被 PAD 正常用，先在这里把关。
/// 检查项：文件完整性、配置齐全、行数自洽、中文未乱码、上会顺序连续。
/// </summary>
public static class PackageVerifier
{
    public static VerifyReport Verify(string path)
    {
        var rep = new VerifyReport { Path = path };
        if (!File.Exists(path))
        {
            rep.Fatal = "文件不存在";
            return rep;
        }
        rep.Bytes = new FileInfo(path).Length;

        try
        {
            var csb = new SqliteConnectionStringBuilder { DataSource = path, Mode = SqliteOpenMode.ReadOnly };
            using var conn = new SqliteConnection(csb.ConnectionString);
            conn.Open();

            // 1) 表齐全
            var tables = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
            using (var c = conn.CreateCommand())
            {
                c.CommandText = "select name from sqlite_master where type='table'";
                using var rd = c.ExecuteReader();
                while (rd.Read()) tables.Add(rd.GetString(0));
            }
            // pad_bound 是实际表名
            foreach (var t in new[] { "pkg_config", "depts", "pad_bound", "cadres", "resumes",
                                      "families", "notes", "materials", "appointdocs", "dicts" })
            {
                if (!tables.Contains(t)) rep.MissingTables.Add(t);
            }

            // 2) 配置
            var cfg = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
            using (var c = conn.CreateCommand())
            {
                c.CommandText = "select k, v from pkg_config";
                using var rd = c.ExecuteReader();
                while (rd.Read()) cfg[rd.GetString(0)] = rd.IsDBNull(1) ? "" : rd.GetString(1);
            }
            foreach (var k in new[] { "pkgNo", "meetingName", "secret", "expireAt", "clearPolicy",
                                      "watermark", "fontLevel", "matOrder", "padBound", "cadreCount" })
            {
                if (!cfg.ContainsKey(k) || string.IsNullOrWhiteSpace(cfg[k]))
                    rep.MissingConfig.Add(k);
            }
            rep.PkgNo = cfg.GetValueOrDefault("pkgNo", "");
            rep.MeetingName = cfg.GetValueOrDefault("meetingName", "");
            rep.ExpireAt = cfg.GetValueOrDefault("expireAt", "");
            rep.Watermark = cfg.GetValueOrDefault("watermark", "");
            rep.MatOrder = cfg.GetValueOrDefault("matOrder", "");

            // 3) 行数
            rep.Cadres = Count(conn, "cadres");
            rep.Resumes = Count(conn, "resumes");
            rep.Families = Count(conn, "families");
            rep.Notes = Count(conn, "notes");
            rep.Materials = Count(conn, "materials");
            if (tables.Contains("files"))
            {
                try
                {
                    using var cf = conn.CreateCommand();
                    cf.CommandText = "select count(*), ifnull(sum(size),0) from files";
                    using var rf = cf.ExecuteReader();
                    if (rf.Read()) { rep.PdfFiles = rf.GetInt32(0); rep.PdfBytes = rf.GetInt64(1); }
                }
                catch { }
                try
                {
                    using var df = conn.CreateCommand();
                    df.CommandText = "select count(*), ifnull(sum(size),0) from docfiles";
                    using var rd = df.ExecuteReader();
                    if (rd.Read()) { rep.DocFiles = rd.GetInt32(0); rep.DocBytes = rd.GetInt64(1); }
                }
                catch { }
                rep.DocLines = Count(conn, "doclines");
            }
            rep.Docs = Count(conn, "appointdocs");
            rep.PadBound = Count(conn, "pad_bound");

            if (int.TryParse(cfg.GetValueOrDefault("cadreCount"), out var expect) && expect != rep.Cadres)
                rep.Errors.Add($"配置 cadreCount={expect} 与实际 {rep.Cadres} 不一致");

            // 4) 上会顺序必须从 1 连续
            var sorts = new List<int>();
            using (var c = conn.CreateCommand())
            {
                c.CommandText = "select sortNo from cadres order by sortNo";
                using var rd = c.ExecuteReader();
                while (rd.Read()) sorts.Add(rd.GetInt32(0));
            }
            for (int i = 0; i < sorts.Count; i++)
                if (sorts[i] != i + 1) { rep.Errors.Add($"上会顺序不连续：第 {i + 1} 位是 {sorts[i]}"); break; }

            // 5) 中文未乱码（姓名必须 2~4 个汉字）
            var badNames = new List<string>();
            using (var c = conn.CreateCommand())
            {
                c.CommandText = "select name from cadres";
                using var rd = c.ExecuteReader();
                while (rd.Read())
                {
                    var n = rd.IsDBNull(0) ? "" : rd.GetString(0);
                    if (!Regex.IsMatch(n, @"^[\u4e00-\u9fa5·]{2,8}$")) badNames.Add(n);
                }
            }
            if (badNames.Count > 0) rep.Errors.Add("姓名异常（疑似乱码）：" + string.Join(",", badNames));

            // 6) 每人至少一条简历（任免表「简历」行不能空）
            using (var c = conn.CreateCommand())
            {
                c.CommandText = @"select count(*) from cadres cd
                                  where not exists(select 1 from resumes r where r.cadreId = cd.id)";
                var n = Convert.ToInt32(c.ExecuteScalar());
                if (n > 0) rep.Errors.Add($"{n} 名干部没有简历记录");
            }

            // 7) 期望的材料类型是否都在清单里
            if (!string.IsNullOrEmpty(rep.MatOrder))
            {
                foreach (var t in new[] { "t30", "exam" })
                    if (!rep.MatOrder.Contains(t)) rep.Errors.Add("下发清单缺少必需材料类型：" + t);
            }

            rep.Ok = rep.MissingTables.Count == 0 && rep.MissingConfig.Count == 0 && rep.Errors.Count == 0;
        }
        catch (Exception e)
        {
            rep.Fatal = e.GetType().Name + ": " + e.Message;
            rep.Ok = false;
        }
        return rep;
    }

    private static int Count(SqliteConnection conn, string table)
    {
        try
        {
            using var c = conn.CreateCommand();
            c.CommandText = "select count(*) from " + table;
            return Convert.ToInt32(c.ExecuteScalar());
        }
        catch { return -1; }
    }
}

public class VerifyReport
{
    public string Path { get; set; } = "";
    public long Bytes { get; set; }
    public bool Ok { get; set; }
    public string? Fatal { get; set; }
    public string PkgNo { get; set; } = "";
    public string MeetingName { get; set; } = "";
    public string ExpireAt { get; set; } = "";
    public string Watermark { get; set; } = "";
    public string MatOrder { get; set; } = "";
    public int Cadres { get; set; }
    /// <summary>包内 PDF 正文份数与总字节</summary>
    public int PdfFiles { get; set; }
    /// <summary>包内任免文件原件份数（上传的 Word 转成的 PDF）</summary>
    public int DocFiles { get; set; }
    public long DocBytes { get; set; }
    /// <summary>包内任免文件正文段落数（PAD 用文字渲染，人名可点）</summary>
    public int DocLines { get; set; }
    public long PdfBytes { get; set; }
    public int Resumes { get; set; }
    public int Families { get; set; }
    public int Notes { get; set; }
    public int Materials { get; set; }
    public int Docs { get; set; }
    public int PadBound { get; set; }
    public List<string> MissingTables { get; } = new();
    public List<string> MissingConfig { get; } = new();
    public List<string> Errors { get; } = new();

    public string Human()
    {
        var sb = new System.Text.StringBuilder();
        sb.AppendLine(Ok ? "✔ 数据包校验通过" : "✘ 数据包校验未通过");
        sb.AppendLine($"  文件     {Path}  ({Bytes / 1024.0:F1} KB)");
        if (Fatal != null) { sb.AppendLine("  致命     " + Fatal); return sb.ToString(); }
        sb.AppendLine($"  包号     {PkgNo}");
        sb.AppendLine($"  会议     {MeetingName}");
        sb.AppendLine($"  有效期至 {ExpireAt}");
        sb.AppendLine($"  水印     {Watermark}");
        sb.AppendLine($"  下发清单 {MatOrder}");
        sb.AppendLine($"  干部 {Cadres} / 简历 {Resumes} / 家庭 {Families} / 批注 {Notes} / 材料 {Materials} / 任免文件 {Docs} / 绑定设备 {PadBound}");
        if (MissingTables.Count > 0) sb.AppendLine("  缺表     " + string.Join(",", MissingTables));
        if (MissingConfig.Count > 0) sb.AppendLine("  缺配置   " + string.Join(",", MissingConfig));
        foreach (var e in Errors) sb.AppendLine("  问题     " + e);
        return sb.ToString();
    }
}