using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MMDAL.Query.ItemAlias
{
    // Indexes recommended:
    //   MITEMALIAS: IX_MITEMALIAS_ALIASITEMGROUPID (ALIASITEMGROUPID) — join to MITEMGROUP
    //   MITEMALIAS: IX_MITEMALIAS_ITEMID           (ITEMID)           — ItemId criteria filter
    //   MITEMGROUP: PK_MITEMGROUP                  (ITEMGROUPID)      — existing
    public static class ItemAliasQB
    {
        // ── Picklist (paged) ────────────────────────────────────────────────────
        //
        // {0} — dynamic criteria conditions appended by ItemAliasDAL.ApplyItemAliasCriteria,
        //       e.g. " AND ital.ITEMID IN(12)" or the parameterized Name LIKE clause
        //       " AND (ital.ALIASITEMNAME LIKE @Name OR ital.ALIASITEMDESCRIPTION LIKE @Name)".
        //       Left as an empty string when no criteria are supplied.
        public const string GET_SELECTLIST_ITEMALIAS_SQL = @"
            WITH Paged AS (
                SELECT ital.ITEMALIASID          AS Id,
                       ital.ALIASITEMNAME        AS AliasItemName,
                       ital.ALIASITEMDESCRIPTION AS AliasItemDescription,
                       ital.ALIASDRAWINGNUMBER   AS DrawingNo,
                       ital.ALIASCATALOGUENUMBER AS CatalogueNumber,
                       ITG.ITEMGROUPNAME         AS GroupName,
                       ROW_NUMBER() OVER (ORDER BY ital.ITEMALIASID DESC) AS RowNum
                FROM   MITEMALIAS ital, MITEMGROUP ITG
                WHERE  ITG.ITEMGROUPID = ital.ALIASITEMGROUPID
                {0}
            )
            SELECT Id, AliasItemName, AliasItemDescription, DrawingNo, CatalogueNumber, GroupName
            FROM   Paged
            WHERE  (@firstnumber = -1 AND @maxresult = -1)
               OR  (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SELECTLIST_ITEMALIAS_PG = @"
            WITH Paged AS (
                SELECT ital.ITEMALIASID          AS ""Id"",
                       ital.ALIASITEMNAME        AS ""AliasItemName"",
                       ital.ALIASITEMDESCRIPTION AS ""AliasItemDescription"",
                       ital.ALIASDRAWINGNUMBER   AS ""DrawingNo"",
                       ital.ALIASCATALOGUENUMBER AS ""CatalogueNumber"",
                       ITG.ITEMGROUPNAME         AS ""GroupName"",
                       ROW_NUMBER() OVER (ORDER BY ital.ITEMALIASID DESC) AS ""RowNum""
                FROM   MITEMALIAS ital, MITEMGROUP ITG
                WHERE  ITG.ITEMGROUPID = ital.ALIASITEMGROUPID
                {0}
            )
            SELECT ""Id"", ""AliasItemName"", ""AliasItemDescription"", ""DrawingNo"", ""CatalogueNumber"", ""GroupName""
            FROM   Paged
            WHERE  (:firstnumber = -1 AND :maxresult = -1)
               OR  (""RowNum"" BETWEEN :firstnumber AND :maxresult);";
    }
}