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;
        var b64 = SysConfigStore.GetOrCreate(connStr, "PackageSm4Key",
            PackageCrypto.NewMasterKeyBase64,
            "数据包 SM4 主密钥（勿外泄；轮换需同时更新 APK）");
        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; }

    private string? PhotoDataUri(string? photoPath)
    {
        if (string.IsNullOrWhiteSpace(PhotoRoot) || string.IsNullOrWhiteSpace(photoPath)) return null;
        try
        {
            var full = Path.Combine(PhotoRoot!, photoPath.Replace('/', Path.DirectorySeparatorChar));
            if (!File.Exists(full)) 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)
    {
        var tmp = outPath + ".plain.tmp";
        ExportResult r;
        try
        {
            r = await ExportAsync(meetingId, tmp, generatedBy);
            var plain = await File.ReadAllBytesAsync(tmp);
            var pkg = PackageCrypto.Encode(plain, MasterKey());
            await File.WriteAllBytesAsync(outPath, pkg);
            r.Path = outPath;
            r.Bytes = pkg.LongLength;
            r.Encrypted = true;
        }
        finally
        {
            WipeAndDelete(tmp);
        }
        return r;
    }

    /// <summary>明文临时文件：先用 0 覆盖再删，降低被恢复的可能</summary>
    private static void WipeAndDelete(string path)
    {
        try
        {
            if (!File.Exists(path)) return;
            var len = new FileInfo(path).Length;
            using (var fs = new FileStream(path, FileMode.Open, FileAccess.Write, FileShare.None))
            {
                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 { /* 清理失败不阻断导出 */ }
    }

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

        // 取「未销毁」的最新包作为配置来源：已销毁的包不应再影响后续导出
        // （否则一次测试生成/销毁就会把设备绑定、有效期带偏）
        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();
        // 未配置下发清单时，按 5 类材料全量下发；否则 PAD 端 applyConfig 会按空清单隐藏所有材料页签
        var matOrder = matOrder0.Count > 0 ? matOrder0 : new List<string>
        {
            MaterialType.AppointmentForm, MaterialType.Inspection,
            MaterialType.Report, MaterialType.Note, MaterialType.Attachment
        };

        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 dicts(type text, code text, label text);
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"] = pkg?.ClearPolicy ?? "expire",
            ["watermark"] = pkg?.Watermark,
            ["fontLevel"] = pkg?.FontLevel ?? "big",
            ["allowFontChange"] = (pkg?.AllowFontChange ?? 1).ToString(),
            ["switches"] = 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")));

        foreach (var m in mats)
            await Exec(conn, @"insert into materials(cadreId,type,title,fileName,pages,version,status,uploadedBy,uploadedAt,seq)
                values($a,$b,$c,$d,$e,$f,$g,$h,$i,$j)",
                ("$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));

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

        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,
            Docs = docs.Count,
            PadBound = padNo.Count
        };
    }

    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; }
    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} 条 / 任免文件 {Docs} 份 / 绑定设备 {PadBound} 台\n" +
        $"  文件：{Path}";
}