using System.Globalization;
using System.Text.Json;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Rmsys.Domain;
using Rmsys.Domain.Entities;

namespace Rmsys.Infrastructure;

/// <summary>
/// 数据包导出：把某场会议的数据打成**单个 SQLite 文件**，供 PAD 端离线读取。
///
/// 为什么要导出 SQLite 而不是给接口：
///   · PAD 是受管设备，会议现场不联网；
///   · 后台配置（水印/字号/有效期/设备绑定/清除策略）随包下发，PAD 启动即校验 —— 这才让后台真正管得住 PAD；
///   · 单文件便于 U 盘导入、便于会后销毁、便于保密检查留痕。
///
/// 表结构刻意做扁平化（cadres 已把上会顺序/议题号/结果内联），PAD 端一句
/// "select * from cadres order by sortNo" 就能画表，安卓侧不用写 join。
/// </summary>
public class PackageExporter
{
    private readonly RmsysDbContext _db;

    public PackageExporter(RmsysDbContext db) => _db = db;

    /// <summary>
    /// 取数据包 SM4 主密钥（方案 A：机构固定密钥，存 sysconfigs）。
    /// 首次调用自动生成 32 字节随机密钥写库。PAD 端内置同一把。
    /// </summary>
    public byte[] MasterKey()
    {
        var connStr = _db.Database.GetConnectionString() ?? DbConfig.DefaultMySql;

        // 首次初始化就用「APK 内置的那把」写库，不能随机生成。
        // 随机生成过一次就会造成库与 APK 密钥不一致，PAD 端打不开任何本机导出的包。
        var b64 = SysConfigStore.GetOrCreate(connStr, "PackageSm4Key",
            () => PackageCrypto.BuiltinMasterKeyBase64,
            "数据包 SM4 主密钥（勿外泄；必须与 PAD 端 APK 内置密钥一致）");

        // 老环境兜底：早期版本在这里随机生成过密钥，库里的值与 APK 内置的不一样，
        // 表现就是 PAD 一律提示「密钥不匹配或数据包已被篡改」。
        // 这里发现不一致就纠正回内置密钥，保证本机新导出的包 PAD 一定能解开。
        if (!string.Equals(b64, PackageCrypto.BuiltinMasterKeyBase64, StringComparison.Ordinal))
        {
            SysConfigStore.Set(connStr, "PackageSm4Key", PackageCrypto.BuiltinMasterKeyBase64,
                "数据包 SM4 主密钥（已自动对齐 PAD 内置密钥 / 原随机值作废）", "system");
            b64 = PackageCrypto.BuiltinMasterKeyBase64;
        }

        if (string.IsNullOrWhiteSpace(b64))
            throw new InvalidOperationException("取不到数据包主密钥：数据库不可用");
        var key = Convert.FromBase64String(b64);
        if (key.Length != PackageCrypto.MasterKeySize)
            throw new InvalidOperationException($"主密钥长度异常：{key.Length} 字节（应为 {PackageCrypto.MasterKeySize}）");
        return key;
    }

    /// <summary>
    /// 照片根目录（后端 App_Data 的绝对路径）。设置后会把干部的证件照读成
    /// data:image/...;base64 写进数据包的 cadres.photo，PAD 端据此直接渲染，
    /// 否则只写 hasPhoto 标记（PAD 照片格留空）。
    /// </summary>
    public string? PhotoRoot { get; set; }

    /// <summary>生成数据包时勾选的材料清单（优先于全局设置；不传则按全局设置）</summary>
    public List<string>? MatOrderOverride { get; set; }

    private string? PhotoDataUri(string? photoPath)
    {
        if (string.IsNullOrWhiteSpace(PhotoRoot)) return null;
        try
        {
            // 用 PhotoStore 解析：兼容早期库里缺 uploads/ 前缀的路径
            var full = PhotoStore.Resolve(PhotoRoot!, photoPath);
            if (full == null) return null;
            var bytes = File.ReadAllBytes(full);
            if (bytes.Length == 0 || bytes.Length > 4 * 1024 * 1024) return null;   // 超过 4MB 不塞包
            var mime = bytes.Length > 8 && bytes[0] == 0x89 && bytes[1] == 0x50 ? "image/png"
                     : bytes.Length > 3 && bytes[0] == 0xFF && bytes[1] == 0xD8 ? "image/jpeg" : "image/png";
            return "data:" + mime + ";base64," + Convert.ToBase64String(bytes);
        }
        catch { return null; }
    }

