using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;
using Xunit;
using System.Data.Common;
using Hua.Todo.Application.Repositories;

namespace Hua.Todo.Host.Tests;

/// <summary>
/// 数据库迁移集成测试，验证迁移链的完整性与幂等性。
/// 覆盖 Maui/Avalonia 中 InitializeDatabase 的 Migrate → EnsureCreated 回退链路。
/// </summary>
public class DatabaseMigrationTests : IDisposable
{
    private readonly SqliteConnection _connection;
    private readonly DbContextOptions<TodoDbContext> _options;

    public DatabaseMigrationTests()
    {
        // 使用独立的内存数据库（每次测试独立的连接）
        _connection = new SqliteConnection("Data Source=:memory:");
        _connection.Open();

        _options = new DbContextOptionsBuilder<TodoDbContext>()
            .UseSqlite(_connection)
            .Options;
    }

    public void Dispose()
    {
        _connection.Dispose();
    }

    /// <summary>
    /// 创建新的 DbContext 实例。
    /// </summary>
    private TodoDbContext CreateContext()
    {
        return new TodoDbContext(_options);
    }

    /// <summary>
    /// 验证所有迁移可以从头开始成功应用（Migrate 从零到当前模型）。
    /// </summary>
    [Fact]
    public void Migrate_FromScratch_AllMigrationsApplied()
    {
        // Arrange
        using var context = CreateContext();

        // Act: 执行全部迁移
        context.Database.Migrate();

        // Assert: 验证所有核心表已创建
        var tables = GetTableNames(context);
        Assert.Contains("T_Tasks", tables);
        Assert.Contains("Attachments", tables);
        Assert.Contains("Users", tables);
        Assert.Contains("UserSessions", tables);
        Assert.Contains("SecurityPolicies", tables);
        Assert.Contains("AuditLogs", tables);
    }

    /// <summary>
    /// 验证 Migrate 是幂等的：第二次调用 Migrate 不会报错。
    /// </summary>
    [Fact]
    public void Migrate_Idempotent_SecondCallDoesNotThrow()
    {
        // Arrange
        using var context = CreateContext();
        context.Database.Migrate();

        // Act & Assert: 第二次 Migrate 不应抛出异常
        var exception = Record.Exception(() => context.Database.Migrate());
        Assert.Null(exception);
    }

    /// <summary>
    /// 验证 T_Tasks 表包含所有期望的列（ABP 审计字段 + 业务字段 + v1.3.0 新增字段）。
    /// </summary>
    [Fact]
    public void T_Tasks_HasAllExpectedColumns()
    {
        // Arrange
        using var context = CreateContext();
        context.Database.Migrate();

        // Act
        var columns = GetColumnNames(context, "T_Tasks");

        // Assert: ABP 审计字段
        Assert.Contains("Id", columns);
        Assert.Contains("ExtraProperties", columns);
        Assert.Contains("ConcurrencyStamp", columns);
        Assert.Contains("CreationTime", columns);
        Assert.Contains("CreatorId", columns);
        Assert.Contains("LastModificationTime", columns);
        Assert.Contains("LastModifierId", columns);
        Assert.Contains("IsDeleted", columns);
        Assert.Contains("DeletionTime", columns);
        Assert.Contains("DeleterId", columns);

        // Assert: 业务字段
        Assert.Contains("UserId", columns);
        Assert.Contains("Title", columns);
        Assert.Contains("Priority", columns);
        Assert.Contains("IsCompleted", columns);
        Assert.Contains("Code", columns);
        Assert.Contains("ParentTaskId", columns);

        // Assert: v1.3.0 新增字段
        Assert.Contains("TaskType", columns);
        Assert.Contains("MeetingNotes", columns);
        Assert.Contains("AudioDuration", columns);
        Assert.Contains("Description", columns);
    }

    /// <summary>
    /// 验证 Attachments 表存在且包含期望的列。
    /// </summary>
    [Fact]
    public void Attachments_HasAllExpectedColumns()
    {
        // Arrange
        using var context = CreateContext();
        context.Database.Migrate();

        // Act
        var columns = GetColumnNames(context, "Attachments");

        // Assert
        Assert.Contains("Id", columns);
        Assert.Contains("FileName", columns);
        Assert.Contains("FilePath", columns);
        Assert.Contains("ContentType", columns);
        Assert.Contains("FileSize", columns);
        Assert.Contains("AttachmentType", columns);
        Assert.Contains("CreatedAt", columns);
        Assert.Contains("TaskId", columns);
    }

