using System;
using GB5Shared.GB5Constant;

namespace GB5Shared.ListQuery
{
    // ─────────────────────────────────────────────────────────────────────
    //  ISqlDialect  – abstracts syntax differences between databases
    //
    //  GB5 already auto-converts SQL Server → PostgreSQL via
    //  ConvertSqlToPostgres in IQueryExecutor. ISqlDialect is for
    //  intentional, explicit dialect differences where the auto-converter
    //  is insufficient or unreliable:
    //    • Paging (OFFSET/FETCH vs LIMIT/OFFSET)
    //    • LIKE vs ILIKE (case sensitivity)
    //    • ISNULL vs NVL vs COALESCE
    //    • OUTER APPLY vs LEFT JOIN LATERAL
    //    • Window COUNT(*) OVER ()
    //    • Parameter prefix (@ vs :)
    //
    //  How to get the dialect in a handler:
    //      var dialect = SqlDialectFactory.Get(loginDTO.DatabaseType);
    //
    //  LoginDTO.DatabaseType values (from GB5Shared.GB5Constant.Constant.DBType):
    //      SQL    = 0  → SqlServerDialect
    //      Oracle = 1  → OracleDialect
    //      PostGre= 2  → PostgreSqlDialect
    //      MySQL  = 3  → SqlServerDialect (closest approximation)
    // ─────────────────────────────────────────────────────────────────────
    public interface ISqlDialect
    {
        /// <summary>GB5 DBType constant (0=SQL, 1=Oracle, 2=PostGre).</summary>
        byte DbType { get; }

        /// <summary>Parameter prefix: "@" for SQL Server/PG, ":" for Oracle.</summary>
        string ParamPrefix { get; }

        /// <summary>LIKE keyword: "LIKE" for SQL Server/Oracle, "ILIKE" for PostgreSQL.</summary>
        string LikeCaseOp { get; }

        /// <summary>Current-date function: GETDATE() / SYSDATE / NOW().</summary>
        string CurrentDate { get; }

        /// <summary>Null-coalescing function name: ISNULL / NVL / COALESCE.</summary>
        string IsNullFn { get; }

        /// <summary>Generates ORDER BY + paging tail (OFFSET/FETCH or LIMIT/OFFSET).</summary>
        string Paging(string orderBy, string offsetParam, string sizeParam);

        /// <summary>Generates COUNT(*) OVER() AS TotalCount (window function).</summary>
        string TotalCountExpr();

        /// <summary>Generates OUTER APPLY / LEFT JOIN LATERAL subquery syntax.</summary>
        string OuterApply(string subquerySql, string alias);

        /// <summary>Generates TOP N / FETCH FIRST N / LIMIT N for single-row fetches.</summary>
        string TopN(int n);

        /// <summary>Wraps a parameter name with the dialect prefix: "@name" or ":name".</summary>
        string Param(string name);

        /// <summary>Generates a LIKE clause: "column LIKE @param" or "column ILIKE @param".</summary>
        string LikeClause(string column, string paramName);

        /// <summary>Generates ISNULL(expr, fallback) / NVL / COALESCE.</summary>
        string IsNull(string expr, string fallback);
    }

    // ─────────────────────────────────────────────────────────────────────
    //  SQL Server dialect
    // ─────────────────────────────────────────────────────────────────────
    public sealed class SqlServerDialect : ISqlDialect
    {
        public static readonly ISqlDialect Instance = new SqlServerDialect();

        public byte   DbType      => (byte)Constant.DBType.SQL;
        public string ParamPrefix => "@";
        public string LikeCaseOp  => "LIKE";
        public string CurrentDate => "GETDATE()";
        public string IsNullFn    => "ISNULL";

        public string Paging(string orderBy, string offsetParam, string sizeParam) =>
            $"ORDER BY {orderBy}\r\nOFFSET @{offsetParam} ROWS FETCH NEXT @{sizeParam} ROWS ONLY";

        public string TotalCountExpr() => "COUNT(*) OVER() AS TotalCount";

        public string OuterApply(string subquerySql, string alias) =>
            $"\r\nOUTER APPLY (\r\n{subquerySql}\r\n) {alias}";

        public string TopN(int n) => $"TOP {n}";

