using BCrypt.Net;
using Microsoft.EntityFrameworkCore;
using MilliSim.Common.Entities;
using MilliSim.Common.Models;
using MilliSim.Common.Dtos;
using System.Text.Json;

namespace MilliSim.Data;

public static class DbSeeder
{
    public static async Task SeedAsync(MilliSimDbContext db)
    {
        // 先执行数据库结构补丁（为已存在的表添加新字段）
        await PatchDatabaseSchemaAsync(db);

        // 管理员
        if (!db.Users.Any(u => u.Username == "admin"))
        {
            db.Users.Add(new User
            {
                Username = "admin",
                Password = BCryptHelper.HashPassword("admin123"),
                Nickname = "系统管理员",
                Role = UserRole.Admin,
                Status = UserStatus.Active,
                Email = "admin@millisim.com",
                Phone = "13800000000"
            });
        }

        // 产品经理
        if (!db.Users.Any(u => u.Username == "pm"))
        {
            db.Users.Add(new User
            {
                Username = "pm",
                Password = BCryptHelper.HashPassword("123456"),
                Nickname = "产品经理",
                Role = UserRole.ProductManager,
                Status = UserStatus.Active,
                Email = "pm@millisim.com",
                Phone = "13800000001"
            });
        }

        // 销售
        if (!db.Users.Any(u => u.Username == "sales"))
        {
            db.Users.Add(new User
            {
                Username = "sales",
                Password = BCryptHelper.HashPassword("123456"),
                Nickname = "销售员",
                Role = UserRole.Sales,
                Status = UserStatus.Active,
                Email = "sales@millisim.com",
                Phone = "13800000002"
            });
        }

        // 分类
        if (!db.ProjectCategories.Any())
        {
            db.ProjectCategories.AddRange(
                new ProjectCategory { Name = "工业仿真", Icon = "factory", SortOrder = 1, Status = UserStatus.Active },
                new ProjectCategory { Name = "医疗仿真", Icon = "medical", SortOrder = 2, Status = UserStatus.Active },
                new ProjectCategory { Name = "教育仿真", Icon = "education", SortOrder = 3, Status = UserStatus.Active },
                new ProjectCategory { Name = "军事仿真", Icon = "military", SortOrder = 4, Status = UserStatus.Active },
                new ProjectCategory { Name = "建筑仿真", Icon = "building", SortOrder = 5, Status = UserStatus.Active }
            );
            await db.SaveChangesAsync();
        }

        // 平台类型
        if (!db.PlatformTypes.Any())
        {
            db.PlatformTypes.AddRange(
                new PlatformType { Name = "WebGL", Icon = "web", SortOrder = 1, Status = UserStatus.Active },
                new PlatformType { Name = "Android", Icon = "android", SortOrder = 2, Status = UserStatus.Active },
                new PlatformType { Name = "Windows", Icon = "windows", SortOrder = 3, Status = UserStatus.Active },
                new PlatformType { Name = "Mac", Icon = "apple", SortOrder = 4, Status = UserStatus.Active },
                new PlatformType { Name = "VR", Icon = "vr", SortOrder = 5, Status = UserStatus.Active }
            );
            await db.SaveChangesAsync();
        }

        // 特性标签
        if (!db.FeatureTags.Any())
        {
            db.FeatureTags.AddRange(
                new FeatureTag { Name = "高精度", Icon = "precision", SortOrder = 1, Status = UserStatus.Active },
                new FeatureTag { Name = "实时渲染", Icon = "render", SortOrder = 2, Status = UserStatus.Active },
                new FeatureTag { Name = "物理引擎", Icon = "physics", SortOrder = 3, Status = UserStatus.Active },
                new FeatureTag { Name = "多人协作", Icon = "collaboration", SortOrder = 4, Status = UserStatus.Active },
                new FeatureTag { Name = "VR支持", Icon = "vr", SortOrder = 5, Status = UserStatus.Active }
            );
            await db.SaveChangesAsync();
        }

        // 示例项目
        if (!db.Projects.Any())
        {
            var categories = db.ProjectCategories.OrderBy(c => c.SortOrder).ToList();
            db.Projects.AddRange(
                new Project
                {
                    Name = "智能工厂生产线仿真",
                    CategoryId = categories[0].Id,
                    CoverImage = "",
                    Description = "基于Unity开发的智能工厂生产线三维仿真系统，支持产线布局规划与工艺流程模拟。",
                    Content = "<p>智能工厂生产线仿真系统，采用物理引擎真实模拟生产流程。</p>",
                    Platforms = "WebGL,Windows",
                    Status = ProjectStatus.Published,
                    IsFeatured = true,
                    SortOrder = 1,
                    ViewCount = 1280,
                    ExperienceCount = 356,
                    FavoriteCount = 89,
                    PublishedAt = DateTime.Now.AddDays(-10)
                },
                new Project
                {
                    Name = "人体解剖学虚拟仿真",
                    CategoryId = categories[1].Id,
                    CoverImage = "",
                    Description = "三维人体解剖学教学仿真，支持器官拆解与结构学习。",
                    Content = "<p>人体解剖学虚拟仿真教学系统。</p>",
                    Platforms = "WebGL,VR",
                    Status = ProjectStatus.Published,
                    IsFeatured = true,
                    SortOrder = 2,
                    ViewCount = 980,
                    ExperienceCount = 245,
                    FavoriteCount = 67,
                    PublishedAt = DateTime.Now.AddDays(-5)
                },
                new Project
                {
                    Name = "化学反应实验仿真",
                    CategoryId = categories[2].Id,
                    CoverImage = "",
                    Description = "虚拟化学实验室，支持多种化学反应模拟与安全培训。",
                    Content = "<p>化学反应实验仿真系统。</p>",
                    Platforms = "WebGL,Android",
                    Status = ProjectStatus.Published,
                    IsFeatured = false,
                    SortOrder = 3,
                    ViewCount = 560,
                    ExperienceCount = 120,
                    FavoriteCount = 34,
                    PublishedAt = DateTime.Now.AddDays(-2)
                }
            );
            await db.SaveChangesAsync();
        }

        // 轮播图
        if (!db.Banners.Any())
        {
            db.Banners.AddRange(
                new Banner { Title = "毫秒数仿 - 专业虚拟仿真平台", Image = "", Link = "/projects", SortOrder = 1, Status = UserStatus.Active },
                new Banner { Title = "海量项目资源", Image = "", Link = "/projects", SortOrder = 2, Status = UserStatus.Active },
                new Banner { Title = "在线体验 WebGL", Image = "", Link = "/projects", SortOrder = 3, Status = UserStatus.Active }
            );
        }

        // 系统设置（补充缺失的设置项）
        var allSettings = new List<(string Key, string Value, string Description)>
        {
            // ===== 基础信息 =====
            ("PlatformName", "毫秒数仿", "平台名称"),
            ("Slogan", "专业虚拟仿真项目资产库", "平台标语"),
            ("CompanyName", "毫秒数仿科技有限公司", "公司名称"),
            ("Contact", "400-888-8888", "联系方式"),
            ("ICP", "沪ICP备XXXXXXXX号", "备案号"),

            // ===== Logo 配置 =====
            ("LogoFrontend", "", "前台Logo"),
            ("LogoBackend", "", "后台Logo"),
            ("LogoFavicon", "", "浏览器图标"),
            ("LogoLogin", "", "登录页Logo"),
            ("LogoFooter", "", "页脚Logo"),

            // ===== 资源存储配置（默认COS私有读写） =====
            ("StorageMode", "Cos", "存储模式：Local=本地存储，Cos=腾讯云COS"),
            ("CosSecretId", "AKIDvmQp11oNjtqa7sTr23sTzIPVlvvmR6lO", "腾讯云COS SecretId"),
            ("CosSecretKey", "VqHWm083mKjzIXdZN7sTtIct69z9B2gL", "腾讯云COS SecretKey"),
            ("CosBucket", "millisim-1300718495", "腾讯云COS 存储桶名称"),
            ("CosRegion", "ap-guangzhou", "腾讯云COS 地域"),
            ("CosSignedUrlExpireMinutes", "60", "COS临时URL有效期（分钟）"),

            // ===== 本地存储配置 =====
            ("LocalBasePath", "wwwroot/uploads", "本地存储根目录"),
            ("LocalBaseUrl", "/uploads", "本地访问基础URL"),

            // ===== 上传大小限制（字节） =====
            ("UploadLimitImage", "5242880", "图片上传限制(字节) 5MB"),
            ("UploadLimitVideo", "524288000", "视频上传限制(字节) 500MB"),
            ("UploadLimitPackage", "2147483648", "资源包上传限制(字节) 2GB"),
            ("UploadLimitDocument", "20971520", "文档上传限制(字节) 20MB"),

            // ===== 备份配置 =====
            ("BackupAutoEnabled", "true", "是否启用自动备份"),
            ("BackupAutoTime", "02:00", "自动备份时间(HH:mm)"),
            ("BackupRetentionDays", "30", "备份保留天数"),

            // ===== 日志配置 =====
            ("LogRetentionDays", "90", "日志保留天数"),

            // ===== 首页模块显示 =====
            ("ShowPartners", "true", "是否在前台首页显示合作伙伴模块"),
            ("ShowAbout", "true", "是否在前台显示关于我们页面"),

            // ===== 关于我们页面内容（JSON） =====
            ("AboutContent", JsonSerializer.Serialize(new AboutContentDto
            {
                HeroTag = "关于我们",
                HeroTitle = "用技术连接虚拟与现实",
                HeroDesc = "毫秒数仿科技有限公司，专注工业仿真与数字孪生技术创新，致力于让每一个仿真场景都触手可及。",
                IntroTitle = "公司简介",
                IntroParagraphs = new List<string>
                {
                    "毫秒数仿科技有限公司成立于 2020 年，是一家专注于工业仿真与数字孪生领域的国家高新技术企业。公司总部位于上海，在北京、深圳、成都设有研发中心，业务覆盖全国及海外市场。",
                    "我们以\u201C让工业仿真触手可及\u201D为使命，自主研发了基于 WebGL 的高性能仿真引擎与多端运行框架，服务于制造、医疗、教育、军事、建筑等多个行业领域，累计交付项目超过 500 个，服务客户包括多家世界 500 强企业。",
                    "公司拥有一支由资深图形学专家、仿真工程师、行业顾问组成的精英团队，核心成员来自国内外知名高校与企业，具备深厚的技术积累与丰富的行业经验。"
                },
                Stats = new List<AboutStatDto>
                {
                    new() { Number = "500+", Label = "交付项目" },
                    new() { Number = "200+", Label = "服务客户" },
                    new() { Number = "50+", Label = "团队成员" },
                    new() { Number = "30+", Label = "行业领域" }
                },
                MissionTitle = "使命与愿景",
                MissionCards = new List<AboutMissionCardDto>
                {
                    new() { Title = "使命", Icon = "rocket", Desc = "让工业仿真触手可及，以技术创新驱动产业升级。" },
                    new() { Title = "愿景", Icon = "globe", Desc = "成为全球领先的工业仿真与数字孪生平台，连接虚拟与现实，赋能千行百业。" },
                    new() { Title = "价值观", Icon = "heart", Desc = "客户至上、技术驱动、开放协作、追求卓越。" }
                },
                TimelineTitle = "发展历程",
                Timeline = new List<AboutTimelineItemDto>
                {
                    new() { Year = "2020", Title = "公司成立", Desc = "毫秒数仿在上海张江正式成立，开启工业仿真技术探索之路。" },
                    new() { Year = "2021", Title = "引擎发布", Desc = "自主研发的 WebGL 仿真引擎 v1.0 正式发布，支持基础渲染与交互。" },
                    new() { Year = "2022", Title = "多端覆盖", Desc = "完成 Web、Android、Windows、iOS 多端运行框架，业务覆盖 10+ 行业。" },
                    new() { Year = "2023", Title = "数字孪生", Desc = "推出数字孪生平台，融合实时数据驱动与协同交互能力，服务客户超 200 家。" },
                    new() { Year = "2024", Title = "生态构建", Desc = "开放平台能力，构建合作伙伴生态，累计交付项目突破 500 个。" }
                }
            }), "关于我们页面内容（JSON 格式）")
        };

        var existingKeys = db.SystemSettings.Select(s => s.Key).ToHashSet();
        var missingSettings = allSettings
            .Where(s => !existingKeys.Contains(s.Key))
            .Select(s => new SystemSetting { Key = s.Key, Value = s.Value, Description = s.Description })
            .ToList();

        if (missingSettings.Count > 0)
        {
            db.SystemSettings.AddRange(missingSettings);
        }

        // 合作伙伴
        if (!db.Partners.Any())
        {
            db.Partners.AddRange(
                new Partner { Name = "腾讯云", Logo = "", Link = "#", SortOrder = 1, IsVisible = true },
                new Partner { Name = "Unity", Logo = "", Link = "#", SortOrder = 2, IsVisible = true }
            );
        }

        await db.SaveChangesAsync();
    }