    /// <summary>
    /// 导出并加密为 .pkg —— **生产唯一路径**。
    /// 流程：先落明文 SQLite 到临时文件 → 校验 → SM4-CBC 加密 + HMAC-SM3 签名 → 写 .pkg
    ///      → 把临时明文清零覆盖后删除（不留明文残片）。
    /// </summary>
    public async Task<ExportResult> ExportEncryptedAsync(int meetingId, string outPath, string? generatedBy = null)
    {
        // 明文临时文件名必须唯一，否则会撞「文件正被另一个进程使用」：
        //   · 同一秒连点两次「生成并下发」→ 两次算出同一个 outPath → 同一个 .plain.tmp
        //   · 上次异常退出、或杀毒软件正在扫描，残留的同名临时文件也会撞
        // 所以带上随机后缀；写入前顺手清掉目录里超龄的残留临时文件。
        var dir = Path.GetDirectoryName(outPath);
        if (!string.IsNullOrWhiteSpace(dir)) CleanStaleTemps(dir);

        var tmp = outPath + "." + Guid.NewGuid().ToString("N").Substring(0, 8) + ".plain.tmp";
        ExportResult r;
        try
        {
            r = await ExportAsync(meetingId, tmp, generatedBy);

            // ① 关键：SQLite 连接池会把文件句柄留着不放（Dispose 只是归还给池）。
            //    Linux 上删/读被打开的文件不受影响，所以本地测不出来；
            //    Windows 上立刻报「The process cannot access the file ... being used by another process」。
            //    这里主动清空池，让句柄真正释放。
            ReleaseSqliteHandles();

            // ② 再兜一层：万一被杀毒软件/索引服务短暂打开，用 FileShare.ReadWrite 打开
            //    （允许对方同时持有），并重试几次，别让整次导出白做。
            byte[] plain = Array.Empty<byte>();
            for (var attempt = 1; ; attempt++)
            {
                try
                {
                    using var fs = new FileStream(tmp, FileMode.Open, FileAccess.Read,
                        FileShare.ReadWrite | FileShare.Delete);
                    plain = new byte[fs.Length];
                    var read = 0;
                    while (read < plain.Length)
                    {
                        var n = await fs.ReadAsync(plain.AsMemory(read, plain.Length - read));
                        if (n <= 0) break;
                        read += n;
                    }
                    if (read < plain.Length) Array.Resize(ref plain, read);
                    break;
                }
                catch (IOException) when (attempt < 4)
                {
                    ReleaseSqliteHandles();
                    await Task.Delay(200 * attempt);
                }
            }

            if (plain.Length == 0)
                throw new InvalidOperationException("导出的明文包为空，无法加密（请检查磁盘空间与杀毒软件拦截）");

            var pkg = PackageCrypto.Encode(plain, MasterKey());

            // ③ 写 .pkg 也用 FileShare.Read 打开：同名的旧包若正被人打开查看，也不至于写不进去
            using (var ofs = new FileStream(outPath, FileMode.Create, FileAccess.Write,
                       FileShare.Read | FileShare.Delete))
            {
                await ofs.WriteAsync(pkg);
                ofs.Flush(true);
            }

            r.Path = outPath;
            r.Bytes = pkg.LongLength;
            r.Encrypted = true;
        }
        finally
        {
            ReleaseSqliteHandles();
            WipeAndDelete(tmp);
        }
        return r;
    }

    /// <summary>
    /// 真正释放 SQLite 文件句柄。
    /// Microsoft.Data.Sqlite 默认启用连接池，conn.Dispose() 只是把连接还进池，
    /// 底层文件句柄仍然开着 —— 在 Windows 上这会导致后续读取/删除该文件报「被另一进程使用」。
    /// </summary>
    private static void ReleaseSqliteHandles()
    {
        try { Microsoft.Data.Sqlite.SqliteConnection.ClearAllPools(); }
        catch { /* 释放失败不阻断流程 */ }
    }