        public string Param(string name) => $"@{name}";

        public string LikeClause(string column, string paramName) =>
            $"{column} LIKE @{paramName}";

        public string IsNull(string expr, string fallback) =>
            $"ISNULL({expr}, {fallback})";
    }

    // ─────────────────────────────────────────────────────────────────────
    //  PostgreSQL dialect
    // ─────────────────────────────────────────────────────────────────────
    public sealed class PostgreSqlDialect : ISqlDialect
    {
        public static readonly ISqlDialect Instance = new PostgreSqlDialect();

        public byte   DbType      => (byte)Constant.DBType.PostGre;
        public string ParamPrefix => "@";
        public string LikeCaseOp  => "ILIKE";          // case-insensitive by default
        public string CurrentDate => "NOW()";
        public string IsNullFn    => "COALESCE";

        public string Paging(string orderBy, string offsetParam, string sizeParam) =>
            $"ORDER BY {orderBy}\r\nLIMIT @{sizeParam} OFFSET @{offsetParam}";

        public string TotalCountExpr() => "COUNT(*) OVER() AS totalcount";

        public string OuterApply(string subquerySql, string alias) =>
            $"\r\nLEFT JOIN LATERAL (\r\n{subquerySql}\r\n) {alias} ON TRUE";

        public string TopN(int n) => "";   // PostgreSQL uses LIMIT at query end

        public string Param(string name) => $"@{name}";

        public string LikeClause(string column, string paramName) =>
            $"{column} ILIKE @{paramName}";

        public string IsNull(string expr, string fallback) =>
            $"COALESCE({expr}, {fallback})";
    }

    // ─────────────────────────────────────────────────────────────────────
    //  Oracle dialect
    // ─────────────────────────────────────────────────────────────────────
    public sealed class OracleDialect : ISqlDialect
    {
        public static readonly ISqlDialect Instance = new OracleDialect();

        public byte   DbType      => (byte)Constant.DBType.Oracle;
        public string ParamPrefix => ":";
        public string LikeCaseOp  => "LIKE";
        public string CurrentDate => "SYSDATE";
        public string IsNullFn    => "NVL";

        public string Paging(string orderBy, string offsetParam, string sizeParam) =>
            $"ORDER BY {orderBy}\r\nOFFSET :{offsetParam} ROWS FETCH NEXT :{sizeParam} ROWS ONLY";

        public string TotalCountExpr() => "COUNT(*) OVER() AS TOTALCOUNT";

        // Oracle 12c+ supports OUTER APPLY
        public string OuterApply(string subquerySql, string alias) =>
            $"\r\nOUTER APPLY (\r\n{subquerySql}\r\n) {alias}";

        public string TopN(int n) => "";   // Oracle uses FETCH FIRST n ROWS ONLY at query end

        public string Param(string name) => $":{name}";

        public string LikeClause(string column, string paramName) =>
            $"{column} LIKE :{paramName}";

        public string IsNull(string expr, string fallback) =>
            $"NVL({expr}, {fallback})";
    }

    // ─────────────────────────────────────────────────────────────────────
    //  SqlDialectFactory  – resolves dialect from LoginDTO.DatabaseType
    //
    //  Usage in handlers:
    //      var dialect = SqlDialectFactory.Get(loginDTO.DatabaseType);
    // ─────────────────────────────────────────────────────────────────────
    public static class SqlDialectFactory
    {
        /// <summary>
        /// Returns the <see cref="ISqlDialect"/> matching the given
        /// <paramref name="dbType"/> (value of <c>LoginDTO.DatabaseType</c>).
        /// </summary>
        /// <exception cref="NotSupportedException">
        /// Thrown when <paramref name="dbType"/> is not a known value.
        /// </exception>
        public static ISqlDialect Get(byte dbType) => (int)dbType switch
        {
            Constant.DBType.SQL     => SqlServerDialect.Instance,
            Constant.DBType.PostGre => PostgreSqlDialect.Instance,
            Constant.DBType.Oracle  => OracleDialect.Instance,
            Constant.DBType.MySQL   => SqlServerDialect.Instance,  // closest approximation
            _ => throw new NotSupportedException(
                $"No SQL dialect registered for LoginDTO.DatabaseType={dbType}.")
        };
    }
}