    /// <summary>
    /// 数据库结构补丁：为已存在的表添加新字段
    /// 因为使用 EnsureCreated() 不会更新已存在数据库的表结构，
    /// 所以需要手动执行 ALTER TABLE 添加新字段
    /// </summary>
    private static async Task PatchDatabaseSchemaAsync(MilliSimDbContext db)
    {
        try
        {
            // 检查 ProjectPackages 表是否需要添加新字段
            var columns = await db.Database.SqlQueryRaw<string>(
                "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ProjectPackages'"
            ).ToListAsync();

            var columnSet = columns.ToHashSet();

            // 添加 WakeProtocolPrefix 字段
            if (!columnSet.Contains("WakeProtocolPrefix"))
            {
                await db.Database.ExecuteSqlRawAsync(
                    "ALTER TABLE `ProjectPackages` ADD COLUMN `WakeProtocolPrefix` VARCHAR(50) NULL DEFAULT ''");
            }

            // 添加 WakeProtocolParam 字段
            if (!columnSet.Contains("WakeProtocolParam"))
            {
                await db.Database.ExecuteSqlRawAsync(
                    "ALTER TABLE `ProjectPackages` ADD COLUMN `WakeProtocolParam` VARCHAR(500) NULL DEFAULT ''");
            }

            // 添加 ExtractedPath 字段
            if (!columnSet.Contains("ExtractedPath"))
            {
                await db.Database.ExecuteSqlRawAsync(
                    "ALTER TABLE `ProjectPackages` ADD COLUMN `ExtractedPath` VARCHAR(500) NULL DEFAULT ''");
            }

            // 添加 FileName 字段（原始文件名）
            if (!columnSet.Contains("FileName"))
            {
                await db.Database.ExecuteSqlRawAsync(
                    "ALTER TABLE `ProjectPackages` ADD COLUMN `FileName` VARCHAR(255) NULL DEFAULT ''");
            }

            // ===== 修复 Authorizations.ProjectId：客户级授权不再关联项目 =====
            // 1) 移除外键约束 FK_Authorizations_Projects_ProjectId（若存在）
            // 2) 让 ProjectId 列可空（实体已声明为 long?）
            // 注意：SqlQueryRaw<string> 要求列别名必须是 Value
            var fkName = await db.Database.SqlQueryRaw<string>(
                "SELECT CONSTRAINT_NAME AS `Value` FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS " +
                "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'Authorizations' " +
                "AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME = 'FK_Authorizations_Projects_ProjectId'"
            ).FirstOrDefaultAsync();

            if (fkName != null)
            {
                await db.Database.ExecuteSqlRawAsync(
                    "ALTER TABLE `Authorizations` DROP FOREIGN KEY `FK_Authorizations_Projects_ProjectId`");
            }

            // 让 ProjectId 列可空
            var projectIdNullable = await db.Database.SqlQueryRaw<string>(
                "SELECT IS_NULLABLE AS `Value` FROM INFORMATION_SCHEMA.COLUMNS " +
                "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'Authorizations' AND COLUMN_NAME = 'ProjectId'"
            ).FirstOrDefaultAsync();

            if (projectIdNullable != null && projectIdNullable != "YES")
            {
                await db.Database.ExecuteSqlRawAsync(
                    "ALTER TABLE `Authorizations` MODIFY COLUMN `ProjectId` BIGINT NULL");
            }

            // ===== 修复分类/平台/标签表 Description 字段：实体模型已移除 Description，但 DB 列可能仍为 NOT NULL =====
            // 导致 DbSeeder 插入新记录时失败（"Field 'Description' doesn't have a default value"）
            var descTablesToPatch = new[] { "ProjectCategories", "PlatformTypes", "FeatureTags" };
            foreach (var table in descTablesToPatch)
            {
                var descInfo = await db.Database.SqlQueryRaw<string>(
                    "SELECT IS_NULLABLE AS `Value` FROM INFORMATION_SCHEMA.COLUMNS " +
                    "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '" + table + "' AND COLUMN_NAME = 'Description'"
                ).FirstOrDefaultAsync();

                if (descInfo != null && descInfo != "YES")
                {
                    await db.Database.ExecuteSqlRawAsync(
                        "ALTER TABLE `" + table + "` MODIFY COLUMN `Description` VARCHAR(200) NULL DEFAULT ''");
                }
            }

            // ===== 修复 ProjectPackages.Version 字段：实体模型已移除 Version，但 DB 列可能仍为 NOT NULL =====
            var versionInfo = await db.Database.SqlQueryRaw<string>(
                "SELECT IS_NULLABLE AS `Value` FROM INFORMATION_SCHEMA.COLUMNS " +
                "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ProjectPackages' AND COLUMN_NAME = 'Version'"
            ).FirstOrDefaultAsync();

            if (versionInfo != null && versionInfo != "YES")
            {
                await db.Database.ExecuteSqlRawAsync(
                    "ALTER TABLE `ProjectPackages` MODIFY COLUMN `Version` VARCHAR(50) NULL DEFAULT ''");
            }

            // ===== PendingUploads 表：垃圾资源清理跟踪 =====
            var pendingTableExists = await db.Database.SqlQueryRaw<string>(
                "SELECT TABLE_NAME AS `Value` FROM INFORMATION_SCHEMA.TABLES " +
                "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'PendingUploads'"
            ).FirstOrDefaultAsync();

            if (pendingTableExists == null)
            {
                await db.Database.ExecuteSqlRawAsync(@"
                    CREATE TABLE `PendingUploads` (
                        `Id` BIGINT NOT NULL AUTO_INCREMENT,
                        `ObjectKey` VARCHAR(500) NOT NULL,
                        `Folder` VARCHAR(50) NOT NULL DEFAULT '',
                        `UploadedBy` BIGINT NULL,
                        `UploadedAt` DATETIME NOT NULL,
                        `ConsumedAt` DATETIME NULL,
                        `CreatedAt` DATETIME NOT NULL,
                        `UpdatedAt` DATETIME NOT NULL,
                        `IsDeleted` BIT NOT NULL DEFAULT 0,
                        PRIMARY KEY (`Id`),
                        INDEX `IX_PendingUploads_ObjectKey` (`ObjectKey`),
                        INDEX `IX_PendingUploads_ConsumedAt` (`ConsumedAt`)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
            }
        }
        catch
        {
            // 忽略补丁错误（如数据库不存在时 INFORMATION_SCHEMA 查询失败）
        }
    }
}

public static class BCryptHelper
{
    public static string HashPassword(string password)
    {
        return BCrypt.Net.BCrypt.HashPassword(password, 12);
    }

    public static bool VerifyPassword(string password, string hash)
    {
        return BCrypt.Net.BCrypt.Verify(password, hash);
    }
}