using System.Reflection;
using System.Text.Json;
using System.Text.Json.Serialization;
using FreeSql;
using F9WebAiTest.Application.Interfaces.SnowflakeID;
using F9WebAiTest.Domain.Entities.Base;
using F9WebAiTest.Domain.Entities.Prod;
using F9WebAiTest.Domain.Enums.SYS.Employees;
using SysDept = F9WebAiTest.Domain.Entities.SYS.Departments;
using SysEmp = F9WebAiTest.Domain.Entities.SYS.Employees;
using SysWt = F9WebAiTest.Domain.Entities.SYS.WorkTypes;

namespace F9WebAiTest.Infrastructure.Data;

/// <summary>
/// 生产模块基础数据种子（来源于 F9 系统导出）：
///   1) biz_prod_qs_jzct —— 机组车台
///   2) biz_prod_qs_cjbc —— 车间班次
/// 幂等：按「车间 + 机组」/「车间 + 班次」匹配，存在则校正，不存在则新增；已删除的数据不导入。
/// </summary>
public static class ProdSeedData
{
    private const string JzctResource = "F9WebAiTest.Infrastructure.Data.SeedData.biz_prod_qs_jzct.json";
    private const string CjbcResource = "F9WebAiTest.Infrastructure.Data.SeedData.biz_prod_qs_cjbc.json";
    private const string KtpbCtgResource = "F9WebAiTest.Infrastructure.Data.SeedData.biz_prod_qs_ktpb_ctg.json";
    private const string KtpbDcgResource = "F9WebAiTest.Infrastructure.Data.SeedData.biz_prod_qs_ktpb_dcg.json";
    private const string KtpbZbzResource = "F9WebAiTest.Infrastructure.Data.SeedData.biz_prod_qs_ktpb_zbz.json";
    private const string KtjlCtgResource = "F9WebAiTest.Infrastructure.Data.SeedData.biz_prod_qs_ktjl_ctg.csv";
    private const string KtjlDcgResource = "F9WebAiTest.Infrastructure.Data.SeedData.biz_prod_qs_ktjl_dcg.csv";

    private static readonly JsonSerializerOptions JsonOpts = new()
    {
        PropertyNameCaseInsensitive = true,
        NumberHandling = JsonNumberHandling.AllowReadingFromString
    };

    public static void Seed(IFreeSql fsql, ISnowflakeIdGenerator snowflake)
    {
        SeedJizuChetai(fsql);
        SeedChejianBanci(fsql);
        // 看台排班（车头工 / 挡车工 / 值班长）
        SeedKtpbCtg(fsql);
        SeedKtpbDcg(fsql);
        SeedKtpbZbz(fsql);
        // 看台记录（历史流水，一次性导入）
        SeedKtjlCtg(fsql);
        SeedKtjlDcg(fsql);
    }

