namespace MMDAL.Query.PartLevel
{
    // Index: MPARTDIMENSION (D1, D2, D3, D4, D5, TENANTID) — unique, already on UQ_MPARTDIMENSION
    public static class PartDimensionQB
    {
        public const string GET_PARTDIMENSION = @"
            SELECT pd.PARTDIMID AS PartDimId,
                   pd.D1, pd.D2, pd.D3, pd.D4, pd.D5,
                   pd.TENANTID AS TenantId,
                   pd.STATUS   AS Status
            FROM   MPARTDIMENSION pd
            WHERE  pd.PARTDIMID = @PartDimId
            AND    pd.TENANTID  = @TenantId";

        public const string GET_OR_CREATE_PARTDIMENSION = @"
            MERGE MPARTDIMENSION WITH (HOLDLOCK) AS target
            USING (SELECT @D1 AS D1, @D2 AS D2, @D3 AS D3, @D4 AS D4, @D5 AS D5, @TenantId AS TENANTID) AS src
            ON    (   ISNULL(target.D1,'') = ISNULL(src.D1,'')
                  AND ISNULL(target.D2,'') = ISNULL(src.D2,'')
                  AND ISNULL(target.D3,'') = ISNULL(src.D3,'')
                  AND ISNULL(target.D4,'') = ISNULL(src.D4,'')
                  AND ISNULL(target.D5,'') = ISNULL(src.D5,'')
                  AND target.TENANTID      = src.TENANTID)
            WHEN NOT MATCHED THEN
                INSERT (PARTDIMID, D1, D2, D3, D4, D5, TENANTID)
                VALUES (@PartDimId, @D1, @D2, @D3, @D4, @D5, @TenantId)
            OUTPUT inserted.PARTDIMID;";

        public const string FIND_PARTDIMENSION = @"
            SELECT pd.PARTDIMID AS PartDimId
            FROM   MPARTDIMENSION pd
            WHERE  ISNULL(pd.D1,'') = ISNULL(@D1,'')
            AND    ISNULL(pd.D2,'') = ISNULL(@D2,'')
            AND    ISNULL(pd.D3,'') = ISNULL(@D3,'')
            AND    ISNULL(pd.D4,'') = ISNULL(@D4,'')
            AND    ISNULL(pd.D5,'') = ISNULL(@D5,'')
            AND    pd.TENANTID      = @TenantId";

        public const string SAVE_PARTDIMENSION = @"
            INSERT INTO MPARTDIMENSION (PARTDIMID, D1, D2, D3, D4, D5, TENANTID, STATUS)
            VALUES (@PartDimId, @D1, @D2, @D3, @D4, @D5, @TenantId, @Status)";

        public const string GET_SELECTLIST_PARTDIMENSION = @"
            SELECT pd.PARTDIMID AS PartDimId,
                   CONCAT_WS(' / ', pd.D1, pd.D2, pd.D3, pd.D4, pd.D5) AS Label
            FROM   MPARTDIMENSION pd
            WHERE  pd.TENANTID = @TenantId
            ORDER BY pd.D1, pd.D2, pd.D3";
    }
}