    /// <summary>
    /// 清理目录里超龄的明文临时文件（*.plain.tmp）。
    /// 只为防「异常退出后残留越积越多」；正被占用的文件跳过，不影响本次导出。
    /// </summary>
    private static void CleanStaleTemps(string dir)
    {
        try
        {
            foreach (var f in Directory.GetFiles(dir, "*.plain.tmp"))
            {
                try
                {
                    if (DateTime.Now - File.GetLastWriteTime(f) > TimeSpan.FromHours(2))
                        File.Delete(f);
                }
                catch { /* 正被使用，跳过 */ }
            }
        }
        catch { /* 目录读不了也不影响导出 */ }
    }

    /// <summary>明文临时文件：先用 0 覆盖再删，降低被恢复的可能。
    /// 公开出来给「程序启动清理残留」复用（见 Program.cs）。</summary>
    public static void WipeAndDelete(string path)
    {
        try
        {
            if (!File.Exists(path)) return;
            var len = new FileInfo(path).Length;
            // FileShare 放宽容忍：杀毒软件/索引服务可能正打开着这个文件，
            // 用 None 会直接失败导致明文残留；这里允许对方同时持有，只要我们能写就行。
            using (var fs = new FileStream(path, FileMode.Open, FileAccess.Write,
                       FileShare.ReadWrite | FileShare.Delete))
            {
                var zeros = new byte[Math.Min(len, 64 * 1024)];
                long left = len;
                while (left > 0)
                {
                    var n = (int)Math.Min(left, zeros.Length);
                    fs.Write(zeros, 0, n);
                    left -= n;
                }
                fs.Flush(true);
            }
            File.Delete(path);
        }
        catch { /* 清理失败不阻断导出 */ }
    }

    private static string FirstNonEmpty(params string?[] vals)
        => vals.FirstOrDefault(v => !string.IsNullOrWhiteSpace(v)) ?? "";

