using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using FrameworkDAL.DTO.MenuGroup;
using FrameworkDAL.Query.MenuGroup;
using Microsoft.Data.Sqlite;
using Xunit;

namespace FrameworkTests;

/// <summary>
/// Covers MenuGroupQB.GET_RESOLVED_LEAVES_BY_GROUPIDS's override/fallback resolution order
/// (gb5_menu_presentation_layer.sql's documented rule: override row found -> use it; not
/// found -> MMENU's own display name + the bundle's own SORTORDER + ISVISIBLE = 1) against a
/// real in-memory SQLite database, matching ConflictResolverTests' "real behavioral proof,
/// not a syntax-compatible fiction" approach. MenuGroupQB's SQL uses only ANSI COALESCE/CASE,
/// so it runs unmodified here exactly as it would against SQL Server or Postgres.
/// </summary>
public class MenuGroupResolutionTests : IAsyncLifetime
{
    private SqliteConnection _conn = null!;

    public async Task InitializeAsync()
    {
        _conn = new SqliteConnection("Data Source=:memory:");
        await _conn.OpenAsync();

        // SQLite has substr(), not SUBSTRING() — register it as a real function so the query
        // runs completely unmodified here, same as it would against SQL Server or Postgres
        // (both of which support the 3-arg SUBSTRING(string, start, length) form natively).
        _conn.CreateFunction<string, long, long, string>("SUBSTRING", (s, start, length) =>
        {
            if (string.IsNullOrEmpty(s)) return s ?? string.Empty;
            int zeroStart = (int)Math.Max(0, start - 1);
            if (zeroStart >= s.Length) return string.Empty;
            int len = (int)Math.Min(length, s.Length - zeroStart);
            return s.Substring(zeroStart, len);
        });

        await _conn.ExecuteAsync(@"
            CREATE TABLE MMENUGROUPDETAIL (MENUGROUPDETAILID INTEGER PRIMARY KEY, MENUGROUPID INTEGER, MENUID INTEGER, SORTORDER INTEGER);
            CREATE TABLE MMENU (MENUID INTEGER PRIMARY KEY, MENUCODE TEXT, MENUDISPLAYNAME TEXT);
            CREATE TABLE MMENUSETDETAIL (MENUSETDETAILID INTEGER PRIMARY KEY, MENUSETID INTEGER, MENUID INTEGER, MENUDISPLAYNAME TEXT, SORTORDER INTEGER, ISVISIBLE INTEGER, MENUGROUPID INTEGER);
            CREATE TABLE MROLEVSMENU (ROLEID INTEGER, MENUID INTEGER, ALLOW TEXT);");
    }

    public async Task DisposeAsync() => await _conn.DisposeAsync();

    private Task<List<MenuGroupResolvedLeafDTO>> ResolveAsync(int menuSetId, int? roleId = null, params int[] groupIds) =>
        _conn.QueryAsync<MenuGroupResolvedLeafDTO>(
            MenuGroupQB.GET_RESOLVED_LEAVES_BY_GROUPIDS,
            new { GroupIds = groupIds, MenuSetId = menuSetId, RoleId = roleId })
            .ContinueWith(t => t.Result.ToList());

    [Fact]
    public async Task NoOverrideRow_FallsBackToMenuDisplayNameAndBundleSortOrderAndVisibleTrue()
    {
        await _conn.ExecuteAsync("INSERT INTO MMENU (MENUID, MENUCODE, MENUDISPLAYNAME) VALUES (501, 'SALESORD', 'Sales Order')");
        await _conn.ExecuteAsync("INSERT INTO MMENUGROUPDETAIL (MENUGROUPID, MENUID, SORTORDER) VALUES (10, 501, 3)");
        // No MMENUSETDETAIL row for (MenuSetId=100, MenuId=501) — the "not found" branch.

        var leaves = await ResolveAsync(menuSetId: 100, groupIds: 10);

        var leaf = Assert.Single(leaves);
        Assert.Equal(10, leaf.MenuGroupId);
        Assert.Equal("Sales Order", leaf.DisplayName);
        Assert.Equal(3, leaf.SortOrder);
        Assert.Equal(1, leaf.IsVisible);
    }

    [Fact]
    public async Task OverrideRowFound_UsesOverrideDisplayNameSortOrderAndVisibility()
    {
        await _conn.ExecuteAsync("INSERT INTO MMENU (MENUID, MENUCODE, MENUDISPLAYNAME) VALUES (501, 'SALESORD', 'Sales Order')");
        await _conn.ExecuteAsync("INSERT INTO MMENUGROUPDETAIL (MENUGROUPID, MENUID, SORTORDER) VALUES (10, 501, 3)");
        await _conn.ExecuteAsync(@"
            INSERT INTO MMENUSETDETAIL (MENUSETID, MENUID, MENUDISPLAYNAME, SORTORDER, ISVISIBLE)
            VALUES (100, 501, 'My Sales Order', 9, 0)");

        var leaves = await ResolveAsync(menuSetId: 100, groupIds: 10);

        var leaf = Assert.Single(leaves);
        Assert.Equal("My Sales Order", leaf.DisplayName);
        Assert.Equal(9, leaf.SortOrder);
        Assert.Equal(0, leaf.IsVisible);
    }

    [Fact]
    public async Task OverrideRowWithBlankDisplayName_StillFallsBackToMenuDisplayName()
    {
        // The override exists (so SortOrder/ISVISIBLE come from it) but MENUDISPLAYNAME is
        // blank — the CASE WHEN COALESCE(...,'')='' branch must still fall back to MMENU's name.
        await _conn.ExecuteAsync("INSERT INTO MMENU (MENUID, MENUCODE, MENUDISPLAYNAME) VALUES (501, 'SALESORD', 'Sales Order')");
        await _conn.ExecuteAsync("INSERT INTO MMENUGROUPDETAIL (MENUGROUPID, MENUID, SORTORDER) VALUES (10, 501, 3)");
        await _conn.ExecuteAsync(@"
            INSERT INTO MMENUSETDETAIL (MENUSETID, MENUID, MENUDISPLAYNAME, SORTORDER, ISVISIBLE)
            VALUES (100, 501, '', 9, 2)");

        var leaf = Assert.Single(await ResolveAsync(menuSetId: 100, groupIds: 10));

        Assert.Equal("Sales Order", leaf.DisplayName);
        Assert.Equal(9, leaf.SortOrder);
        Assert.Equal(2, leaf.IsVisible);
    }

    [Fact]
    public async Task OverrideMenuGroupId_RefilesLeafUnderADifferentGroupThanTheBundleDefault()
    {
        await _conn.ExecuteAsync("INSERT INTO MMENU (MENUID, MENUCODE, MENUDISPLAYNAME) VALUES (501, 'SALESORD', 'Sales Order')");
        // Bundle default places this menu under group 10 ...
        await _conn.ExecuteAsync("INSERT INTO MMENUGROUPDETAIL (MENUGROUPID, MENUID, SORTORDER) VALUES (10, 501, 3)");
        // ... but this client re-filed it under group 20 via MMENUSETDETAIL.MENUGROUPID.
        await _conn.ExecuteAsync(@"
            INSERT INTO MMENUSETDETAIL (MENUSETID, MENUID, MENUGROUPID)
            VALUES (100, 501, 20)");

        var leaf = Assert.Single(await ResolveAsync(menuSetId: 100, groupIds: 10));

        Assert.Equal(20, leaf.MenuGroupId);
    }

    [Fact]
    public async Task OtherMenuSetsOverride_IsIgnored_OnlyTheRequestedSetsOverrideApplies()
    {
        await _conn.ExecuteAsync("INSERT INTO MMENU (MENUID, MENUCODE, MENUDISPLAYNAME) VALUES (501, 'SALESORD', 'Sales Order')");
        await _conn.ExecuteAsync("INSERT INTO MMENUGROUPDETAIL (MENUGROUPID, MENUID, SORTORDER) VALUES (10, 501, 3)");
        // Override belongs to a DIFFERENT MenuSetId (200) — must not leak into MenuSetId 100's resolution.
        await _conn.ExecuteAsync(@"
            INSERT INTO MMENUSETDETAIL (MENUSETID, MENUID, MENUDISPLAYNAME, SORTORDER, ISVISIBLE)
            VALUES (200, 501, 'Someone Else''s Override', 1, 0)");

        var leaf = Assert.Single(await ResolveAsync(menuSetId: 100, groupIds: 10));

        Assert.Equal("Sales Order", leaf.DisplayName);
        Assert.Equal(3, leaf.SortOrder);
        Assert.Equal(1, leaf.IsVisible);
    }

    [Fact]
    public async Task NullRoleId_SkipsRoleFilterEntirely_AdminAndContentManagementCallersSeeFullMembership()
    {
        await _conn.ExecuteAsync("INSERT INTO MMENU (MENUID, MENUCODE, MENUDISPLAYNAME) VALUES (501, 'SALESORD', 'Sales Order')");
        await _conn.ExecuteAsync("INSERT INTO MMENUGROUPDETAIL (MENUGROUPID, MENUID, SORTORDER) VALUES (10, 501, 3)");
        // No MROLEVSMENU row at all for this menu — would deny every role, but roleId=null must
        // bypass the filter entirely (admin/content-management path).

        var leaf = Assert.Single(await ResolveAsync(menuSetId: 100, roleId: null, groupIds: 10));

        Assert.Equal(501, leaf.MenuId);
    }

    [Fact]
    public async Task RoleIdProvided_WithNoMatchingRoleVsMenuRow_LeafIsFilteredOut()
    {
        await _conn.ExecuteAsync("INSERT INTO MMENU (MENUID, MENUCODE, MENUDISPLAYNAME) VALUES (501, 'SALESORD', 'Sales Order')");
        await _conn.ExecuteAsync("INSERT INTO MMENUGROUPDETAIL (MENUGROUPID, MENUID, SORTORDER) VALUES (10, 501, 3)");
        // Role 7 has no MROLEVSMENU grant at all for menu 501 — deny-by-default, same as the
        // legacy GetMenuForUserModule query's inner-join-only-grants-are-visible semantics.

        var leaves = await ResolveAsync(menuSetId: 100, roleId: 7, groupIds: 10);

        Assert.Empty(leaves);
    }

    [Fact]
    public async Task RoleIdProvided_WithDeniedAllowFlag_LeafIsFilteredOut()
    {
        await _conn.ExecuteAsync("INSERT INTO MMENU (MENUID, MENUCODE, MENUDISPLAYNAME) VALUES (501, 'SALESORD', 'Sales Order')");
        await _conn.ExecuteAsync("INSERT INTO MMENUGROUPDETAIL (MENUGROUPID, MENUID, SORTORDER) VALUES (10, 501, 3)");
        // Allow position 1 = '1' means view NOT granted (legacy convention: '0' = allowed).
        await _conn.ExecuteAsync("INSERT INTO MROLEVSMENU (ROLEID, MENUID, ALLOW) VALUES (7, 501, '10000')");

        var leaves = await ResolveAsync(menuSetId: 100, roleId: 7, groupIds: 10);

        Assert.Empty(leaves);
    }

    [Fact]
    public async Task RoleIdProvided_WithGrantedAllowFlag_LeafIsIncluded()
    {
        await _conn.ExecuteAsync("INSERT INTO MMENU (MENUID, MENUCODE, MENUDISPLAYNAME) VALUES (501, 'SALESORD', 'Sales Order')");
        await _conn.ExecuteAsync("INSERT INTO MMENUGROUPDETAIL (MENUGROUPID, MENUID, SORTORDER) VALUES (10, 501, 3)");
        await _conn.ExecuteAsync("INSERT INTO MROLEVSMENU (ROLEID, MENUID, ALLOW) VALUES (7, 501, '00000')");
        // A different role (8) is denied — must not leak this role's grant into role 7's view.
        await _conn.ExecuteAsync("INSERT INTO MROLEVSMENU (ROLEID, MENUID, ALLOW) VALUES (8, 501, '10000')");

        var leaf = Assert.Single(await ResolveAsync(menuSetId: 100, roleId: 7, groupIds: 10));

        Assert.Equal(501, leaf.MenuId);
    }
}