    // ==================== 机组车台 ====================
    private static void SeedJizuChetai(IFreeSql fsql)
    {
        try
        {
            var items = Load<JzctItem>(JzctResource);
            if (items.Count == 0) return;

            var deptMap = BuildDeptMap(fsql);
            var existing = fsql.Select<JizuChetai>().ToList()
                .GroupBy(x => (x.DepartmentId, x.Jizu))
                .ToDictionary(g => g.Key, g => g.First());

            int added = 0, updated = 0, skipped = 0;
            var unmappedDeps = new HashSet<long>();

            foreach (var item in items)
            {
                if (!string.IsNullOrWhiteSpace(item.DeletedAt)) { skipped++; continue; }

                if (!deptMap.TryGetValue(item.DepId, out var deptId))
                {
                    unmappedDeps.Add(item.DepId);
                    skipped++;
                    continue;
                }

                var key = (deptId, item.Jizu);
                if (existing.TryGetValue(key, out var e))
                {
                    if (e.ChetaiStart != item.ChetaiStart || e.ChetaiEnd != item.ChetaiEnd)
                    {
                        e.ChetaiStart = item.ChetaiStart;
                        e.ChetaiEnd = item.ChetaiEnd;
                        fsql.Update<JizuChetai>().SetSource(e).ExecuteAffrows();
                        updated++;
                    }
                }
                else
                {
                    fsql.Insert(new JizuChetai
                    {
                        DepartmentId = deptId,
                        Jizu = item.Jizu,
                        ChetaiStart = item.ChetaiStart,
                        ChetaiEnd = item.ChetaiEnd
                    }).ExecuteAffrows();
                    added++;
                }
            }

            Console.WriteLine($"[ProdSeed] 机组车台：来源 {items.Count} 条，新增 {added} 条，更新 {updated} 条，跳过（已删除/无法映射）{skipped} 条");
            if (unmappedDeps.Count > 0)
                Console.WriteLine($"[ProdSeed] 提示：以下 F9 部门 Id 未匹配到本系统部门，已跳过 -> {string.Join(", ", unmappedDeps)}");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"[ProdSeed] 机组车台导入跳过：{ex.Message}");
        }
    }

    // ==================== 车间班次 ====================
    private static void SeedChejianBanci(IFreeSql fsql)
    {
        try
        {
            var items = Load<CjbcItem>(CjbcResource);
            if (items.Count == 0) return;

            var deptMap = BuildDeptMap(fsql);
            var existing = fsql.Select<ChejianBanci>().ToList()
                .GroupBy(x => (x.DeptId, x.Banci))
                .ToDictionary(g => g.Key, g => g.First());

            int added = 0, updated = 0, skipped = 0;
            var unmappedDeps = new HashSet<long>();

            foreach (var item in items)
            {
                if (!string.IsNullOrWhiteSpace(item.DeletedAt)) { skipped++; continue; }

                if (!deptMap.TryGetValue(item.DepId, out var deptId))
                {
                    unmappedDeps.Add(item.DepId);
                    skipped++;
                    continue;
                }

                var banci = (Prod_Banci_Enum)item.Banci;
                var key = (deptId, banci);
                if (existing.TryGetValue(key, out var e))
                {
                    if (Math.Abs(e.KaoqinDayhours - item.KaoqinDayhours) > 0.001)
                    {
                        e.KaoqinDayhours = item.KaoqinDayhours;
                        fsql.Update<ChejianBanci>().SetSource(e).ExecuteAffrows();
                        updated++;
                    }
                }
                else
                {
                    fsql.Insert(new ChejianBanci
                    {
                        DeptId = deptId,
                        Banci = banci,
                        KaoqinDayhours = item.KaoqinDayhours
                    }).ExecuteAffrows();
                    added++;
                }
            }

            Console.WriteLine($"[ProdSeed] 车间班次：来源 {items.Count} 条，新增 {added} 条，更新 {updated} 条，跳过（已删除/无法映射）{skipped} 条");
            if (unmappedDeps.Count > 0)
                Console.WriteLine($"[ProdSeed] 提示：以下 F9 部门 Id 未匹配到本系统部门，已跳过 -> {string.Join(", ", unmappedDeps)}");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"[ProdSeed] 车间班次导入跳过：{ex.Message}");
        }
    }

    // ==================== 车头工看台排班 ====================
    private static void SeedKtpbCtg(IFreeSql fsql)
    {
        try
        {
            var items = Load<KtpbCtgItem>(KtpbCtgResource);
            if (items.Count == 0) return;

            var deptMap = BuildDeptMap(fsql);
            var empIds = BuildEmpIdSet(fsql);
            var existing = fsql.Select<KtpbCtg>().ToList()
                .GroupBy(x => (x.DeptId, x.Banci, x.Jizu))
                .ToDictionary(g => g.Key, g => g.First());

            int added = 0, updated = 0, skipped = 0;
            foreach (var item in items)
            {
                if (!string.IsNullOrWhiteSpace(item.DeletedAt)) { skipped++; continue; }
                if (!deptMap.TryGetValue(item.DepId, out var deptId)) { skipped++; continue; }
                if (!empIds.Contains(item.Ygid1)) { skipped++; continue; }

                var ygid2 = empIds.Contains(item.Ygid2) ? item.Ygid2 : 0;
                var banci = (Prod_Banci_Enum)item.Banci;
                var key = (deptId, banci, item.Jizu);
                if (existing.TryGetValue(key, out var e))
                {
                    if (e.Ygid1 != item.Ygid1 || e.Ygid2 != ygid2)
                    {
                        e.Ygid1 = item.Ygid1;
                        e.Ygid2 = ygid2;
                        fsql.Update<KtpbCtg>().SetSource(e).ExecuteAffrows();
                        updated++;
                    }
                }
                else
                {
                    fsql.Insert(new KtpbCtg
                    {
                        DeptId = deptId,
                        Banci = banci,
                        Jizu = item.Jizu,
                        Ygid1 = item.Ygid1,
                        Ygid2 = ygid2
                    }).ExecuteAffrows();
                    added++;
                }
            }

            Console.WriteLine($"[ProdSeed] 车头工看台排班：来源 {items.Count} 条，新增 {added} 条，更新 {updated} 条，跳过 {skipped} 条");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"[ProdSeed] 车头工看台排班导入跳过：{ex.Message}");
        }
    }

    // ==================== 挡车工看台排班 ====================
    private static void SeedKtpbDcg(IFreeSql fsql)
    {
        try
        {
            var items = Load<KtpbDcgItem>(KtpbDcgResource);
            if (items.Count == 0) return;

            var deptMap = BuildDeptMap(fsql);
            var empIds = BuildEmpIdSet(fsql);
            // 已有排班的「车间+班次+机组」整组跳过，避免每次启动重复插入（人工维护的排班不被覆盖）
            var existGroups = fsql.Select<KtpbDcg>().ToList()
                .Select(x => (x.DeptId, x.Banci, x.Jizu))
                .ToHashSet();

            int added = 0, skipped = 0;
            var handledGroups = new HashSet<(long, Prod_Banci_Enum, int)>();

            foreach (var item in items)
            {
                if (!string.IsNullOrWhiteSpace(item.DeletedAt)) { skipped++; continue; }
                if (!deptMap.TryGetValue(item.DepId, out var deptId)) { skipped++; continue; }
                if (!empIds.Contains(item.YgId)) { skipped++; continue; }
                if (item.ChetaiStart <= 0 || item.ChetaiEnd < item.ChetaiStart) { skipped++; continue; }

                var banci = (Prod_Banci_Enum)item.Banci;
                var group = (deptId, banci, item.Jizu);
                if (existGroups.Contains(group)) { skipped++; continue; }

                fsql.Insert(new KtpbDcg
                {
                    DeptId = deptId,
                    Banci = banci,
                    Jizu = item.Jizu,
                    ChetaiStart = item.ChetaiStart,
                    ChetaiEnd = item.ChetaiEnd,
                    YgId = item.YgId,
                    Dangcheshu = item.Dangcheshu
                }).ExecuteAffrows();
                handledGroups.Add(group);
                added++;
            }

            Console.WriteLine($"[ProdSeed] 挡车工看台排班：来源 {items.Count} 条，新增 {added} 条（{handledGroups.Count} 组），跳过 {skipped} 条");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"[ProdSeed] 挡车工看台排班导入跳过：{ex.Message}");
        }
    }

    // ==================== 值班长看台排班 ====================
    private static void SeedKtpbZbz(IFreeSql fsql)
    {
        try
        {
            var items = Load<KtpbZbzItem>(KtpbZbzResource);
            if (items.Count == 0) return;

            var deptMap = BuildDeptMap(fsql);
            var empIds = BuildEmpIdSet(fsql);
            var exist = fsql.Select<KtpbZbz>().ToList()
                .Select(x => (x.DeptId, x.Banci, x.YgId))
                .ToHashSet();

            int added = 0, skipped = 0;
            foreach (var item in items)
            {
                if (!string.IsNullOrWhiteSpace(item.DeletedAt)) { skipped++; continue; }
                if (!deptMap.TryGetValue(item.DepId, out var deptId)) { skipped++; continue; }
                if (!empIds.Contains(item.YgId)) { skipped++; continue; }

                var banci = (Prod_Banci_Enum)item.Banci;
                if (!exist.Add((deptId, banci, item.YgId))) { skipped++; continue; }

                fsql.Insert(new KtpbZbz
                {
                    DeptId = deptId,
                    Banci = banci,
                    YgId = item.YgId
                }).ExecuteAffrows();
                added++;
            }

            Console.WriteLine($"[ProdSeed] 值班长看台排班：来源 {items.Count} 条，新增 {added} 条，跳过 {skipped} 条");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"[ProdSeed] 值班长看台排班导入跳过：{ex.Message}");
        }
    }

    // ==================== 看台记录 ====================

    /// <summary>
    /// 车头工看台记录（历史流水）：一次性导入，表内已有数据则跳过。
    /// 数据格式：date|depId|banci|jizu|hours|employeeId|employeeDepId|worktypeId|noChanliang
    /// </summary>
    private static void SeedKtjlCtg(IFreeSql fsql)
    {
        try
        {
            if (fsql.Select<KtjlCtg>().Any()) { return; }
            var lines = LoadCsv(KtjlCtgResource);
            if (lines.Count == 0) return;

            var deptMap = BuildDeptMap(fsql);
            var empIds = BuildEmpIdSet(fsql);
            var wtNames = fsql.Select<SysWt.WorkType>().ToList().ToDictionary(w => w.Id, w => w.WorkName);

            var batch = new List<KtjlCtg>(1000);
            int added = 0, skipped = 0;
            foreach (var line in lines)
            {
                var f = line.Split('|');
                if (f.Length < 9) { skipped++; continue; }
                if (!DateTime.TryParse(f[0], out var date)) { skipped++; continue; }
                if (!deptMap.TryGetValue(long.Parse(f[1]), out var depId)) { skipped++; continue; }
                if (!empIds.Contains(long.Parse(f[5]))) { skipped++; continue; }

                var wtId = long.Parse(f[7]);
                batch.Add(new KtjlCtg
                {
                    Date = date.Date,
                    DeptId = depId,
                    Banci = (Prod_Banci_Enum)int.Parse(f[2]),
                    Jizu = int.Parse(f[3]),
                    Hours = decimal.Parse(f[4]),
                    EmployeeId = long.Parse(f[5]),
                    EmployeeDepId = deptMap.TryGetValue(long.Parse(f[6]), out var ed) ? ed : depId,
                    WorktypeId = wtId,
                    WorktypeName = wtNames.GetValueOrDefault(wtId, ""),
                    NoChanliang = f[8] == "1" || f[8].Equals("true", StringComparison.OrdinalIgnoreCase)
                });
                if (batch.Count >= 1000)
                {
                    fsql.Insert(batch).ExecuteAffrows();
                    added += batch.Count;
                    batch.Clear();
                }
            }
            if (batch.Count > 0)
            {
                fsql.Insert(batch).ExecuteAffrows();
                added += batch.Count;
            }
            Console.WriteLine($"[ProdSeed] 车头工看台记录：来源 {lines.Count} 行，导入 {added} 行，跳过 {skipped} 行");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"[ProdSeed] 车头工看台记录导入跳过：{ex.Message}");
        }
    }

    /// <summary>
    /// 挡车工看台记录（历史流水）：一次性导入，表内已有数据则跳过。
    /// 数据格式：date|depId|banci|jizu|chetaiStart|chetaiEnd|dangcheNum|hours|employeeId|employeeDepId|worktypeId|noChanliang
    /// </summary>
    private static void SeedKtjlDcg(IFreeSql fsql)
    {
        try
        {
            if (fsql.Select<KtjlDcg>().Any()) { return; }
            var lines = LoadCsv(KtjlDcgResource);
            if (lines.Count == 0) return;

            var deptMap = BuildDeptMap(fsql);
            var empIds = BuildEmpIdSet(fsql);
            var wtNames = fsql.Select<SysWt.WorkType>().ToList().ToDictionary(w => w.Id, w => w.WorkName);

            var batch = new List<KtjlDcg>(1000);
            int added = 0, skipped = 0;
            foreach (var line in lines)
            {
                var f = line.Split('|');
                if (f.Length < 12) { skipped++; continue; }
                if (!DateTime.TryParse(f[0], out var date)) { skipped++; continue; }
                if (!deptMap.TryGetValue(long.Parse(f[1]), out var depId)) { skipped++; continue; }
                if (!empIds.Contains(long.Parse(f[8]))) { skipped++; continue; }

                var wtId = long.Parse(f[10]);
                batch.Add(new KtjlDcg
                {
                    Date = date.Date,
                    DeptId = depId,
                    Banci = (Prod_Banci_Enum)int.Parse(f[2]),
                    Jizu = int.Parse(f[3]),
                    ChetaiStart = int.Parse(f[4]),
                    ChetaiEnd = int.Parse(f[5]),
                    DangcheNum = decimal.Parse(f[6]),
                    Hours = decimal.Parse(f[7]),
                    EmployeeId = long.Parse(f[8]),
                    EmployeeDepId = deptMap.TryGetValue(long.Parse(f[9]), out var ed) ? ed : depId,
                    WorktypeId = wtId,
                    WorktypeName = wtNames.GetValueOrDefault(wtId, ""),
                    NoChanliang = f[11] == "1" || f[11].Equals("true", StringComparison.OrdinalIgnoreCase)
                });
                if (batch.Count >= 1000)
                {
                    fsql.Insert(batch).ExecuteAffrows();
                    added += batch.Count;
                    batch.Clear();
                }
            }
            if (batch.Count > 0)
            {
                fsql.Insert(batch).ExecuteAffrows();
                added += batch.Count;
            }
            Console.WriteLine($"[ProdSeed] 挡车工看台记录：来源 {lines.Count} 行，导入 {added} 行，跳过 {skipped} 行");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"[ProdSeed] 挡车工看台记录导入跳过：{ex.Message}");
        }
    }

    private static List<string> LoadCsv(string resourceName)
    {
        var asm = Assembly.GetExecutingAssembly();
        using var stream = asm.GetManifestResourceStream(resourceName);
        if (stream == null) return new List<string>();
        using var reader = new StreamReader(stream);
        var list = new List<string>();
        while (!reader.EndOfStream)
        {
            var line = reader.ReadLine();
            if (!string.IsNullOrWhiteSpace(line)) list.Add(line);
        }
        return list;
    }

    // ==================== 公共 ====================

    /// <summary>本系统存在的职工 Id 集合（F9 职工导出时保留了原始雪花 Id，可直接比对）</summary>
    private static HashSet<long> BuildEmpIdSet(IFreeSql fsql)
        => fsql.Select<SysEmp.Employee>().ToList().Select(e => e.Id).ToHashSet();


    /// <summary>建立 F9 部门 Id → 本系统部门 Id 的映射（以部门名称为桥梁）</summary>
    private static Dictionary<long, long> BuildDeptMap(IFreeSql fsql)
    {
        var f9NameMap = BusinessSeedData.DeptData.ToDictionary(x => x.Id, x => x.Name);
        var localNameMap = fsql.Select<SysDept.Department>().ToList()
            .GroupBy(d => d.Name)
            .ToDictionary(g => g.Key, g => g.First().Id);

        var map = new Dictionary<long, long>();
        foreach (var (f9Id, name) in f9NameMap)
        {
            if (localNameMap.TryGetValue(name, out var localId)) map[f9Id] = localId;
        }
        return map;
    }

    private static List<T> Load<T>(string resourceName)
    {
        var asm = Assembly.GetExecutingAssembly();
        using var stream = asm.GetManifestResourceStream(resourceName);
        if (stream == null) return new List<T>();
        return JsonSerializer.Deserialize<List<T>>(stream, JsonOpts) ?? new List<T>();
    }

    // ---- 数据项 ----
    private class JzctItem
    {
        [JsonPropertyName("id")] public long Id { get; set; }
        [JsonPropertyName("deleted_at")] public string? DeletedAt { get; set; }
        [JsonPropertyName("dep_id")] public long DepId { get; set; }
        [JsonPropertyName("jizu")] public int Jizu { get; set; }
        [JsonPropertyName("chetai_start")] public int ChetaiStart { get; set; }
        [JsonPropertyName("chetai_end")] public int ChetaiEnd { get; set; }
    }

    private class CjbcItem
    {
        [JsonPropertyName("id")] public long Id { get; set; }
        [JsonPropertyName("deleted_at")] public string? DeletedAt { get; set; }
        [JsonPropertyName("dep_id")] public long DepId { get; set; }
        [JsonPropertyName("banci")] public int Banci { get; set; }
        [JsonPropertyName("kaoqin_dayhours")] public double KaoqinDayhours { get; set; }
    }

    private class KtpbCtgItem
    {
        [JsonPropertyName("id")] public long Id { get; set; }
        [JsonPropertyName("deleted_at")] public string? DeletedAt { get; set; }
        [JsonPropertyName("dep_id")] public long DepId { get; set; }
        [JsonPropertyName("banci")] public int Banci { get; set; }
        [JsonPropertyName("jizu")] public int Jizu { get; set; }
        [JsonPropertyName("ygid_1")] public long Ygid1 { get; set; }
        [JsonPropertyName("ygid_2")] public long Ygid2 { get; set; }
    }

    private class KtpbDcgItem
    {
        [JsonPropertyName("id")] public long Id { get; set; }
        [JsonPropertyName("deleted_at")] public string? DeletedAt { get; set; }
        [JsonPropertyName("dep_id")] public long DepId { get; set; }
        [JsonPropertyName("banci")] public int Banci { get; set; }
        [JsonPropertyName("jizu")] public int Jizu { get; set; }
        [JsonPropertyName("chetai_start")] public int ChetaiStart { get; set; }
        [JsonPropertyName("chetai_end")] public int ChetaiEnd { get; set; }
        [JsonPropertyName("yg_id")] public long YgId { get; set; }
        [JsonPropertyName("dangcheshu")] public double Dangcheshu { get; set; }
    }

    private class KtpbZbzItem
    {
        [JsonPropertyName("id")] public long Id { get; set; }
        [JsonPropertyName("deleted_at")] public string? DeletedAt { get; set; }
        [JsonPropertyName("dep_id")] public long DepId { get; set; }
        [JsonPropertyName("banci")] public int Banci { get; set; }
        [JsonPropertyName("yg_id")] public long YgId { get; set; }
    }
}