    public async Task<ExportResult> ExportAsync(int meetingId, string outPath, string? generatedBy = null)
    {
        var meeting = await _db.Meetings.FirstOrDefaultAsync(x => x.Id == meetingId)
                      ?? throw new InvalidOperationException("会议不存在: " + meetingId);

        var depts = await _db.Depts.OrderBy(x => x.Sort).ToListAsync();
        var deptCode = depts.ToDictionary(x => x.Id, x => x.Code);
        var deptName = depts.ToDictionary(x => x.Id, x => x.Name);

        // ---------- 全局 PAD 数据设置（后台「PAD端数据设置」，不跟会议走）----------
        // 优先级：全局设置 > 单个包自身的旧值 > 硬编码默认。这样改了设置再导出，包内容就跟着变。
        PadGlobalCfg? pad = null;
        try
        {
            var cfgRow = await _db.SysConfigs.FirstOrDefaultAsync(x => x.ConfigKey == "pad.settings");
            if (cfgRow?.ConfigValue != null)
                pad = System.Text.Json.JsonSerializer.Deserialize<PadGlobalCfg>(cfgRow.ConfigValue,
                    new System.Text.Json.JsonSerializerOptions { PropertyNameCaseInsensitive = true });
        }
        catch { pad = null; }

        // 取「未销毁」的最新包作为配置来源：已销毁的包不应再影响后续导出
        // （否则一次测试生成/销毁就会把设备绑定、有效期带偏）
        var pkg = await _db.DataPackages.Where(x => x.MeetingId == meetingId && x.Status != PackageStatus.Destroyed)
            .OrderByDescending(x => x.Id).FirstOrDefaultAsync()
            ?? await _db.DataPackages.Where(x => x.MeetingId == meetingId)
                .OrderByDescending(x => x.Id).FirstOrDefaultAsync();

        var mcs = await _db.MeetingCadres.Where(x => x.MeetingId == meetingId)
            .OrderBy(x => x.SortNo).ToListAsync();
        var cadreIds = mcs.Select(x => x.CadreId).ToList();
        var cadres = await _db.Cadres.Where(x => cadreIds.Contains(x.Id)).ToListAsync();
        var resumes = await _db.CadreResumes.Where(x => cadreIds.Contains(x.CadreId)).ToListAsync();
        var families = await _db.CadreFamilies.Where(x => cadreIds.Contains(x.CadreId)).ToListAsync();
        var notes = await _db.CadreNotes.Where(x => cadreIds.Contains(x.CadreId)
                        && (x.MeetingId == null || x.MeetingId == meetingId)).ToListAsync();
        var mats = await _db.Materials.Where(x => x.MeetingId == meetingId).ToListAsync();
        var docs = await _db.AppointDocs.Where(x => x.MeetingId == meetingId && x.IsDeleted == 0).ToListAsync();
        var dicts = await _db.DictItems.Where(x => x.Enabled == 1).OrderBy(x => x.Sort).ToListAsync();

        var padNo = pkg == null
            ? new List<string>()
            : await (from pd in _db.PackageDevices
                     join d in _db.PadDevices on pd.PadId equals d.Id
                     where pd.PackageId == pkg.Id
                     select d.PadNo).ToListAsync();

        var matOrder0 = pkg == null
            ? new List<string>()
            : await _db.PackageItems.Where(x => x.PackageId == pkg.Id && x.Enabled == 1)
                .OrderBy(x => x.Sort).Select(x => x.MaterialType).ToListAsync();
        // 下发清单优先级：生成时显式勾选 > 全局 PAD 设置 > 旧包记录 > 全量
        List<string> matOrder;
        if (MatOrderOverride != null && MatOrderOverride.Count > 0) matOrder = MatOrderOverride;
        else if (pad?.matOrder != null && pad.matOrder.Count > 0) matOrder = pad.matOrder;
        else if (matOrder0.Count > 0) matOrder = matOrder0;
        else matOrder = new List<string>
        {
            MaterialType.AppointmentForm, MaterialType.Inspection,
            MaterialType.Report, MaterialType.Note
        };

        Directory.CreateDirectory(Path.GetDirectoryName(Path.GetFullPath(outPath))!);
        if (File.Exists(outPath)) File.Delete(outPath);

        var csb = new SqliteConnectionStringBuilder { DataSource = outPath };
        await using var conn = new SqliteConnection(csb.ConnectionString);
        await conn.OpenAsync();
        await using var tx = await conn.BeginTransactionAsync();

        // ---------- 1) 建表（PAD 端扁平结构）----------
        await Exec(conn, @"
create table pkg_config(k text primary key, v text);
create table depts(id integer primary key, code text, name text, sortNo integer, enabled integer);
create table pad_bound(no text primary key);
create table cadres(
  id integer primary key, deptCode text, deptName text, sortNo integer, issueNo text,
  name text, sex text, birth text, nation text, nativePlace text, birthPlace text,
  partyDate text, workDate text, health text, techPost text, expertise text,
  eduFullSchool text, eduFullMajor text, eduFullLevel text, eduFullDegree text,
  eduInSchool text, eduInMajor text, eduInLevel text, eduInDegree text,
  currentPost text, proposedPost text, dismissPost text,
  rewards text, exams text, appointReason text, kw text, perf text, care text, suggest text,
  result text, vote text, hasPhoto integer, photo text);
create table resumes(id integer primary key, cadreId integer, sortNo integer, period text, content text);
create table families(id integer primary key, cadreId integer, sortNo integer, relation text, name text, age text, political text, workUnit text);
create table notes(id integer primary key, cadreId integer, fieldName text, content text, source text, author text, createdAt text);
create table materials(id integer primary key, cadreId integer, type text, title text, fileName text,
  pages integer, version text, status text, uploadedBy text, uploadedAt text, seq integer);
create table appointdocs(id integer primary key, deptCode text, docNo text, title text, persons integer,
  pages integer, version text, status text);
create table doc_cadres(docId integer, cadreId integer, sort integer);
/* 任免文件原件：上传的 Word 在出包时转 PDF 存这里，PAD 直接显示原件 */
create table docfiles(docId integer primary key, fileName text, pages integer, size integer, data blob);
/* 任免文件正文：按段落存文字，PAD 端渲染成原文版式并支持点人名跳审批表 */
create table doclines(docId integer, seq integer, content text);
create table dicts(type text, code text, label text);
/* PAD 只能显示 PDF：Word 在出包时转 PDF 后存在这里（matId 与 materials.id 一一对应） */
create table files(matId integer primary key, cadreId integer, type text, fileName text, mime text, size integer, data blob);
create index ix_cadres_sort on cadres(sortNo);
create index ix_resumes_cadre on resumes(cadreId, sortNo);
create index ix_families_cadre on families(cadreId, sortNo);
create index ix_notes_cadre on notes(cadreId);
create index ix_materials_cadre on materials(cadreId, type);
");

        // ---------- 2) 配置 ----------
        var cfg = new Dictionary<string, string?>
        {
            ["formatVersion"] = "1",
            ["pkgNo"] = pkg?.PkgNo ?? ("PK" + DateTime.Now.ToString("yyyyMMdd-HHmm")),
            ["meetingId"] = meeting.Id.ToString(),
            ["meetingType"] = meeting.Type,
            ["sessionNo"] = meeting.SessionNo?.ToString(),
            ["meetingName"] = meeting.Name,
            ["heldAt"] = meeting.HeldAt?.ToString("yyyy-MM-dd HH:mm"),
            ["place"] = meeting.Place,
            ["host"] = meeting.Host,
            ["secret"] = meeting.Secret,
            ["secretStars"] = meeting.SecretStars,
            ["pwdLevel"] = meeting.PwdLevel,
            // 未配置有效期、或会议已过（默认值＝会议当天 18:00）时，给「会后 7 天」，
            // 否则包一导出就是过期状态，PAD 直接拒载（本次常委会 10-15 就踩了这个坑）
            ["expireAt"] = (pkg?.ExpireAt is { } pe && pe > DateTime.Now
                ? pe
                : ((meeting.HeldAt ?? DateTime.Now).AddDays(7) > DateTime.Now
                    ? (meeting.HeldAt ?? DateTime.Now).AddDays(7)
                    : DateTime.Now.AddDays(7))).ToString("yyyy-MM-dd HH:mm"),
            ["clearPolicy"] = FirstNonEmpty(pad?.clearPolicy, pkg?.ClearPolicy, "expire"),
            ["watermark"] = FirstNonEmpty(pad?.watermark, pkg?.Watermark),
            ["fontLevel"] = FirstNonEmpty(pad?.fontLevel, pkg?.FontLevel, "big"),
            ["allowFontChange"] = (pad?.allowFontChange ?? pkg?.AllowFontChange ?? 1).ToString(),
            ["switches"] = pad != null ? pad.SwitchesJson() : (pkg?.SwitchesJson ?? "{}"),
            ["matOrder"] = JsonSerializer.Serialize(matOrder),
            ["padBound"] = JsonSerializer.Serialize(padNo),
            ["needPwd"] = (pkg != null && pkg.Encrypt == "sm4_pwd") ? "1" : "0",
            ["pwdHash"] = pkg?.PwdHash,
            ["generatedAt"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
            ["generatedBy"] = generatedBy ?? "system",
            ["cadreCount"] = cadres.Count.ToString(),
            ["deptCount"] = (meeting.Type == MeetingType.BuWu ? depts.Count(d => d.Enabled == 1) : 0).ToString()
        };
        foreach (var kv in cfg)
            await Exec(conn, "insert into pkg_config(k,v) values($k,$v)",
                ("$k", kv.Key), ("$v", kv.Value));

        // ---------- 3) 处室 / 绑定设备 ----------
        foreach (var d in depts.Where(x => x.Enabled == 1))
            await Exec(conn, "insert into depts(id,code,name,sortNo,enabled) values($a,$b,$c,$d,$e)",
                ("$a", d.Id), ("$b", d.Code), ("$c", d.Name), ("$d", d.Sort), ("$e", d.Enabled));
        foreach (var no in padNo)
            await Exec(conn, "insert or ignore into pad_bound(no) values($a)", ("$a", no));

        // ---------- 4) 干部（含上会顺序/议题/结果）----------
        foreach (var mc in mcs)
        {
            var c = cadres.FirstOrDefault(x => x.Id == mc.CadreId);
            if (c == null) continue;
            var code = c.DeptId.HasValue && deptCode.ContainsKey(c.DeptId.Value) ? deptCode[c.DeptId.Value] : null;
            var nm = c.DeptId.HasValue && deptName.ContainsKey(c.DeptId.Value) ? deptName[c.DeptId.Value] : null;
            // 照片标记以「包里实际有没有照片数据」为准，避免 PhotoPath 有值但文件缺失时 PAD 留空却标记有照
            var photoUri = PhotoDataUri(c.PhotoPath);
            await Exec(conn, @"insert into cadres(id,deptCode,deptName,sortNo,issueNo,name,sex,birth,nation,
                nativePlace,birthPlace,partyDate,workDate,health,techPost,expertise,
                eduFullSchool,eduFullMajor,eduFullLevel,eduFullDegree,eduInSchool,eduInMajor,eduInLevel,eduInDegree,
                currentPost,proposedPost,dismissPost,rewards,exams,appointReason,kw,perf,care,suggest,
                result,vote,hasPhoto,photo)
                values($id,$dc,$dn,$sn,$iss,$nm,$sex,$birth,$nat,$np,$bp,$pd,$wd,$h,$tp,$ex,
                       $efs,$efm,$efl,$efd,$eis,$eim,$eil,$eid,$cur,$prop,$dis,$rw,$exm,$ar,
                       $kw,$perf,$care,$sug,$res,$vote,$hp,$ph)",
                ("$id", c.Id), ("$dc", code), ("$dn", nm), ("$sn", mc.SortNo), ("$iss", mc.IssueNo),
                ("$nm", c.Name), ("$sex", c.Sex), ("$birth", c.Birth), ("$nat", c.Nation),
                ("$np", c.NativePlace), ("$bp", c.BirthPlace), ("$pd", c.PartyDate), ("$wd", c.WorkDate),
                ("$h", c.Health), ("$tp", c.TechPost), ("$ex", c.Expertise),
                ("$efs", c.EduFullSchool), ("$efm", c.EduFullMajor), ("$efl", c.EduFullLevel), ("$efd", c.EduFullDegree),
                ("$eis", c.EduInSchool), ("$eim", c.EduInMajor), ("$eil", c.EduInLevel), ("$eid", c.EduInDegree),
                ("$cur", c.CurrentPost), ("$prop", c.ProposedPost), ("$dis", c.DismissPost),
                ("$rw", c.Rewards), ("$exm", c.Exams), ("$ar", c.AppointReason),
                ("$kw", c.Kw), ("$perf", c.Perf), ("$care", c.Care), ("$sug", c.Suggest),
                ("$res", mc.Result), ("$vote", mc.Vote), ("$hp", photoUri != null ? 1 : 0),
                ("$ph", photoUri));
        }

        foreach (var r in resumes)
            await Exec(conn, "insert into resumes(cadreId,sortNo,period,content) values($a,$b,$c,$d)",
                ("$a", r.CadreId), ("$b", r.Sort), ("$c", r.Period), ("$d", r.Content));
        foreach (var f in families)
            await Exec(conn, @"insert into families(cadreId,sortNo,relation,name,age,political,workUnit)
                values($a,$b,$c,$d,$e,$f,$g)",
                ("$a", f.CadreId), ("$b", f.Sort), ("$c", f.Relation), ("$d", f.Name),
                ("$e", f.Age), ("$f", f.Political), ("$g", f.WorkUnit));
        foreach (var n in notes)
            await Exec(conn, @"insert into notes(cadreId,fieldName,content,source,author,createdAt)
                values($a,$b,$c,$d,$e,$f)",
                ("$a", n.CadreId), ("$b", n.FieldName), ("$c", n.Content), ("$d", n.Source),
                ("$e", n.Author), ("$f", n.CreatedAt.ToString("yyyy-MM-dd HH:mm")));

        // 材料行显式带 id，使 PAD 端 materials.id 与 files.matId 一一对应
        var pdfCount = 0;
        foreach (var m in mats)
        {
            await Exec(conn, @"insert into materials(id,cadreId,type,title,fileName,pages,version,status,uploadedBy,uploadedAt,seq)
                values($id,$a,$b,$c,$d,$e,$f,$g,$h,$i,$j)",
                ("$id", m.Id), ("$a", m.CadreId), ("$b", m.Type), ("$c", m.Title), ("$d", m.FileName),
                ("$e", m.Pages), ("$f", m.Version), ("$g", m.Status), ("$h", m.UploadedBy),
                ("$i", m.UploadedAt?.ToString("yyyy-MM-dd HH:mm")), ("$j", m.Seq));

            // 把材料转成 PDF 一起进包（PAD 端用 pdf.js 显示原件）
            var pdfPath = MaterialPdf(m);
            if (pdfPath == null) continue;
            try
            {
                var bytes = await File.ReadAllBytesAsync(pdfPath);
                if (bytes.Length == 0) continue;
                await Exec(conn, @"insert or replace into files(matId,cadreId,type,fileName,mime,size,data)
                    values($a,$b,$c,$d,$e,$f,$g)",
                    ("$a", m.Id), ("$b", m.CadreId), ("$c", m.Type),
                    ("$d", PdfName(m)), ("$e", "application/pdf"), ("$f", bytes.LongLength), ("$g", bytes));
                pdfCount++;
            }
            catch { /* 单份材料失败不影响出包 */ }
        }

        foreach (var d in docs)
            await Exec(conn, @"insert into appointdocs(id,deptCode,docNo,title,persons,pages,version,status)
                values($a,$b,$c,$d,$e,$f,$g,$h)",
                ("$a", d.Id),
                ("$b", d.DeptId.HasValue && deptCode.ContainsKey(d.DeptId.Value) ? deptCode[d.DeptId.Value] : "single"),
                ("$c", d.DocNo), ("$d", d.Title), ("$e", d.Persons), ("$f", d.Pages),
                ("$g", d.Version), ("$h", d.Status));

        // 任免文件原件：Word 转 PDF 后写进包，PAD 端显示的就是上传的那份文件
        foreach (var d in docs)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(d.FilePath) || string.IsNullOrWhiteSpace(PhotoRoot)) continue;
                var srcFull = Path.Combine(PhotoRoot!.Replace('/', Path.DirectorySeparatorChar),
                                           d.FilePath!.Replace('/', Path.DirectorySeparatorChar));
                if (!File.Exists(srcFull)) continue;

                var pdfPath = PdfConverter.ToPdf(srcFull, Path.Combine(PhotoRoot!, "_pdfcache"),
                                                 $"doc_{d.Id}_{d.Version}");
                if (pdfPath == null) continue;

                var bytes = await File.ReadAllBytesAsync(pdfPath);
                var name = (string.IsNullOrWhiteSpace(d.Title) ? ("任免文件" + d.Id) : d.Title!);
                if (!name.EndsWith(".pdf", StringComparison.OrdinalIgnoreCase)) name += ".pdf";

                await Exec(conn,
                    "insert or replace into docfiles(docId,fileName,pages,size,data) values($a,$b,$c,$d,$e)",
                    ("$a", d.Id), ("$b", name), ("$c", d.Pages), ("$d", (long)bytes.Length), ("$e", bytes));
                pdfCount++;

                /* 正文按段落入库：PAD 用原文文字渲染，人名可点（跳干部任免审批表） */
                var plain = DocNameMatcher.ExtractText(srcFull, out _);
                if (!string.IsNullOrWhiteSpace(plain))
                {
                    var seq = 0;
                    foreach (var raw in plain.Replace("\r", "").Split('\n'))
                    {
                        var line = raw.Trim();
                        if (line.Length == 0) continue;
                        if (line.Length > 400) line = line[..400];
                        await Exec(conn, "insert into doclines(docId,seq,content) values($a,$b,$c)",
                            ("$a", d.Id), ("$b", seq++), ("$c", line));
                        if (seq >= 400) break;
                    }
                }
            }
            catch (Exception ex)
            {
                /* 单份文件失败不影响出包，但要留痕，否则静默缺件没人发现 */
                Console.WriteLine($"[pkg] 任免文件 #{d.Id} 写入失败：{ex.Message}");
            }
        }

        // 任免文件 ↔ 干部 关联（PAD 端据此显示"涉及 N 人"并标出未列入文件的人）
        var docIdsForLinks = docs.Select(x => x.Id).ToList();
        if (docIdsForLinks.Count > 0)
        {
            var links = await _db.AppointDocCadres.Where(x => docIdsForLinks.Contains(x.DocId)).ToListAsync();
            foreach (var l in links)
                await Exec(conn, "insert into doc_cadres(docId,cadreId,sort) values($a,$b,$c)",
                    ("$a", l.DocId), ("$b", l.CadreId), ("$c", l.Sort));
        }

        foreach (var d in dicts)
            await Exec(conn, "insert into dicts(type,code,label) values($a,$b,$c)",
                ("$a", d.Type), ("$b", d.Code), ("$c", d.Label));

        await tx.CommitAsync();
        await conn.CloseAsync();

        var fi = new FileInfo(outPath);
        return new ExportResult
        {
            Path = outPath,
            Bytes = fi.Length,
            PkgNo = cfg["pkgNo"] ?? "",
            Cadres = cadres.Count,
            Resumes = resumes.Count,
            Families = families.Count,
            Notes = notes.Count,
            Materials = mats.Count,
            PdfFiles = pdfCount,
            Docs = docs.Count,
            PadBound = padNo.Count
        };
    }

    /// <summary>材料 → PDF 的全路径（PDF 原样返回；Word 走转换器并缓存；其余类型返回 null）</summary>
    private string? MaterialPdf(Material m)
    {
        if (string.IsNullOrWhiteSpace(m.FilePath) || string.IsNullOrWhiteSpace(PhotoRoot)) return null;
        var root = PhotoRoot!;
        var src = Path.Combine(root.Replace('/', Path.DirectorySeparatorChar),
                               m.FilePath!.Replace('/', Path.DirectorySeparatorChar));
        var cacheDir = Path.Combine(root, "_pdfcache");
        return PdfConverter.ToPdf(src, cacheDir, $"{m.Id}_{m.Version}");
    }

    /// <summary>包内 PDF 的展示文件名（保留原名，扩展名换成 .pdf）</summary>
    private static string PdfName(Material m)
    {
        var baseName = string.IsNullOrWhiteSpace(m.FileName) ? ("材料" + m.Id) : Path.GetFileNameWithoutExtension(m.FileName);
        return baseName + ".pdf";
    }

    private static async Task Exec(SqliteConnection conn, string sql, params (string, object?)[] ps)
    {
        await using var cmd = conn.CreateCommand();
        cmd.CommandText = sql;
        foreach (var (k, v) in ps)
            cmd.Parameters.AddWithValue(k, v ?? DBNull.Value);
        await cmd.ExecuteNonQueryAsync();
    }
}

