using Dapper;
using GB5Shared.CriteriaHandler;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using Newtonsoft.Json;
using System.Text.RegularExpressions;

namespace GB5Shared.QueryExecutor;

public static class QueryWithCriteriaExtensions
{
    // Matches ORDER BY ... at the end of the text after {DYNAMIC_WHERE} has been removed.
    // Used to lift ORDER BY from inner subquery to outer query (SQL Server forbids
    // ORDER BY inside a subquery without TOP/OFFSET).
    private static readonly Regex _orderByRegex = new(
        @"(?i)\s*\bORDER\s+BY\b[\s\S]*$",
        RegexOptions.IgnoreCase | RegexOptions.Compiled);

    /// <summary>
    /// Replaces {DYNAMIC_WHERE} in the SQL.
    /// When <paramref name="whereClause"/> is non-empty the query is automatically
    /// wrapped in a derived table so that SELECT-list aliases (e.g. SKILLCODE AS Code)
    /// are visible in the outer WHERE clause.  ORDER BY is extracted from the original
    /// query and moved to the outer SELECT so SQL Server's subquery restriction is respected.
    /// </summary>
    private static string ApplyCriteria(string sql, string whereClause)
    {
        if (string.IsNullOrEmpty(whereClause))
            return sql.Replace("{DYNAMIC_WHERE}", string.Empty);

        int idx = sql.IndexOf("{DYNAMIC_WHERE}", StringComparison.OrdinalIgnoreCase);
        if (idx < 0)
            return sql;

        string before = sql[..idx].TrimEnd();
        string after  = sql[(idx + "{DYNAMIC_WHERE}".Length)..];

        // Extract trailing ORDER BY so it can be placed on the outer query
        var orderByMatch = _orderByRegex.Match(after);
        string orderBy = orderByMatch.Success ? orderByMatch.Value : string.Empty;
        string middle  = orderByMatch.Success ? after[..orderByMatch.Index] : after;

        string innerSql = (before + middle).TrimEnd().TrimEnd(';');

        // Strip table-alias prefixes from criteria column references (e.g. "A.CODE" → "CODE")
        // so they resolve against the derived-table columns exposed by the inner SELECT aliases.
        string outerWhere = Regex.Replace(whereClause, @"\b[A-Za-z_]\w*\.", string.Empty);

        // ORDER BY moves to the outer query too, so its alias prefixes (e.g. "ti.INSTANCETITLE")
        // must be stripped the same way — the inner table aliases aren't visible outside __T.
        string outerOrderBy = Regex.Replace(orderBy, @"\b[A-Za-z_]\w*\.", string.Empty);

        return $"SELECT * FROM ({innerSql}) AS __T WHERE 1=1 {outerWhere}{outerOrderBy}";
    }

    public static async Task<string> QueryWithCriteriaAsync<T>(
        this IQueryExecutor executor,
        LoginDTO loginDTO,
        string sql,
        CriteriaDTO criteriaDTO)
    {
        var existingParams = new DynamicParameters();
        var (whereClause, parameters) = CriteriaBuilder.Build(criteriaDTO, existingParams, sql);
        var finalSql = ApplyCriteria(sql, whereClause);
        var result = await executor.QueryAsync<T>(loginDTO, finalSql, parameters);
        return JsonConvert.SerializeObject(result);
    }

    public static async Task<string> QueryWithCriteriaAsync<T>(
        this IQueryExecutor executor,
        LoginDTO loginDTO,
        string sql,
        CriteriaDTO criteriaDTO,
        CancellationToken cancellationToken)
    {
        var existingParams = new DynamicParameters();
        var (whereClause, parameters) = CriteriaBuilder.Build(criteriaDTO, existingParams, sql);
        var finalSql = ApplyCriteria(sql, whereClause);
        var result = await executor.QueryAsync<T>(loginDTO, finalSql, parameters, cancellationToken: cancellationToken);
        return JsonConvert.SerializeObject(result);
    }

    public static async Task<IEnumerable<T>> QueryWithCriteriaAsync<T>(
        this IQueryExecutor executor,
        LoginDTO loginDTO,
        string sql,
        object sqlParams,
        CriteriaDTO criteriaDTO,
        CancellationToken cancellationToken = default)
    {
        var parameters = new DynamicParameters(sqlParams);

        var (whereClause, finalParameters) =
            CriteriaBuilder.Build(criteriaDTO, parameters, sql);

        var finalSql = ApplyCriteria(sql, whereClause);

        return await executor.QueryAsync<T>(
            loginDTO,
            finalSql,
            finalParameters,
            cancellationToken: cancellationToken);
    }

    // JSON-serializing counterpart to the IEnumerable<T> overload above — same
    // static-param + criteria composition, for callers whose interface returns
    // the raw JSON string (e.g. dynamic select-list endpoints) instead of typed objects.
    public static async Task<string> QueryWithCriteriaJsonAsync<T>(
        this IQueryExecutor executor,
        LoginDTO loginDTO,
        string sql,
        object sqlParams,
        CriteriaDTO criteriaDTO,
        CancellationToken cancellationToken = default)
    {
        var parameters = new DynamicParameters(sqlParams);

        var (whereClause, finalParameters) =
            CriteriaBuilder.Build(criteriaDTO, parameters, sql);

        var finalSql = ApplyCriteria(sql, whereClause);

        var result = await executor.QueryAsync<T>(
            loginDTO,
            finalSql,
            finalParameters,
            cancellationToken: cancellationToken);

        return JsonConvert.SerializeObject(result);
    }
}