    /// <summary>
    /// 验证 EnsureCreated 可以独立创建完整 schema（不依赖迁移历史）。
    /// 模拟 MauiProgram.InitializeDatabase 的 fallback 路径。
    /// </summary>
    [Fact]
    public void EnsureCreated_FromScratch_CreatesAllTables()
    {
        // Arrange
        using var context = CreateContext();

        // Act: 使用 EnsureCreated 而非 Migrate
        context.Database.EnsureCreated();

        // Assert: 所有核心表存在
        var tables = GetTableNames(context);
        Assert.Contains("T_Tasks", tables);
        Assert.Contains("Attachments", tables);
        Assert.Contains("Users", tables);
        Assert.Contains("UserSessions", tables);
        Assert.Contains("SecurityPolicies", tables);
        Assert.Contains("AuditLogs", tables);
    }

    /// <summary>
    /// 验证 Migrate 后所有核心表（包括 Attachments）都存在。
    /// </summary>
    [Fact]
    public void Migrate_AllTablesExist_IncludingAttachments()
    {
        // Arrange
        using var context = CreateContext();

        // Act: 执行全部迁移
        context.Database.Migrate();

        // Assert: 所有核心表已创建
        var tables = GetTableNames(context);
        Assert.Contains("T_Tasks", tables);
        Assert.Contains("Attachments", tables);
        Assert.Contains("Users", tables);
        Assert.Contains("UserSessions", tables);
        Assert.Contains("SecurityPolicies", tables);
        Assert.Contains("AuditLogs", tables);
    }

    /// <summary>
    /// 验证迁移后可以正常读写 T_Tasks 表。
    /// </summary>
    [Fact]
    public void Migrate_CanInsertAndQueryTask()
    {
        // Arrange
        using var context = CreateContext();
        context.Database.Migrate();

        // 创建测试用户
        var userId = Guid.NewGuid();
        context.Users.Add(new Core.Entities.UserEntity
        {
            Id = userId,
            UserName = "test",
            PasswordHash = "hash",
            PasswordSalt = "salt",
            Role = "User"
        });
        context.SaveChanges();

        // Act: 插入任务
        var taskId = Guid.NewGuid();
        context.Tasks.Add(new Core.Entities.TaskEntity
        {
            Id = taskId,
            UserId = userId,
            Title = "测试任务",
            Priority = Core.Entities.TaskPriority.High,
            Code = "T001",
            Description = "测试描述",
            TaskType = Core.Entities.TaskType.Normal,
            CreationTime = DateTime.UtcNow,
            CreatorId = userId
        });
        context.SaveChanges();

        // Assert: 查询验证
        var task = context.Tasks.Find(taskId);
        Assert.NotNull(task);
        Assert.Equal("测试任务", task!.Title);
        Assert.Equal("测试描述", task.Description);
        Assert.Equal(Core.Entities.TaskType.Normal, task.TaskType);
    }

    /// <summary>
    /// 验证迁移历史表包含所有预期迁移。
    /// </summary>
    [Fact]
    public void Migrate_RecordsAllMigrationsInHistory()
    {
        // Arrange
        using var context = CreateContext();

        // Act
        context.Database.Migrate();

        // Assert
        var history = context.GetService<IHistoryRepository>();
        var appliedMigrations = history.GetAppliedMigrations().Select(m => m.MigrationId).ToList();
        Assert.NotEmpty(appliedMigrations);
        // 迁移已合并为单一 InitialCreate，仅验证该迁移已记录
        Assert.Contains(appliedMigrations, m => m.Contains("InitialCreate"));
    }

    // ========== 辅助方法 ==========

    /// <summary>
    /// 获取数据库中所有表名。
    /// </summary>
    private static List<string> GetTableNames(TodoDbContext context)
    {
        return context.Database.SqlQueryRaw<string>(
            "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' AND name NOT LIKE '__EF%' ORDER BY name"
        ).ToList();
    }

    /// <summary>
    /// 获取指定表的列名列表。
    /// 使用原始 ADO.NET 查询 PRAGMA table_info 以避免 EF Core 列映射问题。
    /// </summary>
    private static List<string> GetColumnNames(TodoDbContext context, string tableName)
    {
        var columns = new List<string>();
        var connection = context.Database.GetDbConnection();
        // 确保连接已打开（内存数据库可能已关闭）
        if (connection.State != System.Data.ConnectionState.Open)
            connection.Open();

        using var command = connection.CreateCommand();
        command.CommandText = $"PRAGMA table_info('{tableName}')";
        using var reader = command.ExecuteReader();
        while (reader.Read())
        {
            // PRAGMA table_info 返回: cid(0), name(1), type(2), notnull(3), dflt_value(4), pk(5)
            columns.Add(reader.GetString(1));
        }
        return columns;
    }
}