public class ExportResult
{
    public string Path { get; set; } = "";
    public long Bytes { get; set; }
    public string PkgNo { get; set; } = "";
    public int Cadres { get; set; }
    public int Resumes { get; set; }
    public int Families { get; set; }
    public int Notes { get; set; }
    public int Materials { get; set; }
    /// <summary>包内 PDF 正文数量（Word 已转 PDF）</summary>
    public int PdfFiles { get; set; }
    public int Docs { get; set; }
    public int PadBound { get; set; }
    /// <summary>是否为国密加密包（SM4-CBC + HMAC-SM3）</summary>
    public bool Encrypted { get; set; }

    public string Human() =>
        $"数据包 {PkgNo} 已生成：{Bytes / 1024.0:F1} KB" + (Encrypted ? "（国密SM4加密）" : "（明文）") + "\n" +
        $"  干部 {Cadres} 人 / 简历 {Resumes} 条 / 家庭成员 {Families} 条 / 批注 {Notes} 条\n" +
        $"  材料 {Materials} 条（其中 {PdfFiles} 份带 PDF 正文） / 任免文件 {Docs} 份 / 绑定设备 {PadBound} 台\n" +
        $"  文件：{Path}";
}

/// <summary>后台「PAD端数据设置」的全局配置（sysconfigs.pad.settings）</summary>
public class PadGlobalCfg
{
    public string? watermark { get; set; }
    public string? fontLevel { get; set; }
    public int? allowFontChange { get; set; }
    public int? copyEnabled { get; set; }
    public int? printEnabled { get; set; }
    public string? clearPolicy { get; set; }
    public List<string>? matOrder { get; set; }

    /// <summary>拼成包内 switches JSON（PAD 端 applyConfig 消费 copy / print）</summary>
    public string SwitchesJson() => System.Text.Json.JsonSerializer.Serialize(new
    {
        copy = copyEnabled == 1,
        print = printEnabled == 1,
        screenWatermark = true,
        screenshotGuard = true
    });
}